Hi Chris/Connors,
I'm facing one problem with inconsistent behavior of DBMS_AQ.REGISTER process. Followed below steps:
1) Created a queue table (multi consumer)
2) Created & Started the Queue (provided all grants)
3) Crated a call back procedure (which will do the DEQUEUE and insert into relevant table)
4) Subscribed the agent for the above queue using DBMS_AQADM.ADD_SUBSCRIBER
5) Registered the call back procedure using DBMS_AQ.REGISTER.
6) ENQUEUE the message into queue for the given agent.
7) Expectation : It should automatically dequeue the message. Step7 is working perfectly fine in DEV instance but not in SANDBOX. Not sure what's the reason, please suggest if any configuration/setup is required like AQ_TM_PROCESSES Parameter or any patches missing in my SANDBOX env? Searched in all forums & metalink, but no clue :(
you can find my code snippet here:
https://livesql.oracle.com/apex/livesql/file/content_GFTHM5TXRH0Y5W78YR4OFCN32.html Here's my product version:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production -------------------------------------------------
Since AskTom was not accepting new questions, i've posted same question under below thread. Pls ignore it, sorry for the inconvenience.
https://asktom.oracle.com/pls/apex/asktom.search?tag=not-able-to-dequeue-messages-automatically-from-advanced-queue
Sorry, I'm not able to reproduce this.
Given this works in one of your databases, you need to figure out what's different between them. Comparing database parameters and AQ configuration is a good way to start.
Also read through the troubleshooting guide:
https://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_trbl.htm#ADQUE2651 The callback procedure may also be hitting an error. If so, you need to find out what this is!
As MOS note 1265138.1 states:
An error can cause the callback procedure to not be executed successfully. This can be seen as an error with the job queue process (jnnn) that executes the callback procedure. The error should be written to the alert log, and a trace file is written. For the case that this note was based on, the following error caused the callback procedure not to run, leaving messages stuck in READY status in the advanced queue:
Fri Aug 20 15:53:23 2010
Errors in file /opt/oracle/admin/w552pr/dump/bg/orcl102a_j000_14645.trc:
ORA-12012: error on auto execute of job 634953
ORA-04031: unable to allocate 39920 bytes of shared memory ("shared pool","DBMS_AQADM_SYS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
ORA-06512: at line 1If you're still stuck when you've gone through the troubleshooting steps, let us know what you've found. And we'll see how we can help.