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;

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s