The inspiration for this article came from reading Mark Hoxey excellent post on avoiding ORA-04068: existing state of packages has been discarded One of the potential solutions to this problem suggested by Mark is to use Application Context. I didn’t know too much about Application Context so this post is my way of documenting and sharing what I learnt.

What is Application Context?

Application Context a set of name value pairs stored in memory. Each context is essentially a namespace which contains one or more name value pairs. Application Context is a large subject and Oracle list a number of potential uses cases for Application Context.

My goal for this post is not to rewrite the documentation but to provide a walk through of creating an Application Context that could serve as replacement for the constant or variable data that is often duplicated in various package bodies scattered throughout your application.

All the examples in this article were built using Oracle’s pre-built Database App Development Virtual Machine which used Database version 11.2.0.2.

Example

CREATE OR REPLACE CONTEXT user_ctx USING pkg_user_application_context
/

The first line creates an Application Context with the name user_ctx. The USING clause identifies the PL/SQL package that can set or reset the context variables. In this example pkg_user_application_context is used. It is worth mentioning that the package specified by the USING clause does not need to exist at the time when the context is created but it must exist at run time. So the next step is create pkg_user_application_context

CREATE OR REPLACE PACKAGE pkg_user_application_context
IS

 PROCEDURE set_context_values;

END pkg_user_application_context;
/

CREATE OR REPLACE PACKAGE BODY pkg_user_application_context
IS
 PROCEDURE set_context_values
 IS

    ld_hire_date      DATE;
    ln_department     NUMBER(5);
    lv_is_a_manager   VARCHAR2(1);

 BEGIN

 -- Pretend look ups from HR tables happens here...

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'hire_date', TO_CHAR(ld_hire_date, 'YYYYMMDD'));

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'department_id', TO_CHAR(ln_department,'99999'));

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'is_a_manager', lv_is_a_manager);

END pkg_user_application_context;
/

The package body shows a look up of various values from tables (which are not shown to keep the example from becoming too bloated) These values are then used by the call to DBMS_SESSION.SET_CONTEXT to create a number of name value pairs under the context created earlier.

A couple of points worth making; the values are stored as characters so for numbers and dates the appropriate calls to TO_CHAR are required and all the values created belong to the same Application Context, user_ctx

If an attempt is made to change the values of the Application context outside of the package pkg_user_application_context such as the following anonymous block :

BEGIN
   DBMS_SESSION.SET_CONTEXT('user_ctx', 'is_a_manager', 'Y');
END;
/

you will see the  following error message:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at line 2   

With both the Application Context and supporting package created we can now go ahead and start using the values within our application:

CREATE OR REPLACE PACKAGE BODY pkg_some_other_package
IS
 PROCEDURE interesting_hr_stuff
 IS

    ld_hire_date      DATE;
    ln_department     NUMBER(5);
    lv_is_a_manager   VARCHAR2(1);

 BEGIN

    ld_hire_date := TO_DATE(SYS_CONTEXT('user_ctx','hire_date'), 'YYYYMMDD');

    ln_department := TO_NUMBER(SYS_CONTEXT('user_ctx','department_id'));

    lv_is_a_manager := SYS_CONTEXT('user_ctx','is_a_manager');

    -- now work with the variables 

 END interesting_hr_stuff

END pkg_some_other_package;
/

The example shows another package (the spec is not shown) where the values are retrieved from the Application Context using the built in SYS_CONTEXT. The values are explicitly converted back to the expected data types and then used within the application.

Summary

In this article I have explained what an Application Context is and demonstrated how to create one use as a replacement for storing information that was previously held in package body constants and variables.

Acknowledgements

Mark Hoxey