Re: dbms_session.set_context from JDBC fails mysteriously, how can I trap it ?

> 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.

I'm assuming you are using an app server and connection pooling?

My guess is you have more than one connection in your pool and the handling of the connection in your codebase has a get_conn before the set_context and then a get_conn before the subsequent database operation and those 2 calls don't always result in the same connection. You do have at least 2 database operations happening in this test correct? A set_context operation and then something else (ie you were able to say the set_context "works"). That something else seems to have a different connection than the connection where the set_context happened on.

One thing you can do to test it is change the number of connections in your pool to 1. This will allow you to see if 010 changes to 020 then to 030 then 061 from your code correctly and narrow it down to your connection pool handling.

This is a good reason to put your own package around the dbms_session package. You could easily have logged things within that package, autonmously written things out, ...

> 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 ?

Yes, you can turn trace on for your database or per your connection and then he trace file will have everything you need.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---