SYS_CONTEXT in Oracle
Oracle has a very useful built-in function called SYS_CONTEXT. The syntax of this function goes like this:
SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session. Go ahead and run the following query:
select
sys_context('USERENV','AUTHENTICATION_TYPE')
,sys_context('USERENV','CURRENT_SCHEMA')
,sys_context('USERENV','CURRENT_SCHEMAID')
,sys_context('USERENV','CURRENT_USER')
,sys_context('USERENV','CURRENT_USERID')
,sys_context('USERENV','DB_DOMAIN')
,sys_context('USERENV','DB_NAME')
,sys_context('USERENV','HOST')
,sys_context('USERENV','INSTANCE')
,sys_context('USERENV','IP_ADDRESS')
,sys_context('USERENV','ISDBA')
,sys_context('USERENV','LANG')
,sys_context('USERENV','LANGUAGE')
,sys_context('USERENV','NETWORK_PROTOCOL')
,sys_context('USERENV','NLS_CALENDAR')
,sys_context('USERENV','NLS_CURRENCY')
,sys_context('USERENV','NLS_DATE_FORMAT')
,sys_context('USERENV','NLS_DATE_LANGUAGE')
,sys_context('USERENV','NLS_TERRITORY')
,sys_context('USERENV','OS_USER')
,sys_context('USERENV','SESSION_USER')
,sys_context('USERENV','SESSION_USERID')
,sys_context('USERENV','SESSIONID')
,sys_context('USERENV','TERMINAL')
from dual
The information returned can be pretty handy sometimes. Consult the documentation for more information.
Related articles:
- About The BUILTIN, FIPSFLAG and INTERFACE Pragmas in Oracle
- Oracle Database 11g Old Feature: Internet Addressing
- I use Oracle Database 10g Express Edition to …
There is a similar function called
USERENV. But based on the documentation:Yes!
We’re on the same page. I discovered this handy thing around the same time:
http://thinkoracle.blogspot.com/2005/07/which-instance-am-i-in.html
Very handy!
Execellent … It is veryful for me.
Hi All
I have a database on which users can execute queries. To ensure they cant view each others information I want to execute this query first: dbms_session.set_context(‘usernameParam’, ‘username’, username); I then want to create views that look like this: CREATE OR REPLACE VIEW “MyView” AS SELECT * FROM “DB”.”MyTable” WHERE “Username” = SYS_CONTEXT(‘usernameParam’, ‘username’) / I then want my clients to use the views instead of tables.
I am doing this using Java. I get a database connection. Using that connection I execute the set_context query. I am wandering, how thread safe is this? If 2 clients at the same time execute a query is it possible that the set_context will be execute in the wrong order and clients see each others information or does the set_context apply only to the current connection even thought the same username is used to connect to the database?