Recently I tried to use a public package variable in a select statement, but ran into an error.
ORA-06553: PLS-221: 'A_VAR' is not a procedure or is undefined
Unfortunately it is not possible to access such a variable directly in SQL. The solution would be to create a getter function which returns value of the variable.
CREATE OR REPLACE PACKAGE onevar
IS
a_var VARCHAR2(10) DEFAULT 'Hello';
a_date DATE DEFAULT SYSDATE;
a_nr NUMBER DEFAULT 100;
FUNCTION get_a_var
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY onevar
IS
FUNCTION get_a_var
RETURN VARCHAR2
IS
BEGIN
RETURN (a_var);
END;
END;
/
We could then use this function in a select statement
The advantage of this approach is that we can define the variable as private, which is a good idea from a security point of view, but we would have to define a getter function for each variable which could be quite tedious. If it is a third party package we might not be able to change the source code because of copyright reasons. So I got to think about another, more generic, way to achieve the same effect.
The best alternative solution I could think of, which could be made universal, is by using an application context. I created a package which accepts a package variable name (which has to be public for obvious reasons) and then use dynamic PL/SQL to define a context variable and then return it from a function.
First the context needs to be created:
CREATE OR REPLACE CONTEXT get_any_var_ctx USING get_any_var;
The package get_any_var is authorized to set the context. After that I created the package that sets the context, fetches it and returns it from a function:
CREATE OR REPLACE PACKAGE get_any_var
IS
PROCEDURE set_context(p_name VARCHAR2
,p_value VARCHAR2);
FUNCTION get_str(p_var_name VARCHAR2)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY get_any_var
IS
PROCEDURE set_context(p_name VARCHAR2
,p_value VARCHAR2)
IS
BEGIN
-- Create a session with a previously defined context.
dbms_session.set_context('GET_ANY_VAR_CTX'
,p_name
,p_value);
END;
FUNCTION get_str(p_var_name VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
EXECUTE IMMEDIATE
'begin get_any_var.set_context(''l_temp_var'','
|| p_var_name
|| '); end;';
RETURN (SYS_CONTEXT('GET_ANY_VAR_CTX'
,'l_temp_var'));
END;
END;
/
Now it is possible to use any public package variable or constant in a select statement:
The package can be extended with multiple functions for each data type, eg. NUMBER, DATE etc.
Of course there are some caveats:
- The schema under which the package is created must have CREATE ANY CONTEXT privilege, and probably also DELETE ANY CONTEXT.
- Because the variables are public they can be set by anyone who has execute rights on the package.
- There could be a negative impact on performance.
- Because of the dynamic SQL typo’s will not be noticed at compile time.
- Using the wrong function could lead to implicit type conversions unless extensive error handling is added to the package.
A full version of the package could look like this (no error handling however):
CREATE OR REPLACE PACKAGE get_any_var
IS
PROCEDURE set_context(p_name VARCHAR2
,p_value VARCHAR2);
FUNCTION get_nr(p_var_name VARCHAR2)
RETURN NUMBER;
FUNCTION get_str(p_var_name VARCHAR2)
RETURN VARCHAR2;
FUNCTION get_date(p_var_name VARCHAR2
,p_date_format VARCHAR2 DEFAULT 'dd-mm-yyyy hh24:mi:ss')
RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY get_any_var
IS
PROCEDURE set_context(p_name VARCHAR2
,p_value VARCHAR2)
IS
BEGIN
-- Create a session with a previously defined context.
dbms_session.set_context('GET_ANY_VAR_CTX'
,p_name
,p_value);
END;
FUNCTION get_nr(p_var_name VARCHAR2)
RETURN NUMBER
IS
BEGIN
EXECUTE IMMEDIATE
'begin get_any_var.set_context(''l_temp_var'','
|| p_var_name
|| '); end;';
RETURN (TO_NUMBER(SYS_CONTEXT('GET_ANY_VAR_CTX'
,'l_temp_var')));
END;
FUNCTION get_str(p_var_name VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
EXECUTE IMMEDIATE
'begin get_any_var.set_context(''l_temp_var'','
|| p_var_name
|| '); end;';
RETURN (SYS_CONTEXT('GET_ANY_VAR_CTX'
,'l_temp_var'));
END;
FUNCTION get_date(p_var_name VARCHAR2
,p_date_format VARCHAR2 DEFAULT 'dd-mm-yyyy hh24:mi:ss')
RETURN DATE
IS
BEGIN
EXECUTE IMMEDIATE
'begin get_any_var.set_context(''l_temp_var'', to_char('
|| p_var_name
|| ','''
|| p_date_format
|| ''')); end;';
RETURN (TO_DATE(SYS_CONTEXT('GET_ANY_VAR_CTX'
,'l_temp_var')
,p_date_format));
END;
END;
/
