How To Reference Package Variables Outside of PL/SQL

When developing with PL/SQL you may store public variables in the package specification. This has many uses, none of which I will get into for this post. The only catch in Oracle is that you can not easily reference these values in SQL statements outside of PL/SQL. The following example demonstrates this:

  • Create Package Spec with Variable

    1
    2
    3
    4
    CREATE OR REPLACE PACKAGE pkg_var
    AS
    c_my_var CONSTANT VARCHAR2 (5) := 'hello';
    END pkg_var;
  • Reference the variable in a SQL statement in SQL*Plus

    1
    2
    3
    4
    5
    6
    SQL> SELECT pkg_var.c_my_var x
    2 FROM DUAL;
    SELECT pkg_var.c_my_var x
    *
    ERROR at line 1:
    ORA-06553: PLS-221: 'C_MY_VAR' is not a procedure or is undefined

This results in an Oracle error.

  • Try the same code, but in an block of PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> DECLARE
2 v_x VARCHAR2 (5);
3 BEGIN
4 SELECT pkg_var.c_my_var x
5 INTO v_x
6 FROM DUAL;
7
8 DBMS_OUTPUT.put_line (v_x);
9 END;
10 /
hello
PL/SQL procedure successfully completed.

As you can see this worked.

So how can we reference package variables in a non-PL/SQL setting? I created the following function to do so. It will handle values that are of type VARCHAR2. I’ve also removed any spaces from the parameter (pkg_name.var_name) to ensure that no SQL injection will occur.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- **
-- * Returns Package Variable value
-- * Note: for demo purposes I broke this function into various steps
-- *
-- * @param p_pkg_var_name fully qualified variable reference. Ex: pkg_x.var_y
-- * @return Varchar2 value
-- * @author Martin Giffy D'Souza: http://apex-smb.blogspot.com
-- **
CREATE OR REPLACE FUNCTION f_get_pkg_val_vc2 (p_pkg_var_name in varchar2)
RETURN VARCHAR2
AS
v_string VARCHAR2 (4000);
-- Full Variable Name (i.e. pkg.var)
v_var_full_name VARCHAR2 (61); -- Max of 61 chars since 30 + . + 30
BEGIN
v_var_full_name := p_pkg_var_name;
-- Remove any spaces to avoid SQL injections
v_var_full_name := REGEXP_REPLACE (v_var_full_name, '[[:space:]]', '');
EXECUTE IMMEDIATE 'begin :v_string := ' || v_var_full_name || '; end;'
USING OUT v_string;
RETURN v_string;
END f_get_pkg_val_vc2;

Now when you run in SQL*Plus you get the following:

1
2
3
4
5
6
7
SQL> SELECT f_get_pkg_val_vc2 ('pkg_var.c_my_var') x
2 FROM DUAL;
X
-----------------------------------------------------
hello