Cannot read value from SYS_CONTEXT

Posted by AppleGrew on Stack Overflow See other posts from Stack Overflow or by AppleGrew
Published on 2011-11-25T12:28:18Z Indexed on 2011/11/25 17:50 UTC
Read the original article Hit count: 361

I have a PL/SQL procedure which sets some variable in user session, like the following:-

Dbms_Session.Set_Context(
    NAMESPACE =>'MY_CTX',
    ATTRIBUTE => 'FLAG_NAME',
    Value => 'some value');

Just after this (in the same procedure), I try to read the value of this flag, using:-

SYS_CONTEXT('MY_CTX', 'FLAG_NAME');

The above returns nothing. How did the DB lose this value? The weirder part is that if I invoke this proc directly from Oracle SQL Developer then it works. It doesn't work when I invoke this proc from my web application from callable statement.

--EDIT--

Added an example as to how we are invoking the proc from our Java code.

String statement = "Begin package_name.proc_name( flag_val => :1); END;";
OracleCallableStatement st = <some object by some framework>
    .createCallableStatement(statement);
st.setString(1, 'flag value');
st.execute();
st.close();

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about web-applications