Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page