This is an old revision of the document!


Oracle Database - Session

> Database > Oracle Database

1 - About

One session = One connection = One transaction (one user behind its screen)

Advertising

3 - V%session

When the username is null, this is server activity not client activity. The program runs oracle.exe.

4 - Session variable

As Sysdba:

GRANT CREATE ANT CONTEXT TO HR;

As HR:

CREATE OR REPLACE
PACKAGE set_context_variable
IS
 
PROCEDURE set_empno;
 
PROCEDURE set_variable_value(
    n VARCHAR2,
    v VARCHAR2);
 
END;
/
 
CREATE OR REPLACE
PACKAGE BODY set_context_variable
IS
  -- dbms_session.set_context
  -- can only be called within the package to which it belongs
  -- If you try to execute DBMS_SESSION.SET_CONTEXT you'll get an error, as
  -- shown here:
  -- ORA-01031: insufficient privileges
 
PROCEDURE set_empno
IS
  emp_id NUMBER;
BEGIN
 
  SELECT
    EMPLOYEE_ID
  INTO
    emp_id
  FROM
    HR.EMPLOYEES
  WHERE
    email = SYS_CONTEXT('USERENV', 'SESSION_USER');
 
  DBMS_SESSION.SET_CONTEXT('empno_ctx', 'employee_id', emp_id);
 
EXCEPTION
 
WHEN NO_DATA_FOUND THEN
  NULL;
 
END;
 
PROCEDURE set_variable_value(
    n VARCHAR2,
    v VARCHAR2)
AS
BEGIN
  DBMS_SESSION.set_context('myContext',n,v);
END;
 
END;
/

Context empno_ctx:

CREATE OR REPLACE CONTEXT empno_ctx USING set_context_variable;
 
SELECT
    SYS_CONTEXT('USERENV', 'SESSION_USER')
  FROM
    dual;

Ouptut:

SYS_CONTEXT('USERENV','SESSION_USER')                                                                        
---------------------------------------
HR
  SELECT
    SYS_CONTEXT('empno_ctx', 'employee_id')
  FROM
    dual;

Ouptut:

SYS_CONTEXT('empno_ctx', 'employee_id')
---------------------------------------
1

Context myContext:

CREATE OR REPLACE CONTEXT myContext USING set_context_variable;
 
EXEC set_context_variable.set_variable_value('myVar','myValue');
 
SELECT
  SYS_CONTEXT('myContext', 'myVar')
FROM
  dual;

Ouptut:

SYS_CONTEXT('MYCONTEXT','MYVAR') 
-------------------------------------- 
myValue    
Advertising

5 - Documentation / Reference

db/oracle/session.1334228237.txt.gz · Last modified: 2012/04/12 12:57 by gerardnico