Sys_contetxt paketi

Sistemden istediğimiz önemli bilgileri bulmamız açısından sys_context pakatei son derece uygundur.

CONNECT OE/password
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
FROM DUAL;

SYS_CONTEXT ('USERENV', 'SESSION_USER')
------------------------------------------------------
OE

SELECT sys_context('USERENV',
'CLIENT_IDENTIFIER') FROM dual;

exec dbms_session.set_identifier(USER
' '  SYSTIMESTAMP);

SELECT sys_context('USERENV',
'CLIENT_IDENTIFIER') FROM dual;

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

SELECT sys_context('USERENV', 'DB_DOMAIN') FROM dual;

SELECT sys_context('USERENV', 'DB_NAME') FROM dual;

SELECT sys_context('USERENV', 'GLOBAL_CONTEXT_MEMORY') FROM dual;

SELECT sys_context('USERENV', 'HOST') FROM dual;

SELECT sys_context('USERENV', 'IDENTIFICATION_TYPE') FROM dual;

SELECT sys_context('USERENV', 'INSTANCE') FROM dual;

SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual;

SELECT sys_context('USERENV', 'LANG') FROM dual;

SELECT sys_context('USERENV', 'LANGUAGE') FROM dual;

SELECT sys_context('USERENV', 'MODULE') FROM dual;

SELECT sys_context('USERENV', 'NLS_CALENDAR') FROM dual;

SELECT sys_context('USERENV', 'NLS_CURRENCY') FROM dual;

SELECT sys_context('USERENV', 'NLS_SORT') FROM dual;

SELECT sys_context('USERENV', 'OS_USER') FROM dual;

SELECT sys_context('USERENV', 'SERVICE_NAME') FROM dual;

SELECT sys_context('USERENV', 'SESSION_USER') FROM dual;

SELECT sys_context('USERENV', 'SID') FROM dual;

SELECT sys_context('USERENV', 'TERMINAL') FROM dual;

bir tanede sys_contextin dısında kendi contextmizi yazabileceğimiz bir paket yapalım.

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;


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;


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;


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;