SYS_CONTEXT

In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment.

sys_context( namespace, parameter)

NameSpace :  Is an Oracle namespace that has already been created.

If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

Parameter : Is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.


USERENV is the context (namespace) provided by oracle.


User defined NameSpaces:

Imagine as we are setting the session wide attributes ( like packaged variables ) and those are used within the session. ( ROW LEVEL Security Implementation )

Steps :

1. Create a namespace 

   CREATE OR REPLACE CONTEXT security_context USING security.pkg_security ACCESSED GLOBALLY;

2. Enclose the assignment of name/value pair in a package ( ex:security.pkg_security)

3. Assign (Name/Value) pair using DBMS_SESSIOn.SET_CONTEXT

   DBMS_SESSION.SET_CONTEXT(NAMESPACE => 'security_context'
                            ,ATTRIBUTE => empid
                            ,VALUE => 420
                           );

4. Attach the package to a trigger (DATABASE Level Trigger): Trigger needs to fire at the time of user login.



Example:

Create Or Replace package pkg_security
As
procedure set_attributes ;
end pkg_security;
/

Create Or Replace package body pkg_security
as
    Procedure set_attributes
    is
    Begin
       Dbms_Session.Set_Context('security_context','empid',420);
       Dbms_Session.Set_Context('security_context','sid',Sys_Context('userenv','sid'));
       Dbms_Session.Set_Context('security_context','ssn',123456789);
    end set_attributes;
End pkg_security;
/

create or replace trigger set_session_trigger after logon on database
Begin
Security.pkg_security.Set_Attributes;
end set_session_trigger;
/



login to a user:

select sys_context('security_context','empid),
         sys_context('security_context','sid'),
         sys_context('security_context','ssn')
from dual;

Using Objects & Nested tables.

1. Create an Object at database level 

    -- Can be Imagined as a record ( composite fields with datatypes )

2. Create a Nested table on above Created Object.

3. Use the Nested table type within the Pl/SQL Code.

This helps to avoid to iterate through the Collections and those can be directly used within the sql statements as table.

Examples:  ( @ SQL Level )

Demo 1: 

CREATE or replace TYPE phone AS TABLE OF NUMBER;   

/

CREATE or replace TYPE phone_list AS TABLE OF phone;

/

SELECT t.COLUMN_VALUE

  FROM TABLE(phone(1,2,3)) t;

  

  

Demo 2:

create or replace type emp_obj as object

( empno number,

   deptno number,

   sal number);

   

create or replace type emp_tab is table of emp_obj;

SELECT empno, deptno, sal

  FROM TABLE (emp_tab (emp_obj (1, 10, 1000), emp_obj (2, 20, 2000)));

  

  

Examples : ( @ PL/SQL Level )

CREATE OR REPLACE TYPE emp_temp AS OBJECT

   (EMPNO NUMBER,

    ENAME VARCHAR2 (10),

    SAL NUMBER,

    DEPTNO NUMBER);

CREATE OR REPLACE TYPE emp_temp_tab IS TABLE OF emp_temp;

DECLARE

   emp_table   emp_temp_tab;

BEGIN

   SELECT emp_temp (EMPNO,

                    ename,

                    sal,

                    deptno)

     BULK COLLECT

     INTO emp_table

     FROM emp;

   DBMS_OUTPUT.put_line (emp_table.COUNT);

   -- Access the collection within the sql.  ( No looping )

   INSERT INTO emp_out

          SELECT * -- column names can be specified 

           FROM TABLE (emp_table);

   COMMIT;

   

END;