1

From the Oracle documentation on the enqueue method of DBMS_AQ, it is specified that "Names for objects are specified by an optional schema name and a name. If the schema name is not specified, the current schema is assumed." [1]

The system in question defines all objects in a schema, let's call it DEV. Users are then defined like DEV_AD, and during login, there is a trigger which ALTER SESSIONs their current schema to DEV. This means that when they select * from sometable, they select * from DEV.sometable, and not DEV_AD.sometable. There are no issues with this.

However, when the user tries to enqueue a message on SOMEQUEUE, it doesn't work - the system tries to enqueue on DEV_AD.SOMEQUEUE which doesn't exist, rather than DEV.SOMEQUEUE. By placing DEV.SOMEQUEUE rather than just SOMEQUEUE as the argument to DBMS_AQ.ENQUEUE, the message is successfully queued.

Is this an Oracle bug (or documentation error), and is there a workaround?

If it's a bug, we're still using 10.2.0.4.0 for the next few months. Is it fixed in 11g?

[1] http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_aq.htm#ARPLS081

Andrew
  • 777
  • 1
  • 7
  • 19

2 Answers2

1

After hunting through Oracle support a bit further, it is a bug - see Doc ID 220477.1 in Metalink.

It's fixed in 11.2.0.2; this public link contains more information: https://community.oracle.com/thread/846141 but the fix didn't work for us.

Andrew
  • 777
  • 1
  • 7
  • 19
  • Is there a reason you can't prefix the queue name with the schema? Are you calling this from another stored procedure, or ad hoc? – Alex Poole May 20 '14 at 09:20
1

Given that this is bug 1259821 and the event workaround doesn't work for you or isn't practical to implement, your other option is to fully qualify the queue name by specifying the schema name. Presumably you're trying to avoid hard-coding the schema name in the AQ call, like:

dbms_aq.enqueue(queue_name => 'DEV.SOMEQUEUE', ...);

... because you have common code shared across environments and you want the right schema/queue to be used at runtime, based on the schema set by the logon trigger.

If that is the case then you can use the current schema to specify the full queue name:

dbms_aq.enqueue(queue_name =>
  sys_context('USERENV', 'CURRENT_SCHEMA') || '.SOMEQUEUE', ...);

At runtime, for your DEV_AD user, that will still pass 'DEV.SOMEQUEUE' as the queue name.

That is a bit of a pain in ad hoc code (if you're calling from an anonymous block), but not too bad if you are queueing from a stored procedure. And from the bug description it seems like it's only an issue in stored code anyway, as the definer/invoker rights aren't relevant for an anonymous block.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318