Skip to Main Content
  • Questions
  • CLIENT_IDENTIFIER and current session

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, J.

Asked: January 01, 2016 - 4:53 pm UTC

Last updated: January 03, 2016 - 12:43 am UTC

Version: Oracle 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi

I have a number of pl/sql package.procedures that are making use of global context variables. I am setting the CLIENT_ID with DBMS_SESSION.set_identifier(my_client_id); and can see it has been set with sys_context('USERENV','CLIENT_IDENTIFIER') . After I have done the processing I need (determining if a user has logged in and successfully been authorized) I redirect to the application. The application wants to then look at the global contexts to determine if they have been set or not, but for some reason it appears my redirect 'looses' the client_id. I am doing a redirect like this
HTP.p('<script type="text/javascript">window.location.href="'||my_url||'"</script>'); (and have tried a number of variations).
Could I be losing my client id with this redirect or am I off in the weeds and should be looking somewhere else for the cause of the problem? I think my problem may be that I do not understand what is considered the "current session".
Thank you for any clues

and Connor said...

A lot depends on if/how connection pooling or similar is setup, because each request may be going to a different session that exists on the database, or even a brand new session (eg if all existing sessions that serve requests were busy).

Hence typically for usage of global contexts, the very first part of *every* request is a call to DBMS_SESSION.set_identifier. Effectively, its a "Hey, its 'me' I'm back again" announcement back to the database.

If somewhere in your midst (redirect or otherwise) you potentially have a call that does not set the client_id, then it could be lost, and hence you wont see the global context information you might have been expecting.

Hope that makes sense.

eg


SQL> create or replace context DEMO using p accessed globally;

Context created.

SQL>
SQL> create or replace procedure p(p_attr varchar2) is
  2  begin
  3    dbms_session.set_context('DEMO','ATTR',p_attr,client_id=>sys_context('USERENV','CLIENT_IDENTIFIER'));
  4  end;
  5  /

Procedure created.

SQL> exec dbms_session.set_identifier('ME');

PL/SQL procedure successfully completed.

SQL> exec p('MY_VALUE');

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> create or replace
  2  procedure good_proc is
  3  begin
  4    dbms_session.set_identifier('ME');
  5    dbms_output.put_line(sys_context('DEMO','ATTR'));
  6  end;
  7  /

Procedure created.

SQL>
SQL> create or replace
  2  procedure bad_proc is
  3  begin
  4    dbms_output.put_line(sys_context('DEMO','ATTR'));
  5  end;
  6  /

Procedure created.


So good_proc always sets the client_id and bad_proc does not. Under SOME situations, both will appear to work fine.

SQL> exec good_proc
MY_VALUE

PL/SQL procedure successfully completed.

SQL> exec bad_proc
MY_VALUE

PL/SQL procedure successfully completed.

SQL>


But bad_proc only worked because good_proc ran first, and they were in the same session. If I reconnect ...

SQL> conn scott/tiger
Connected.

SQL> set serverout on
SQL> exec bad_proc

PL/SQL procedure successfully completed.

SQL> exec good_proc
MY_VALUE

PL/SQL procedure successfully completed.



then bad_proc has lost its way.

Rating

  (2 ratings)

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

Comments

Very Prompt Response

J Pack, January 02, 2016 - 2:33 am UTC

Thank you for such a prompt response on a Friday evening and a holiday at that...
Yes we are using connection pooling but I am not aware of its setup other than to know we use it.
I was hoping to find an obvious place where I was not setting the identifier. I put debugs in the code and I can see that I have the client_identifier right up to where I redirect. Just for grins I even did DBMS_SESSION.set_identifier(l_client_id); right before the HTP.p('<script type="text/javascript">window.location.href="'||p_submit_url||'"</script>'); but still sys_context('USERENV','CLIENT_IDENTIFIER') shows null after the redirect occurs. If sys_context('USERENV','SESSIONID') is an indicator of my current session, I do see it changing frequently but I don't lose client_id until that redirect. I will keep looking! Thank you jp
Connor McDonald
January 02, 2016 - 2:49 am UTC

I assume that "p_submit_url" is another plsql based page ? If so, its in *that* routine you want to ensure that you are immediately setting the client id

Hope this helps.

Almost there

J Pack, January 02, 2016 - 4:11 pm UTC

Yep that is the rub for me...when I get to the p_submit_url procedure I have lost the value I want to use for client_identifier. And I don't believe I want to pass it along in the link. I know I must be missing the obvious. So close but not quite!
Connor McDonald
January 03, 2016 - 12:43 am UTC

That sounds more then like you want to save some state between calls. The place for that is a table :-)

So you would have some sort of random/unique identifier representing the *session*, and that is passed along from call to call. That is a key into a session state table, which would contain client id (and anything else you needed).

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