Tuesday, December 21, 2010

Creating a User Defined Context Variable

Creating a user Defined Context Variable

SQL> conn / as sysdba;
SQL> CREATE OR REPLACE CONTEXT my_app_ctx using myuser.ctx_pkg;

Context created

SQL> select * from dba_context where namespace='MY_APP_CTX';

NAMESPACE SCHEMA PACKAGE TYPE
----------- ------- ------- ----------------
MY_APP_CTX MYUSER CTX_PKG ACCESSED LOCALLY

SQL> disc

SQL> conn myuser/password@DBNAME


SQL> CREATE OR REPLACE PACKAGE ctx_pkg IS
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
END;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY ctx_pkg IS
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('my_app_ctx',p_name,p_value);
END set_ctx;
END;
/
Package body created

SQL> exec ctx_pkg.set_ctx(p_name => 'VAR1',p_value => 'VAL1');

PL/SQL procedure successfully completed

SQL> SELECT sys_context('my_app_ctx', 'VAR1') var1 FROM dual;

VAR1
------------
VAL1

No comments:

Post a Comment