Wednesday, March 24, 2010

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

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

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

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 refernce 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.

-- **
-- * 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:

SQL> SELECT f_get_pkg_val_vc2 ('pkg_var.c_my_var') x
2 FROM DUAL;

X
-----------------------------------------------------

hello

6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi, Martin!

    First of all, thanks for your blog, it's great.

    Now the question. Can you point on the benefits of your package constant access? (It's not a package variable in your example...)

    I know other way to do it - wrapping a package constant by a package deterministic function. (Or a package variable by a package non-deterministic function...) It doesn't use Native Dynamic SQL, it stores a dependency (if such exists, e.g. a constant in one package and a function in another one) in the database dictionary, it parses once on package compilation, it caches...

    And one more way to do it, when you need a constant value. Why don't you look on using user context? Set the appropriate context with constant value in the package initialization part, then get this context in your SQL by calling sys_context(...) or its wrapper.

    Alex.

    PS: Sorry, my Russian and Ukrainian are much better than English. :)

    ReplyDelete
  3. That's a very creative approach - it might be useful for some scenarios.

    I'd be interested to see what sort of performance characteristics it would exhibit when scaled up, since it involves a parse/execute call every time the function is called (although this might be mitigated with Oracle's function result cache feature).

    A problem I see is that the variable name will not be checked at compile time, since it is a literal string.

    I would think it would be better just to create a function in the package that returns the value of the variable.

    ReplyDelete
  4. Hi!

    Just one thing in your code:

    -- Full Variable Name (i.e. pkg.var)
    v_var_full_name VARCHAR2 (61); -- Max of 61 chars since 30 + . + 30

    Don't forget about really full name - with schema name identifier. ;)

    Alex.

    ReplyDelete
  5. I've seen this technique used bringing the production database down to a grinding halt. Simply because of all the extra parsing that needs to be done. Wouldn't recommend this to anyone working on a production database... Use the technique described by Jeffrey Kemp.

    ReplyDelete
  6. Hi,

    I think everyone has some excellent points. I agree that for the long term solution, creating individual deterministic functions for each variable would be the ideal case.

    Alex N. had an great commented that this sort of method can bring down a server really quickly. I agree as the function will be executed for each row. To get around this issue I do an inline materialized view to minimize the amount of times the function is executed.

    Instead of this: (function is executed for each row)
    select *
    from emp
    where ename = f_get_pkg_val_vc2 ('pkg_var.c_my_var')

    I do this: (function executed once)
    select e.*
    from emp e, (select f_get_pkg_val_vc2 ('pkg_var.c_my_var') my_val from dual where rownum > 0) vals
    where e.ename = vals.my_val

    ReplyDelete