Thursday, November 20, 2014

Oracle: How to use Constants defined in a package in SQL Statement

I have an Oracle package with 100+ Constants defined.  I wanted to use those constants in a SQL, and couldn't find any easy option.

I Found this solution in StackOverflow from "Björn", which is brilliant. I just changed the return to Varchar2 instead of number, so it will work for all data types

----------------------------------------------------------------------------------------------------------------
Create or Replace Function get_constant (i_constant IN Varchar2)  RETURN Varchar2 deterministic AS
   output Varchar2(4000);
Begin

   execute immediate 'begin :output := '||i_constant||'; end;' using out output;   
   Return output;

End;
/
----------------------------------------------------------------------------------------------------------------

Create or Replace Package My_Constants as

number_constant constant int := 9;
varchar2_constant constant varchar2(200) :='Hello There';
Date_Constant constant date := sysdate;
end;
/
select get_constant('My_Constants.number_constant'),
       get_constant('My_Constants.varchar2_constant'),
       get_constant('My_Constants.Date_Constant')
     from dual;