sys_contex应用举例

Syntax：
SYS_CONTEXT('namespace', 'parameter' [, length ])
Purpose：SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

For namespace and parameter, you can specify either a string or an expression that resolves to a string designating a namespace or an attribute. The context namespace must already have been created, and the associated parameter and its value must also have been set using the DBMS_SESSION.set_context procedure. The namespace must be a valid SQL identifier. The parameter name can be any string. It is not case sensitive, but it cannot exceed 30 bytes in length.

测试：

CREATE OR REPLACE CONTEXT App_Ctx using My_pkg ACCESSED GLOBALLY; CREATE OR REPLACE PACKAGE my_pkg IS PROCEDURE set_session_id(p_session_id NUMBER); PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2); PROCEDURE close_session(p_session_id NUMBER); END; / CREATE OR REPLACE PACKAGE BODY my_pkg IS g_session_id NUMBER; PROCEDURE set_session_id(p_session_id NUMBER) IS BEGIN g_session_id := p_session_id; dbms_session.set_identifier(p_session_id); end set_session_id; --=============================================== PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS BEGIN dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id); END set_ctx; --=============================================== PROCEDURE close_session(p_session_id NUMBER) IS BEGIN dbms_session.set_identifier(p_session_id); dbms_session.clear_identifier; END close_session; --=============================================== END; / col var1 format a10 col var2 format a10 exec my_pkg.set_session_id(1234); exec my_pkg.set_ctx('Var1', 'Val1'); exec my_pkg.set_ctx('Var2', 'Val2'); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual; -- Now we'll log out/log in -- At first, the context is empty-but we rejoin the session & there it is disconnect connect uwclass/uwclass SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual; exec my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual; -- Now we'll show that this context is tied to our user (we specified -- USER above, if we used null anyone can join this session). grant execute on my_pkg to scott; conn scott/tiger exec uwclass.my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual; -- Return to the set context again and clear it conn uwclass/uwclass exec my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual; exec my_pkg.close_session(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472985/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472985/