How to use a package constant in SQL SELECT statement?
How can I use a package variable in a simple SELECT query statement in Oracle?
Something like
SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE
Is it possible at all or only when using PL/SQL (use SELECT within BEGIN/END)?
You can't.
For a public package variable to be used in a SQL statement, you have to write a wrapper function to expose the value to the outside world:
SQL> create package my_constants_pkg
2 as
3 max_number constant number(2) := 42;
4 end my_constants_pkg;
5 /
Package created.
SQL> with t as
2 ( select 10 x from dual union all
3 select 50 from dual
4 )
5 select x
6 from t
7 where x < my_constants_pkg.max_number
8 /
where x < my_constants_pkg.max_number
*
ERROR at line 7:
ORA-06553: PLS-221: 'MAX_NUMBER' is not a procedure or is undefined
Create a wrapper function:
SQL> create or replace package my_constants_pkg
2 as
3 function max_number return number;
4 end my_constants_pkg;
5 /
Package created.
SQL> create package body my_constants_pkg
2 as
3 cn_max_number constant number(2) := 42
4 ;
5 function max_number return number
6 is
7 begin
8 return cn_max_number;
9 end max_number
10 ;
11 end my_constants_pkg;
12 /
Package body created.
And now it works:
SQL> with t as
2 ( select 10 x from dual union all
3 select 50 from dual
4 )
5 select x
6 from t
7 where x < my_constants_pkg.max_number()
8 /
X
----------
10
1 row selected.
There is a more generic way which works fine for me. You create a function with input constant name (i.e. schema.package.constantname) and it returns you the constant value. You make use of executing immediate a PL/SQL block by binding res variable (see example).
Function looks like this:
CREATE OR REPLACE FUNCTION GETCONSTANTVALUE (i_constant IN VARCHAR2) RETURN NUMBER deterministic AS
res number;
BEGIN
execute immediate 'begin :res := '||i_constant||'; end;' using out res;
RETURN res;
END;
/
You can then use the constant of any package in any SQL, i.e. like
select GETCONSTANTVALUE('PKGGLOBALCONSTANTS.constantname') from dual;
Like this you need only 1 function and you take the advantage to use existing packages.constants.