Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sun.

Asked: September 14, 2001 - 12:00 am UTC

Last updated: December 17, 2012 - 3:39 pm UTC

Version: 8i

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Why We not just use the Global varibal to Replace the Sys_context. What is the advantage and disadvantage of it? Thanks!

and Tom said...

o sys_context works in regular SQL. a global variable works only in PLSQL.

o sys_context can be used where a global could not, see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>
for an example. That would be impossible with globals.

o sys_context works (can be set) from a logon trigger, globals would not
o sys_context is dynamic -- you can add whatever variable/value pairs you want to. globals -- static namespace defined at compile time.


But hey -- if the global works for you -- use it. It is strongly typed (number, date, varchar2, etc) and doesn't need to be converted using to_number/to_date.



Rating

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

strongly typed --Ummmmm ??

A reader, September 14, 2001 - 12:14 pm UTC

tell us more about 'strongly typed', and related things

Tom Kyte
September 14, 2001 - 12:19 pm UTC

A sys_context variable doesn't have a datatype -- its just a string.

If you stick a number in there, its a string.
If you stick a date in there, its a string.

Package variables (globals) have a datatype -- number, varchar2(20), date, etc.

If you code:

....
g_global_date number default sysdate;
....

procedure p
is
begin
for x in ( select * from t where x = g_global_date ) loop
....


when we put the date in there, it is stored as a date (it is STRONGLY typed). On the other hand, if we put that into an application context, it'll just be a string and you would have to "to_date" it to make it a date:

for x in ( select * from t where x =
TO_DATE( sys_context('my_ctx','global_date'), 'some date format' ) )



Sun, September 15, 2001 - 12:59 am UTC

Thank you!

Sys context and PLSQL types

tom, June 29, 2002 - 8:43 am UTC

You mention that everything in the sys context is stored as a string....does this mean it is not possible to store a user defined type in the sys context, for example, would I be able to store a record type in the sys context?

Tom Kyte
June 29, 2002 - 10:29 am UTC

Nope, not unless you encoded it into a string. sys_context is a simple "variable name", "variable value" pair where the value is a string.

sys_context

mo, January 29, 2003 - 5:09 pm UTC

Tom:

when I do:

select sys_context( 'userenv', 'os_user' ) from dual;

it gives me my personal unix account name.

I am logging in with generic oracle account (dev/dev) using sql*plus for windows and not logging into unix. how does oracle know who am i. Is it because when dba set the oracle account he linked it to my name somehow.

Thank you,

Tom Kyte
January 30, 2003 - 8:29 am UTC

it is the OS username of the OS you logged in from.

Your windoze account name must be the same as your unix account.

j.

A reader, June 29, 2003 - 6:18 am UTC

hi tom,

is there a way to get session contexts initialized based on ROLES granted to the users creating the sessions?

for example: contexts and roles are bound to applications

application/schema/context/role A
application/schema/context/role B

user X should have context A intialized if role A is granted (in order to use application A).

what is the "best practice" to achieve this or would you in general leave this to the client parts of the applications since they determine the CURRENT role of their users?

Tom Kyte
June 29, 2003 - 9:27 am UTC

you either do that upon login (in a login trigger) or in the application itself. There is no facility to have a role populate a context, however, there are application roles (roles only enabled via a stored procedure call) that would be able to a) enable role and b) setup context.

Datatypes

Martin, March 30, 2004 - 8:05 am UTC 

Hi Tom,

couldn't find an answer to this elsewhere on your site, and it's a quick question anyway. 

Check out the return types of USERENV and SYS_CONTEXT for sessionid :

SQL>  select userenv('sessionid'), sys_context('USERENV', 'SESSIONID') from dual;

USERENV('SESSIONID') SYS_CONTEXT('USERENV','SESSIONID')
-------------------- -------------------------------------------------------------------------------
                3050 3050

As you can see, SQL*Plus seems to be inferring that USERENV returns a numeric, whereas SYS_CONTEXT is a string. 

If sessionid is used in a query against a numeric, presumably it would be "better" to use USERENV in this case? Seems like it's an ideal way to remove any TO_NUMBERs from queries if this is the case. 

Thanks in advance

Tom Kyte
March 30, 2004 - 8:31 am UTC

sys_context is prefered going forward. Just to_number it when appropriate.

Thanks

Martin, March 30, 2004 - 8:36 am UTC


sys_context preferred or call stack parsing??

Ditto, April 23, 2004 - 5:12 pm UTC

Tom,

I'm just trying to write a routine to return the "owner" or "schema" of the current routine (ie Function, package, etc.).

I came across your document containing: "who_called_me" and "who_am_i" functions ...

And then did some more digging and found this: "sys_context" thingy ... did some more searching .. and well .. here I am .. ;)

My question(s) to you ...

1) If all I'm trying to retreive/determine is the schema name (ie I don't care about the function name), should I use sys_context and invoke it using select .. from dual? or use your "who_am_i" and rip the name out ..

and.
2) If I want to retrieve the name of the current routine (ie function, etc.), is my only option to parse the call stack? or can this SYS_CONTEXT thing do that as well? or something else??

Thanks!!


Tom Kyte
April 23, 2004 - 7:34 pm UTC

1) i would use sys_context -- but try to cache it (eg: we should all be using packages, use a package global and set it ONCE instead of each call)

2) well, that would imply that you are using standalone (yuck) functions. Else, you would be getting the package name and then I might just:


create or replace package body foo
as
g_my_name varchar2(30) constant := 'foo';
....


and avoid the overhead, else you are parsing the call stack.

sys_context - how to use it?

Ditto, April 26, 2004 - 9:14 am UTC

Ok, Tom ... again, sorry to bother you, but on the last question we confirmed we should be using sys_context.

I did some trials with it, but I'm not 100% sure how to implement it, as it's reacting a little odd ...

I was thinking of creating a wrapper package around it (so I can make it a little easier for our developers to access - for example - creating a specific function in the wrapper with no parms, and returns the schema owner of the sub-routine who called the wrapper ...)

But sys_context seems to always see the original ID of the session ...

For example:
User A - Function (wrapper) f_who_am_i (returns sys_context ( 'userenv', 'current_schema' ) )

User B - Function (running program) pkg_something.p_something ( misc. code + calls: f_who_am_i )

User C - Logs in, and runs a job that calls: pkg_something.p_something

(I've used 3 users to really show the seperation - note that in our environment, User A actually = User B ... but that's beside the point ..) ;)

When I run this, I always get a response of: "User C" ..

However, if I change the pkg compiled under "User B", to do a Select sys_context from dual (as you mentioned earlier in this thread) ... it returns "User B" ...

And, of course, if I put the select sys_context from dual in the function f_who_am_i under User C ... then I get User C.

The answer I want, of course, is "User B" ... but that means the developers have to write their code to call this "wrapper" function using a select ... from dual; ... but I'm pretty sure, when writing PL/SQL, a developers instinct is going to be to write something like:

lv_var := pkg_wrapper.f_who_am_i;

... and that's not going to work ...

Is there a better way to implement this? Or am I just too hopeful?? :) ... just looking for suggestions/ideas ... or confirmation that I've seriously messed things up .. :)

Thanks!


Tom Kyte
April 26, 2004 - 9:40 am UTC

don't do the wrapper - it returns YOUR schemaid!!

in your wrapper, YOU ARE the current schema!

(authid current_user -- invokers rights -- could be used as well)


but i'd ask, is f() really easier than sys_context()?

don't do the wrapper??

Ditto, April 26, 2004 - 11:57 am UTC

Don't do the wrapper?? I thought it was a good practice to have a wrapper around something like this, to provide both simpler access to developers, as well as ensuring that something changes with it, or your business needs to do something a little differently, you only need to change the code in one place ... not everywhere you called sys_context ..

???

And yes ... f_who_am_i()
Is a little easier than:
sys_context ( 'userenv', 'current_schema' ) ...
Because not only is it longer to type .. and there's 2 hardcoded values in there . ;) but they have to remember which one they want:
'current_schema', 'current_user' or 'session_user' ..

... sorry, not trying to sound confrontational, just honestly confused as to why you'd avoid the wrapper??


Tom Kyte
April 26, 2004 - 2:23 pm UTC

depends on who you ask I suppose. a function with 2 arguments seems as easy as one with none, since they are both just a function call away. but, at the end of the day -- totally up to you.


the wrapper just seems to be hiding "something not very hard". in fact, I would hope the coders would say "hmm, what is this sys_context, what else can i get from it" and be more productive.

you can use authid current_user.

A reader, April 26, 2004 - 1:33 pm UTC

Somewhere in the middle of the page you say (as followup to review by J): "you either do that upon login (in a login trigger) or in the application itself. There is no facility to have a role populate a context, however, there are application roles (roles only enabled via a stored procedure call) that would be able to a) enable role and b) setup context."

I could not understand what you mean by "There is no facility to have a role populate a context". What is the difference between "populate a context" and "setup a context"?

Tom Kyte
April 26, 2004 - 2:27 pm UTC

populate = setup

issuing "set role ...." cannot "setup" or 'populate' a context, that is what I meant.

A reader, April 26, 2004 - 2:47 pm UTC

Well, sys_context being a supplied function would IMHO cancel the need for wrapping it further. This seems like the perennial debate between flexibility and simplicity.

Anyways, in this case the wrapper -- like Einstein's observer -- changes the behavior of the function. So clearly the wrapper is not an option.

Tom Kyte
April 27, 2004 - 3:57 am UTC 

well, technically -- it is an option, you have to use authid current user (if you have expert one on one Oracle -- i beat this to death in the invoker/definer rights chapter).....



ops$tkyte@ORA9IR2> create user a identified by a;
 
User created.
 
ops$tkyte@ORA9IR2> grant create session, create procedure to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user b identified by b;
 
User created.
 
ops$tkyte@ORA9IR2> grant create session, create procedure to b;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create or replace function DR return varchar2
  2  as
  3          l_cschema varchar2(30);
  4  begin
  5      select sys_context( 'userenv', 'current_schema' )
  6            into l_cschema
  7            from dual;
  8          return l_cschema;
  9  end;
 10  /
 
Function created.
 
a@ORA9IR2>
a@ORA9IR2> create or replace function IR return varchar2
  2  AUTHID CURRENT_USER
  3  as
  4          l_cschema varchar2(30);
  5  begin
  6      select sys_context( 'userenv', 'current_schema' )
  7            into l_cschema
  8            from dual;
  9          return l_cschema;
 10  end;
 11  /
 
Function created.
 
a@ORA9IR2> grant execute on DR to b;
 
Grant succeeded.
 
a@ORA9IR2> grant execute on IR to b;
 
Grant succeeded.
 
a@ORA9IR2>
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2>
b@ORA9IR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_output.put_line( 'DR = ' || a.dr );
  5          dbms_output.put_line( 'IR = ' || a.ir );
  6  end;
  7  /
 
Procedure created.
 
b@ORA9IR2> exec p
DR = A
IR = B
 
PL/SQL procedure successfully completed.
 
b@ORA9IR2>

Actuall .. no, it doesn't ...

Ditto, April 26, 2004 - 4:20 pm UTC

In response to the previous reply as quoted:

"Anyways, in this case the wrapper -- like Einstein's observer -- changes the
behavior of the function. So clearly the wrapper is not an option."

.. actually, no it doesn't change the behaviour at all, that's the beauty of it ... :)

The sys_context function will always return the last user who issued a SELECT ... statement in the current call stack that it used to get to the sys_context function. That is:

User A:
Function f_1 ()
Return sys_context ('userenv', 'current_schema' );

User B:
Function f_2 ()
select f_1 into lv_var from dual;
return lv_var;

User C:
Function f_3 ()
select f_2 into lv_var from dual;
return lv_var;

.. if you call function: f_3 ... you'll get a result of: "User B" ... I've tried this a number of times, and hence my questions to Tom .. :)
If you change f_2 to:
Function f_2 ()
Return f_1 ();

Then the result, if you call function: f_3 will be "User C" ...

The behaviour of the function sys_context doesn't change by wrapping it .. only whether or not you use it in a sql statement or not. So when you wrap it, be sure NOT to use it in a sql statement .. and then it will always return the caller's userid ... (assuming they used select ... from dual)
:)

A little strange, maybe ... but it does make sense when you think about it ... and at least it's consistent. :) Meaning you can predict what the result will be.


btw .. Thanks Tom for the replies on my previous posts ... I understand what you're saying now ... I was just afraid you were trying to say something else .. ;)



Tom Kyte
April 27, 2004 - 4:20 am UTC

authid current_user.................

Actually ...

Ditto, April 27, 2004 - 8:51 am UTC

authid current_user doesn't work the way I need it to ..

In my example above ... I need the result of f_3 () to be "User B" ... Because f_3 calls f_2, and it's f_2 that I want to ask the question: "who the heck am I" ...

If I put "authid current_user" in any of those functions (f_1, f_2 or f_3), I always get back: "User C" .. which is not what I want ...

The only way I can get the functions to return: "User B" is to have f_2 use a select ... from dual statement while calling f_1 ...


Tom Kyte
April 28, 2004 - 12:22 pm UTC

the WRAPPER FUNCTION if it is using authid current user (it is the one that needs it -- please read the example above) returns the right answer.


whomever calls the "wrapper" funtion and the wrapper function is authid current user, would get the schema name of "themselves"

it works -- see the example please. it is provided above and does exactly what you asked for.

Thanks

Vaughn Ripley, April 28, 2004 - 10:15 am UTC


Ahh ... I see the light!!

Ditto, April 28, 2004 - 1:51 pm UTC

Got it ... I thought you were doing something else with that authid ... I was trying some tests, but didn't set them up right ... *blush* ... oops ...

Got it know ... that helps alot!!

Thank-you!!


sys_context session hangs.

Vidya, October 10, 2004 - 10:20 pm UTC

Hi Tom,

I have a procedure sp_getresults which dynamically constructs SQL statements and executes them. I have created a context as
create or replace context MY_CTX as sp_getresults;
For most of the parameters, when the query is constructed dynamically, the results are returned instantaneuosly, like lastname, the query for which is shown below:

SELECT /*+ FIRST_ROWS */ a.city, to_char(a.timestamp, 'yyyy-mm-dd hh24:mi:ss')
timestamp,
a.demdatatype , dlid, dlidtype, issuetype, issuedate ,
0 docketnumber FROM ( select /*+ FIRST_ROWS */ a.* , 0 docketnumber
from vdemdata a where a.currentstatus= 1 and a.lastname = sys_context(
'MY'_CTX', 'LASTNAME' )
and rownum <= 100) a
order by a.timestamp desc

But for an instance as described below, when I run the query in SQL* Plus the session just hangs and I don't get any result. If I replace the sys_context statement with an equal comparison, then I get the results back in seconds. I don't know how to debug it and find out where it is going wrong. Please help me in debugging this problem.

Slow query (with sys_context)
--------------------------------------------

SELECT /*+ FIRST_ROWS */ a.city, to_char(a.timestamp, 'yyyy-mm-dd hh24:mi:ss')
timestamp,
a.demdatatype , dlid, dlidtype, issuetype, issuedate ,
0 docketnumber FROM ( select /*+ FIRST_ROWS */ a.* , 0 docketnumber
from vdemdata a where a.currentstatus= 1 and a.eyecolor = sys_context(
'MY'_CTX', 'EYECOLOR' )
and rownum <= 100) a
order by a.timestamp desc

Fast query (replacing sys_context with equal comparison)
------------------------------------------------------------------------------------

SELECT /*+ FIRST_ROWS */ a.city, to_char(a.timestamp, 'yyyy-mm-dd hh24:mi:ss')
timestamp,
a.demdatatype , dlid, dlidtype, issuetype, issuedate ,
0 docketnumber FROM ( select /*+ FIRST_ROWS */ a.* , 0 docketnumber
from vdemdata a where a.currentstatus= 1 and a.eyecolor = 'BRN'
and rownum <= 100) a
order by a.timestamp desc

Thanks,
Vidya.

Tom Kyte
October 11, 2004 - 7:46 am UTC

are you really building a query that looks like this????

SELECT /*+ FIRST_ROWS */ a.city, to_char(a.timestamp, 'yyyy-mm-dd hh24:mi:ss')
timestamp,
a.demdatatype , dlid, dlidtype, issuetype, issuedate ,
0 docketnumber FROM ( select /*+ FIRST_ROWS */ a.* , 0 docketnumber
from vdemdata a where a.currentstatus= 1 and a.lastname = sys_context(
'MY'_CTX', 'LASTNAME' )
and rownum <= 100) a
order by a.timestamp desc


You are aware that gets "100 random rows that meets your predicate, and then sorts them"

is that what you really meant to do? or did you mean to "find the rows, sort them, keep the first 100"?



do you know how to use sql_trace and tkprof? what are the plans like? is eyecolor something "selective"? are we using an index here? do you have statistics? are they current? is eyecolor "skewed" (it seems eyecolor would have all of maybe 5/6 distinct values -- index would be bad and seems it would be very skewed).

find the plan (use sql_trace, use tkprof) and see what the difference in the plan between using sys_context and not using it is.


And consider what I said about your query -- i think it is wrong probably. Most of the time you mean to get the rows, sort them and return the first 100 -- you are not doing that, you are getting "some random 100 rows, and sorting them"

SYS_CONTEXT('USERENV','SESSIONID') returns a value of 0

Barry Karsh, September 06, 2005 - 6:30 pm UTC

Why is a call to SYS_CONTEXT('USERENV','SESSIONID') returning a value of 0 when called from inside FGA handler in 9i? Is there a workaround? Supposed to be fixed version 10g. The govt agencies I work for take there time before going to a new version.

Tom Kyte
September 06, 2005 - 9:20 pm UTC

example, and if this is "supposed to be fixed", why are you not working this via support?

sessionid is very "unreliable" (audsid in v$session), I myself ALWAYS use

(select sid from v$mystat where rownum=1)

eg:

select * from v$session where sid = (select sid from v$mystat where rownum=1)


that works in all cases -- what are you *trying* to do, that always helps to know

Quick question

Mathew Butler, October 03, 2005 - 12:45 pm UTC

Is there a way to use sys_context, but the context be private to a specific user? No other user should be able to select information from the app context.

I'm on 9.2.0.5.

</code> http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_53a.htm#2060929 <code>

Tom Kyte
October 03, 2005 - 8:34 pm UTC

make it such that only that user has execute on the procedure that can SET the context -- and the answer is "yes"

for all others, the context would always return "null"

Difference between 'current_schema' and 'current_user'

Shiju, October 19, 2005 - 3:54 am UTC 

Tom,

Is there any difference between values returned by sys_context ( 'userenv','current_schema' )  and sys_context ( 'userenv','current_user' )  ?

Boths returns the same value in definer rights and invoker rights. So what is the difference between those?

Thanks,
Shiju


***********************************************************DEV@DEV SQL>

create or replace function test_user return number
as
lsUser varchar2(100);
begin
select sys_context ( 'userenv','current_schema' ) into lsUser from dual;
dbms_output.put_line('current_schema:'||lsUser);

select sys_context ( 'userenv','current_user' )  into lsUser from dual;
dbms_output.put_line('current_user:'||lsUser);

select user into lsUser from dual;
dbms_output.put_line('user:'||lsUser);


return 1;
end;
/

DEV@DEV SQL>Grant execute on  test_user to public ;

Grant succeeded.

DEV@DEV SQL>create public synonym test_user for DEV.test_user ;

Synonym created.



DEV@DEV SQL>
DEV@DEV SQL>select test_user from dual;

 TEST_USER
----------
         1

DEV@DEV SQL>exec dbms_output.put_line('');
current_schema:DEV
current_user:DEV
user:DEV

PL/SQL procedure successfully completed.

DEV@DEV SQL>



SCOTT@DEV SQL>select test_user from dual;

 TEST_USER
----------
         1

-- Using 9i client !! so need to use dbms_output.put_line

SCOTT@DEV SQL>exec dbms_output.put_line('');
current_schema:DEV
current_user:DEV
user:SCOTT

PL/SQL procedure successfully completed.

SCOTT@DEV SQL>


***********************************************************
DEV@DEV SQL>

create or replace function test_user return number
authid current_user
as
lsUser varchar2(100);
begin
select sys_context ( 'userenv','current_schema' ) into lsUser from dual;
dbms_output.put_line('current_schema:'||lsUser);
select sys_context ( 'userenv','current_user' )  into lsUser from dual;
dbms_output.put_line('current_user:'||lsUser);
select user into lsUser from dual;
dbms_output.put_line('user:'||lsUser);
return 1;
end;
/


DEV@DEV SQL>select test_user from dual;

 TEST_USER
----------
         1

DEV@DEV SQL>exec dbms_output.put_line('');
current_schema:DEV
current_user:DEV
user:DEV

PL/SQL procedure successfully completed.

DEV@DEV SQL>


SCOTT@DEV SQL>exec dbms_output.put_line('');
current_schema:SCOTT
current_user:SCOTT
user:SCOTT

PL/SQL procedure successfully completed.

SCOTT@DEV SQL>

***********************************************************

Tom Kyte
October 19, 2005 - 7:09 am UTC 

ops$tkyte@ORA10GR2> select sys_context( 'userenv', 'current_schema' ), sys_context('userenv','current_user' ) from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','CURRENT_USER')
-------------------------------------------------------------------------------
SCOTT
OPS$TKYTE



current_schema = the name supplied to resolve referenced schema objects if none is used otherwise.  EG: in an invokers rights routine, the query:

   for x in ( select * from emp )
   loop .....


will have the current_schema put in front of EMP in order to figure out what emp.


A definers rights routine has this replacement done at compile time though.


So, it can be very relevant in an invokers rights routine.



ops$tkyte@ORA10GR2> create table emp as select * from scott.emp where 1=0;

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure p
  2  authid current_user
  3  as
  4      l_cnt    number;
  5  begin
  6      select count(*) into l_cnt from emp;
  7      dbms_output.put_line( 'cnt = ' || l_cnt );
  8  end;
  9  /

Procedure created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec p
cnt = 0

PL/SQL procedure successfully completed.

<b>that read ops$tkyte.emp...</b>

ops$tkyte@ORA10GR2> alter session set current_schema=scott;

Session altered.

ops$tkyte@ORA10GR2> exec p
BEGIN p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'P' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

<b>whoops - cannot find our own procedure - because it was assumed to be SCOTT.P</b>

ops$tkyte@ORA10GR2> exec ops$tkyte.p
cnt = 14

PL/SQL procedure successfully completed.

<b>ran our procedure but it reads scott.emp this time....</b>

Thanks

Shiju, October 21, 2005 - 7:06 am UTC


what is a wrapper

A reader, November 08, 2005 - 10:04 am UTC

Hi

What is a wrapper, seen that term many times in PL/SQL. What is it used for?

Tom Kyte
November 08, 2005 - 10:19 pm UTC

it is sort of what it sounds like.

Usually they simplify the API somehow. You might combine 5 functions together into a single call - so the invoker just calls one "wrapper" routine and the wrapper routine handles the heavy lifting.

How does context operate with the memory?

Alex Makarov, February 06, 2007 - 10:47 pm UTC

Tom, could you describe a backstage of the context? What part of the memory area is context allocated in? On the assumption of the tkprof and autotrace tests it seems like context operates with zero consistent and db block gets, doesn't it?

10gR2
SQL> 
SQL> create or replace package my_ctx is
  2    procedure set_parameter(p_ctx_name varchar2, p_ctx_parameter varchar2
  3    , p_ctx_parameter_value varchar2);
  4  end my_ctx;
  5  /

Package created.

SQL> create or replace package body my_ctx is
  2  procedure set_parameter(p_ctx_name varchar2, p_ctx_parameter varchar2
  3  , p_ctx_parameter_value varchar2) is
  4  begin
  5    dbms_session.set_context(p_ctx_name, p_ctx_parameter, p_ctx_parameter_value);
  6  end;
  7  end my_ctx;
  8  /

Package body created.

SQL> create or replace context usr_ctx using my_ctx;

Context created.

SQL> create or replace context usr_globally_accessed_ctx using my_ctx accessed globally;

Context created.

SQL> begin
  2   my_ctx.set_parameter('usr_ctx','p','test');
  3   my_ctx.set_parameter('usr_globally_accessed_ctx', 'gp', 'test test');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> column ctx_param format a15
SQL> select sys_context('usr_ctx', 'p') ctx_param from dual;

CTX_PARAM
---------------
test

SQL> select sys_context('usr_globally_accessed_ctx', 'gp') ctx_param from dual;

CTX_PARAM
---------------
test test

SQL> set autotrace traceonly statistics
SQL> select sys_context('usr_ctx', 'p') ctx_param from dual;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sys_context('usr_globally_accessed_ctx', 'gp') ctx_param from dual;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL> 
SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select sys_context('usr_ctx', 'p') ctx_param from dual;

CTX_PARAM
---------------
test

SQL> select sys_context('usr_globally_accessed_ctx', 'gp') ctx_param from dual;

CTX_PARAM
---------------
test test

SQL>

tkprof output is:
********************************************************************************

select sys_context('usr_ctx', 'p') ctx_param 
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 82  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

select sys_context('usr_globally_accessed_ctx', 'gp') ctx_param 
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 82  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

Tom Kyte
February 07, 2007 - 1:07 pm UTC

it is stored in your session memory (UGA) for normal context values.

for global application contexts (shareable across sessions) it is in the SGA.

getting all set variables

MIchal Pravda, December 06, 2011 - 7:41 am UTC

Hi,

is it possible to list all "variable names" set in a context?

hypothetical code I am looking for:
--lists all variable names and its values in the context p_context:
for l_variable_name in
(select *
from table(sys_context(p_context)))
loop
select sys_context(p_context, l_variable) into l_value from dual;
dbms_output.put_Line('var:' || l_variable || ', value: ' || l_value);
end loop;

Tom Kyte
December 06, 2011 - 11:27 am UTC

query v$context

List context

Michel Cadot, December 07, 2011 - 4:31 am UTC


You can also have a look at v$globalcontext.
Another way is to use dbms_session.list_context procedure.

Regards
Michel

USERENV

MIchal Pravda, December 07, 2011 - 7:11 am UTC

Thanks,

but both suggestions seems to work only on user contexts. What about

select sys_context('USERENV', 'SID') from dual;

How can I know, that I should query namespace userenv and variable sid (besides documentation, google etc.)?
Tom Kyte
December 07, 2011 - 1:34 pm UTC

sorry, documentation for that one. I suppose you could dump the values into a table

I did a quicky cut and paste as a demo:

ops$tkyte%ORA11GR2> create or replace view userenv_view
  2  as
  3  select 'ACTION' what, sys_context( 'userenv', 'ACTION' ) value from dual union all
  4  select 'AUDITED_CURSORID', sys_context( 'userenv', 'AUDITED_CURSORID' ) from dual union all
  5  select 'AUTHENTICATED_IDENTITY', sys_context( 'userenv', 'AUTHENTICATED_IDENTITY' ) from dual union all
  6  select 'AUTHENTICATION_DATA', sys_context( 'userenv', 'AUTHENTICATION_DATA' ) from dual union all
  7  select 'AUTHENTICATION_METHOD', sys_context( 'userenv', 'AUTHENTICATION_METHOD' ) from dual union all
  8  select 'BG_JOB_ID', sys_context( 'userenv', 'BG_JOB_ID' ) from dual union all
  9  select 'CLIENT_IDENTIFIER', sys_context( 'userenv', 'CLIENT_IDENTIFIER' ) from dual union all
 10  select 'CLIENT_INFO', sys_context( 'userenv', 'CLIENT_INFO' ) from dual union all
 11  select 'CURRENT_BIND', sys_context( 'userenv', 'CURRENT_BIND' ) from dual union all
 12  select 'CURRENT_EDITION_ID', sys_context( 'userenv', 'CURRENT_EDITION_ID' ) from dual union all
 13  select 'CURRENT_EDITION_NAME', sys_context( 'userenv', 'CURRENT_EDITION_NAME' ) from dual union all
 14  select 'CURRENT_SCHEMA', sys_context( 'userenv', 'CURRENT_SCHEMA' ) from dual union all
 15  select 'CURRENT_SCHEMAID', sys_context( 'userenv', 'CURRENT_SCHEMAID' ) from dual union all
 16  select 'CURRENT_SQL', sys_context( 'userenv', 'CURRENT_SQL' ) from dual union all
 17  select 'CURRENT_SQL_LENGTH', sys_context( 'userenv', 'CURRENT_SQL_LENGTH' ) from dual union all
 18  select 'CURRENT_USER', sys_context( 'userenv', 'CURRENT_USER' ) from dual union all
 19  select 'CURRENT_USERID', sys_context( 'userenv', 'CURRENT_USERID' ) from dual union all
 20  select 'DATABASE_ROLE', sys_context( 'userenv', 'DATABASE_ROLE' ) from dual union all
 21  select 'DB_DOMAIN', sys_context( 'userenv', 'DB_DOMAIN' ) from dual union all
 22  select 'DB_NAME', sys_context( 'userenv', 'DB_NAME' ) from dual union all
 23  select 'DB_UNIQUE_NAME', sys_context( 'userenv', 'DB_UNIQUE_NAME' ) from dual union all
 24  select 'DBLINK_INFO', sys_context( 'userenv', 'DBLINK_INFO' ) from dual union all
 25  select 'ENTRYID', sys_context( 'userenv', 'ENTRYID' ) from dual union all
 26  select 'ENTERPRISE_IDENTITY', sys_context( 'userenv', 'ENTERPRISE_IDENTITY' ) from dual union all
 27  select 'GLOBAL_CONTEXT_MEMORY', sys_context( 'userenv', 'GLOBAL_CONTEXT_MEMORY' ) from dual union all
 28  select 'GLOBAL_UID', sys_context( 'userenv', 'GLOBAL_UID' ) from dual union all
 29  select 'HOST', sys_context( 'userenv', 'HOST' ) from dual union all
 30  select 'IDENTIFICATION_TYPE', sys_context( 'userenv', 'IDENTIFICATION_TYPE' ) from dual union all
 31  select 'INSTANCE', sys_context( 'userenv', 'INSTANCE' ) from dual union all
 32  select 'INSTANCE_NAME', sys_context( 'userenv', 'INSTANCE_NAME' ) from dual union all
 33  select 'IP_ADDRESS', sys_context( 'userenv', 'IP_ADDRESS' ) from dual union all
 34  select 'ISDBA', sys_context( 'userenv', 'ISDBA' ) from dual union all
 35  select 'LANG', sys_context( 'userenv', 'LANG' ) from dual union all
 36  select 'LANGUAGE', sys_context( 'userenv', 'LANGUAGE' ) from dual union all
 37  select 'MODULE', sys_context( 'userenv', 'MODULE' ) from dual union all
 38  select 'NETWORK_PROTOCOL', sys_context( 'userenv', 'NETWORK_PROTOCOL' ) from dual union all
 39  select 'NLS_CALENDAR', sys_context( 'userenv', 'NLS_CALENDAR' ) from dual union all
 40  select 'NLS_CURRENCY', sys_context( 'userenv', 'NLS_CURRENCY' ) from dual union all
 41  select 'NLS_DATE_FORMAT', sys_context( 'userenv', 'NLS_DATE_FORMAT' ) from dual union all
 42  select 'NLS_DATE_LANGUAGE', sys_context( 'userenv', 'NLS_DATE_LANGUAGE' ) from dual union all
 43  select 'NLS_SORT', sys_context( 'userenv', 'NLS_SORT' ) from dual union all
 44  select 'NLS_TERRITORY', sys_context( 'userenv', 'NLS_TERRITORY' ) from dual union all
 45  select 'OS_USER', sys_context( 'userenv', 'OS_USER' ) from dual union all
 46  select 'POLICY_INVOKER', sys_context( 'userenv', 'POLICY_INVOKER' ) from dual union all
 47  select 'PROXY_ENTERPRISE_IDENTITY', sys_context( 'userenv', 'PROXY_ENTERPRISE_IDENTITY' ) from dual union all
 48  select 'PROXY_USER', sys_context( 'userenv', 'PROXY_USER' ) from dual union all
 49  select 'PROXY_USERID', sys_context( 'userenv', 'PROXY_USERID' ) from dual union all
 50  select 'SERVER_HOST', sys_context( 'userenv', 'SERVER_HOST' ) from dual union all
 51  select 'SERVICE_NAME', sys_context( 'userenv', 'SERVICE_NAME' ) from dual union all
 52  select 'SESSION_EDITION_ID', sys_context( 'userenv', 'SESSION_EDITION_ID' ) from dual union all
 53  select 'SESSION_EDITION_NAME', sys_context( 'userenv', 'SESSION_EDITION_NAME' ) from dual union all
 54  select 'SESSION_USER', sys_context( 'userenv', 'SESSION_USER' ) from dual union all
 55  select 'SESSION_USERID', sys_context( 'userenv', 'SESSION_USERID' ) from dual union all
 56  select 'SESSIONID', sys_context( 'userenv', 'SESSIONID' ) from dual union all
 57  select 'SID', sys_context( 'userenv', 'SID' ) from dual union all
 58  select 'STATEMENTID', sys_context( 'userenv', 'STATEMENTID' ) from dual union all
 59  select 'TERMINAL', sys_context( 'userenv', 'TERMINAL' ) from dual
 60  /

View created.

ops$tkyte%ORA11GR2> select * from userenv_view;

WHAT                           VALUE
------------------------------ --------------------
ACTION
AUDITED_CURSORID
AUTHENTICATED_IDENTITY         tkyte
AUTHENTICATION_DATA
AUTHENTICATION_METHOD          OS
BG_JOB_ID
CLIENT_IDENTIFIER
CLIENT_INFO
CURRENT_BIND
CURRENT_EDITION_ID             100
CURRENT_EDITION_NAME           ORA$BASE
CURRENT_SCHEMA                 OPS$TKYTE
CURRENT_SCHEMAID               374
CURRENT_SQL
CURRENT_SQL_LENGTH
CURRENT_USER                   OPS$TKYTE
CURRENT_USERID                 374
DATABASE_ROLE                  PRIMARY
DB_DOMAIN                      localdomain
DB_NAME                        ora11gr2
DB_UNIQUE_NAME                 ora11gr2
DBLINK_INFO
ENTRYID
ENTERPRISE_IDENTITY            tkyte
GLOBAL_CONTEXT_MEMORY          0
GLOBAL_UID
HOST                           ora
IDENTIFICATION_TYPE            LOCAL
INSTANCE                       1
INSTANCE_NAME                  ora11gr2
IP_ADDRESS
ISDBA                          FALSE
LANG                           US
LANGUAGE                       AMERICAN_AMERICA.WE8
                               MSWIN1252

MODULE                         SQL*Plus
NETWORK_PROTOCOL
NLS_CALENDAR                   GREGORIAN
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
OS_USER                        tkyte
POLICY_INVOKER
PROXY_ENTERPRISE_IDENTITY
PROXY_USER
PROXY_USERID
SERVER_HOST                    ora
SERVICE_NAME                   SYS$USERS
SESSION_EDITION_ID             100
SESSION_EDITION_NAME           ORA$BASE
SESSION_USER                   OPS$TKYTE
SESSION_USERID                 374
SESSIONID                      78518368
SID                            132
STATEMENTID
TERMINAL                       pts/1

57 rows selected.



I don't know of a table that has these values in them.

PROXY_GLOBAL_UID

Sokrates, March 13, 2012 - 5:52 am UTC 

sokrates@11.2 > select sys_context( 'USERENV', 'PROXY_GLOBAL_UID' ) from dual;
select sys_context( 'USERENV', 'PROXY_GLOBAL_UID' ) from dual
                                                         *
ERROR at line 1:
ORA-02003: invalid USERENV parameter

though otherwise documented on
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm#SQLRF06117
Tom Kyte
March 13, 2012 - 8:12 am UTC

I just filed

Bug 13840214 - SYS_CONTEXT( 'USERENV', 'PROXY_GLOBAL_UID') FAILS WITH ORA-2003

How to update USERENV variables

Jack Wells, December 12, 2012 - 7:45 pm UTC

Tom,

I know that you can update some of the USERENV context variables with DBMS_APPLICATION_INFO (i.e. "MODULE", "ACTION", and "CLIENT_INFO") and DBMS_SESSION (i.e. "CLIENT_IDENTIFIER").

However, I would like to update the value of some of the other USERENV variables, specifically:
"AUTHENTICATED", "AUTHENTICATION_DATA", and "AUTHENTICATION_METHOD". From my research, I am coming to the conclusion that you can only do this with low-level OCI calls in a C program.

Before I resort to creating my own custom CONTEXT object, do you know of a way to update these USERENV context variables from PL/SQL?

Thanks,
Jack

p.s. The reason I am trying to do this is because I am building a custom authentication procedure for a mod_plsql web application.

Tom Kyte
December 17, 2012 - 3:39 pm UTC

you cannot, we use those to tell you information - they are not user settable.



p.s. The reason I am trying to do this is because I am building a custom
authentication procedure for a mod_plsql web application.


that is why we provided the thing you say you have to "resort" to... I don't know why you'd make it sound onerous or anything - it is what they were invented for. Application contexts where introduced to do exactly what you are asking.

Application Contexts are Great!

Jack Wells, December 27, 2012 - 3:50 pm UTC

Hi Tom,

I definitely didn't mean to suggest that Application Contexts are onerous. I think they're a great addition to the database's feature set.

I just did not want to reinvent functionality that is already there (like you've mentioned over and over in the past, too many developers build "new" tools/apps without knowing they already exist in the core database).

Those "AUTHENTICATED", "AUTHENTICATION_DATA", and "AUTHENTICATION_METHOD" variables in USERENV really appeared to me an awful lot like something I could hook into (i.e. OIM, SSO, OID, etc.).

Anyway, thanks for the clarification as always!
Jack

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library