Hi,
We're in the process of migrating our applications from a standalone environment to a RAC environment. Our applications use dbms_pipe a lot and I recently discovered dbms_pipe does not work in a clustered environment.
I've built a package which is a drop-in replacement for dbms_pipe, but uses AQ (buffered messages) as the message transport. It works fine when I run it from the schema in which I created the package.
However, my intention was to grant execute on the package to the various schemas that are currently using dbms_pipe from their own packages. Those schemas would then create a private synonym called dbms_pipe to my new package and they should just work without changing any code.
That was the plan. Unfortunately when I tried to run a program that uses dbms_pipe, it dies when it calls dbms_aqadm.create_queue. My thinking was to create the queue table in the schema that owns my dbms_pipe replacement package however some of the AQ code seems to run as AUTHID CURRENT_USER which is currently causing me grief. I started off getting this error :
DBD::Oracle::db do failed: ORA-24002: QUEUE_TABLE MSCFW_USER.PIPE_QUEUE_T does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3241
ORA-06512: at "SYS.DBMS_AQADM", line 119
ORA-06512: at "MSC#UTILITY.MSC$PIPE_P", line 129
The schema I am running the program from is MSCFW_USER, and the schema that owns the replacement package (msc$pipe_p) and the queue table is msc#utility.
I've tried doing 'grant all on {queue table} to public', explicitly defining queue_table => 'msc#utility.pipe_queue_t' in the call to create_queue, but now I'm getting an error like this :
DBD::Oracle::st execute failed: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3241
ORA-06512: at "SYS.DBMS_AQADM", line 119
ORA-06512: at "MSC#UTILITY.MSC$PIPE_P", line 129
In any case, all our code runs from a schema that doesn't own any objects, and doesn't have privileges to create objects. All objects are accessed via private synonyms and roles.
Is there any way to solve this?
I'm running on Oracle 11.1.0.7.
Thanks,
Steve