How to access a package variable in a SQL-statement.

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;
/