How to Reference Package Variables in SQL
A long time ago (pre-12c) I wrote about {% post_link how-to-reference-package-variables %}. This technique used an execute immediate
function to reference a given package variable. Another alternative at the time was to create individual get
functions for each variable.
Starting in Oracle 12c you can directly use PL/SQL in SQL and thus reference package variables in SQL. The following example shows how:
create or replace package pkg_demo as
gc_first_name constant varchar2(255) := 'Martin';
end pkg_demo;
/
with
function get_name return varchar2 as
begin
return pkg_demo.gc_first_name;
end;
select get_name() my_name
from dual;
/
-- Will return
MY_NAME
Martin
You can also use this concept in views.