How to use a package constant in SQL SELECT statement?

Question

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)?

Answer 1

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.