RE: time of prepare - solved

Found my problem.  
Sorry to bother.

I ran part and pieces from sqlplus and determined that
part of the code to set role had a meaning change
between Oracle 8 and Oracle 9.  We went from 8 to 11
so that explains that.

I believe I have it working again.





-----Original Message-----
From: Howard, Chris [mailto:[email protected]] 
Sent: Friday, February 24, 2012 1:14 PM
To: '[email protected]'
Subject: time of prepare ?

I have a DBD::Oracle script which needs some help.

I have other DBD::Oracle scripts working ok, so I think
my installation is fine.  

This particular script attempts to integrate into a larger
application's job security features.

The application vendor supplied me with some wrapped procedures
and an outline for how to make this work.  And I used
it in the past.  But we recently moved to Oracle 11
and the script no longer works.

The tricky part uses a PL/SQL block to run a stored
procedure which figures out what "role" should be in play
for this particular user when running this particular job.
It then does a dbms_session.set_role, and away we go.

That all seems to work, as far as I can tell.  But
I am failing on a later prepared select of a table
for which I need that role's permissions.

The prepare statement occurs in the file after the
role setting statement.

My question is:  is there some forward-in-time
action going on which causes the prepare to happen
before my role is properly set?  And if so, how
do I defeat that feature?

Chris