Differences

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Text|String|Character)

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
db:oracle:session [2012/04/12 12:57]
gerardnico
db:oracle:session [2017/09/06 19:29] (current)
gerardnico ↷ Page moved from oracle:session to db:oracle:session
Line 3: Line 3:
 ===== About ===== ===== About =====
 One session = One connection = One transaction (one user behind its screen) One session = One connection = One transaction (one user behind its screen)
 +
 +For session variable, see [[context]]
 ===== Articles Related ===== ===== Articles Related =====
 {{backlinks>​.}} {{backlinks>​.}}
  
-===== V%session =====+===== Session Id ===== 
 +  * SESSIONID - The auditing session identifier. You cannot use this attribute in distributed SQL statements. 
 +  * SID - The session ID. 
 +You can get the current values for a session with the help of the [[userenv|userenv]] context. 
 + 
 +===== V$session =====
 When the username is null, this is server activity not client activity. The program runs oracle.exe. When the username is null, this is server activity not client activity. The program runs oracle.exe.
  
  
-===== Session variable ===== 
- 
-As Sysdba: 
-<code plsql> 
-GRANT CREATE ANT CONTEXT TO HR; 
-</​code>​ 
- 
-As HR: 
-<code plsql> 
-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; 
-/ 
-</​code>​ 
- 
-Context empno_ctx: 
-<code plsql> 
-CREATE OR REPLACE CONTEXT empno_ctx USING set_context_variable;​ 
-  ​ 
-SELECT 
-    SYS_CONTEXT('​USERENV',​ '​SESSION_USER'​) 
-  FROM 
-    dual; 
-</​code>​ 
-Ouptut: 
-<​file>​ 
-SYS_CONTEXT('​USERENV','​SESSION_USER'​) ​                                                                       ​ 
---------------------------------------- 
-HR 
-</​file>​ 
-<code sql> ​                       
-  SELECT 
-    SYS_CONTEXT('​empno_ctx',​ '​employee_id'​) 
-  FROM 
-    dual; 
-</​code>​ 
-Ouptut: 
-<​file>​ 
-SYS_CONTEXT('​empno_ctx',​ '​employee_id'​) 
---------------------------------------- 
-1 
-</​file>​ 
-Context myContext: 
-<code plsql> ​ 
-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; 
-</​code>​ 
-Ouptut: 
-<​file>​ 
-SYS_CONTEXT('​MYCONTEXT','​MYVAR'​) ​ 
--------------------------------------- ​ 
-myValue ​   ​ 
-</​file>​ 
  
-===== Documentation / Reference ===== 
-  * [[http://​docs.oracle.com/​cd/​B19306_01/​appdev.102/​b14258/​d_sessio.htm|DBMS_SESSION]] 
-  * [[http://​docs.oracle.com/​cd/​B28359_01/​network.111/​b28531/​app_context.htm|Oracle® Database Security Guide 
-11g Release 1 (11.1) Part Number B28531-15 - 6 Using Application Contexts to Retrieve User Information]] 
db/oracle/session.1334228237.txt.gz · Last modified: 2012/04/12 12:57 by gerardnico