Oracle-Plsql — Using a package variable in a SQL Select Statement:

Consider the following Example:

create package test
is
g_name varchar2(10) := ‘X’;
end test;

when test.g_name is accessed from outside, (from SQL )
select test.g_name from dual;

following error would be thrown:
ORA-06553: PLS-221: ‘G_NAME’ is not a procedure or is undefined

[Note: However, g_name can still be accessed inside a plsql block,
declare
l_name varchar2(10);
begin select test.g_name into l_name from dual;
dbms_output.put_line(l_name);
end;
]
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 as:

create or replace package test is
g_name varchar2(10) := ‘X’;
function fn_get_gname return varchar2;
end test;
create or replace package body test
is
function fn_get_gname return varchar2
is
begin
return g_name;
end;
end test;
Now, g_name can be accessed as :
select test.fn_get_gname() from dual?-?Output: X