Re: dbms_session.set_context from JDBC fails mysteriously, how can I trap it ?
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 10 Feb 2010 05:45:43 -0800 (PST)
Message-ID: <43e5659d-f2fe-4da1-9217-834962cfac70_at_m16g2000yqc.googlegroups.com>
On Feb 10, 2:54 pm, Krist <krisl..._at_gmail.com> wrote:
> Hi gurus,
>
> Our ADF application connect via JDBC to Oracle DB and using VPD .
> This is done by executing
> dbms_session.set_context('vpd_new','loc_code', pLocCode) at the
> beginning of the session.
>
> Mysteriously, all loc_code succeed to be set resulting in the VPD is
> correctly working, EXCEPT for one loc_code, it doesn't work.
>
> dbms_session.set_context('vpd_new','loc_code', '010') -> works
> dbms_session.set_context('vpd_new','loc_code', '020') -> works
> dbms_session.set_context('vpd_new','loc_code', '030') -> works
> dbms_session.set_context('vpd_new','loc_code', '061') -> DOES NOT
> work.
> -> and it Seems to use the last value set, i.e : '030'
> Other loc_code also works perfectly.
>
> how can I know what happen when the application execute :
> dbms_session.set_context('vpd_new','loc_code', '061')
> Why does it fail ?
>
> Can I know what command is really sent to the Database ?
>
> or can it be other reason ?
>
> Thank you for your help,
> xtanto
Date: Wed, 10 Feb 2010 05:45:43 -0800 (PST)
Message-ID: <43e5659d-f2fe-4da1-9217-834962cfac70_at_m16g2000yqc.googlegroups.com>
On Feb 10, 2:54 pm, Krist <krisl..._at_gmail.com> wrote:
> Hi gurus,
>
> Our ADF application connect via JDBC to Oracle DB and using VPD .
> This is done by executing
> dbms_session.set_context('vpd_new','loc_code', pLocCode) at the
> beginning of the session.
>
> Mysteriously, all loc_code succeed to be set resulting in the VPD is
> correctly working, EXCEPT for one loc_code, it doesn't work.
>
> dbms_session.set_context('vpd_new','loc_code', '010') -> works
> dbms_session.set_context('vpd_new','loc_code', '020') -> works
> dbms_session.set_context('vpd_new','loc_code', '030') -> works
> dbms_session.set_context('vpd_new','loc_code', '061') -> DOES NOT
> work.
> -> and it Seems to use the last value set, i.e : '030'
> Other loc_code also works perfectly.
>
> how can I know what happen when the application execute :
> dbms_session.set_context('vpd_new','loc_code', '061')
> Why does it fail ?
>
> Can I know what command is really sent to the Database ?
>
> or can it be other reason ?
>
> Thank you for your help,
> xtanto
How did you test? How exactly your application initializes each session (code sample might be helpful)? Is your application running in stateful or stateless mode? Might be a side effect of JDBC connection pooling when wrong database connection is being reused. To find out what's going on at the database side, enable SQL tracing with bind variable capture and check generated trace files for clues.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Wed Feb 10 2010 - 07:45:43 CST