Oracle PL/SQL for DBAs

A Simple Example

Let’s start by using the CREATE CONTEXT command to define a new context named DEPT_CTX. Any user with the CREATE ANY CONTEXT system privilege and EXECUTE privilege on the package DBMS_SESSION can create and set a context.

    SQL> CREATE CONTEXT dept_ctx USING set_dept_ctx;
    Context created.

Note the clause “USING set_dept_ctx.” This clause indicates that an attribute of the dept_ctx context can only be set and changed through a call to the set_dept_ctx procedure .

I have not yet specified any attributes of the context; I have simply defined the overall context (its name and the secure mechanism for changing it). So let’s create the procedure. Inside this procedure, I will assign values to the context attributes using the SET_CONTEXT function from the built-in package DBMS_SESSION, as shown in the following example.

    CREATE PROCEDURE set_dept_ctx (
       p_attr IN VARCHAR2, p_val IN VARCHAR2
    )
    IS
    BEGIN
       DBMS_SESSION.set_context ('DEPT_CTX', p_attr, p_val);
    END;

Now, if I remain connected to the same session that owns this procedure, I can call it directly to set the attribute named DEPTNO to a value 10, as follows:

    SQL> EXEC set_dept_ctx ('DEPTNO','10')
    PL/SQL procedure successfully completed.

To obtain the current value of an attribute, you call the SYS_CONTEXT function , which accepts two parameters—the context name and the attribute name. Here is an example:

    SQL> DECLARE
      2     l_ret   VARCHAR2 (20);
      3  BEGIN
      4     l_ret := SYS_CONTEXT ('DEPT_CTX', 'DEPTNO');
      5     DBMS_OUTPUT.put_line ('Value ...