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;