PL/SQL: How do I declare session variables ?

Question

How do I declare a session variable in PL/SQL - one that will persist for the duration of the session only, without me having to store it in the database itself?

Answer 1

You create a package level variable. This is a minimal example:

CREATE OR REPLACE PACKAGE my_package
AS
    FUNCTION get_a RETURN NUMBER;
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package
AS
    a  NUMBER(20);

    FUNCTION get_a
    RETURN NUMBER
    IS
    BEGIN
      RETURN a;
    END get_a;
END my_package;
/

If you do this you should read up on (and handle correctly) ORA-04068 errors. Each database session will have it's own value for a. You can try this with:

SELECT my_package.get_a FROM DUAL;

Answer 2

You can use a user create context to store data shared across multiple units within session.

First, create a context:

CREATE CONTEXT SYS_CONTEXT ('userenv', 'current_schema')|| '_ctx' USING PKG_COMMON

Second, create a package that would manage your context:

CREATE OR REPLACE PACKAGE PKG_COMMON
IS
   common_ctx_name   CONSTANT VARCHAR2 (60)
                 := SYS_CONTEXT ('userenv', 'current_schema')
                    || '_ctx';

   FUNCTION fcn_get_context_name RETURN VARCHAR2;
   PROCEDURE prc_set_context_value (var_name VARCHAR2, var_value NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY PKG_COMMON
IS
   FUNCTION fcn_get_context_name
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN common_ctx_name;
   END;

   PROCEDURE prc_set_context_value (var_name VARCHAR2, var_value NUMBER)
   IS
   BEGIN
      DBMS_SESSION.set_context (common_ctx_name, var_name, var_value);
   END;
END;

The prc_set_context_value can be more advances, that's just an example. With the context and the package created you can start using them. Set a context variable using a procedure call

begin
  PKG_COMMON.prc_set_context_value('MyVriable', 9000)
end;

and use it anywhere - any procedure, package, fuction or event a view.

CREATE VIEW V_TEST AS
  SELECT ID, LOGIN, NAME 
    FROM USERS 
   WHERE ROLE_ID =  SYS_CONTEXT(PKG_COMMON.FCN_GET_CONTEXT_NAME, 'MyVariable')

For more information see http://www.psoug.org/reference/sys_context.html

Answer 3

Here is a related article I wrote on my blog a while ago.