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.
Possibly related:
Tagged function, sys_context | Post a Comment
There is a similar function called `USERENV`. But based on the [documentation](http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions162a.htm#1019148):
August 8th, 2005, at 3:20 pm #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!
August 9th, 2005, at 4:51 pm #Execellent … It is veryful for me.
February 13th, 2007, at 10:42 pm #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?
May 23rd, 2007, at 12:14 am #