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 Variable1234CREATE OR REPLACE PACKAGE pkg_varASc_my_var CONSTANT VARCHAR2 (5) := 'hello';END pkg_var;
Reference the variable in a SQL statement in SQL*Plus123456SQL> SELECT pkg_var.c_my_var x2 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
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.
Now when you run in SQL*Plus you get the following: