PL/SQL: How do I declare session variables?

oracle session variables list
oracle variables
postgres session variables
system session variables in obiee 12c
oracle sys_context
nq_session variables in obiee
sys_context 12c

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?

You can use a 'user-created context' to store data shared across multiple units within a 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 advanced, 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('MyVariable', 9000)
end;

and use it anywhere - any procedure, package, function 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

PL/SQL: How do I declare session variables?, from the pseudo table dual or as a comparison value in the SELECT -list. SELECT @my_variable_name AS "The Value" FROM dual; General Syntax to declare a variable is. variable_name datatype [NOT NULL := value ]; variable_name is the name of the variable. datatype is a valid PL/SQL datatype. NOT NULL is an optional specification on the variable. value or DEFAULT value is also an optional specification, where you can initialize a variable.

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;

Working with Session Variables, You can use a 'user-created context' to store data shared across multiple units within a session. First, create a context: CREATE CONTEXT� Variable Declaration in PL/SQL. PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name. The syntax for declaring a variable is −.

I like to use a short, but intuitive syntax and thus would create e.g. some ctx package that just provides one function to set and get some global "variables". (valid for the current session only ; in my case no need to implement it as user-created-context variables, but easily changeable to it under the hood ; example with some foo varchar2 and bar number vars)

usage:

select ctx.foo from dual                                                 -- => null (init)
select ctx.foo('a') from dual                                            -- => 'a'
select ctx.foo('b') from dual ; select ctx.foo from dual                 -- => 'b', 'b'

.

-- (optimizer should cause the subquerys unselected columns not to be executed:)
select 'ups' from (select ctx.foo('a') from dual) ; select ctx.foo from dual   -- => null

select ctx.bar(1.5) from dual ; select ctx.bar from dual                 -- => 1.5,  1.5
-- ...

package header:

create or replace package  ctx  as

  -- select ctx.foo from dual                                            -- => null (init)
  -- select ctx.foo('a') from dual                                       -- => 'a'
  -- select ctx.foo('b') from dual ; select ctx.foo from dual            -- => 'b', 'b'
  -- (optimizer should cause the subquerys unselected columns not to be executed:)
  -- select 'ups' from (select ctx.foo('a') from dual) ; select ctx.foo from dual
    -- => null
  -- parallel_enable for queries since it should not change inside of them
  function foo(  set varchar2 := null  ) return varchar2  parallel_enable;

  -- (samples like in foo above as executable test comments like in foo above skipped for 
  -- brevity)
  function bar(  set number := null  ) return  number  parallel_enable;

end;

package body:

create or replace package body  ctx  as

  foo_  varchar2(30);  -- e.g. 'blabla'
  bar_  number;


  -- internal helper function for varchars
  function set_if_not_null( ref  in out  varchar2,  val  varchar2  ) return varchar2 as 
  begin
    if  val is not null  then  ref := val;  end if;
    return ref ;
  end;


  -- internal helper function for numbers
  function set_if_not_null( ref  in out  number,  val number  ) return  number  as begin
    if  val is not null  then  ref := val;  end if;
    return ref ;
  end;


  -- (same test comments like in foo above skipped for brevity)      
  function foo(  set varchar2 := null  ) return varchar2  parallel_enable as begin
    return set_if_not_null(  foo_,  set  ) ;
  end;


  -- (same test comments like in foo above skipped for brevity)      
  function bar(  set number := null  ) return  number  parallel_enable as begin
    return set_if_not_null(  bar_,  set  ) ;
  end;

end;

If you know that the variable (foo) may change within a single query then remove parallel_enable, which should otherwise be more performant if queries are parallelizable.

Depending on ones needs one could of course add some foo_reset() to set it to null etc..

Oracle - Session Variables, System session variables are used by the Oracle BI Server and See <<Syntax and Usage Notes for SELECT_PHYSICAL- link to SQL Ref>> for You use the same procedure to define nonsystem session� I like to use a short, but intuitive syntax and thus would create e.g. some ctx package that just provides one function to set and get some global "variables". (valid for the current session only ; in my case no need to implement it as user-created-context variables, but easily changeable to it under the hood ; example with some foo varchar2 and bar number vars)

SQL & PL/SQL � Setting session variables from , We want to set the logging level in the front end, which should set a PL/SQL package/session variable to the respective value. Any subsequent� Whenever you want to deal with session variables, you need to make sure that a session is already started. There are a couple of ways you can start a session in PHP. Use the session_start Function. This is the method that you'll see most often, where a session is started by the session_start function. <?php // start a session session_start(); // manipulate session variables ?>

MySQL - Session Variables, COBOL to Oracle PL/SQL MySQL - Session Variables - How to Define and Use User-Defined can be used in any SQL query or statement, not visible to other sessions, and exists until the end of the current session. Quick Example: -- Initialize the session variable (declaration statement is not required)� From the Project menu, point to Add Web Item, and then click Active Server Page.; Click Open to accept the default information for the new ASP page.; Now that you added an ASP page to the current project, the ASP code samples in the next two sections demonstrate the use of both Session and Application variables.

DBMS_SESSION : Managing Sessions From a , DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle To counter this, Oracle 9iR1 introduced the SET_IDENTIFIER and Later releases of Oracle included the CLIENT_IDENTIFIER information in the audit trail, SQL trace ability to alter package state by amending the values of package variables. Example - Declare a variable. Let's look at an example of how to declare a variable in SQL Server. For example: DECLARE @techonthenet VARCHAR(50); This DECLARE statement example would declare a variable called @techonthenet that is a VARCHAR datatype, with a length of 50 characters.

Comments
  • When I create context it says: ORA-28265: NameSpace beginning with 'sys_' is not allowed. I tried to set it into variable. But it sets variable name instead of value
  • Why dynamically build up the context name using CURRENT_SCHEMA? Does it need to be globally unique or something?
  • I'm assuming you need a procedure to set the value?
  • @jpmc26 It would be normal to have a setter. I think you can put the variable in the package (not the package body) to make it public.