Databases
StackOverflow2013
Postsdbo
POOracle trigger to queue/dequeue doesn't fire
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle trigger to queue/dequeue doesn't fire
    primarykey
    data
    text
    <p>Oracle 11g. I have a trigger which calls a procedure. That procedure performs queue and dequeue. When I run the procedure in sqldeveloper, it runs fine. If I update the tables which the trigger fires off, the procedure runs fine. If I go into the application in question and run a process which causes the trigger to fire, the procedure doesn't work. Specifically the queue and dequeue don't work because if I remove the queue/dequeue code, and instead do an insert on a table, the procedure runs fine.</p> <p>So I'm thinking that maybe there's a permissions problem causing the trigger unable to execute on dbms_aq. Does anyone know how to resolve this? Is there a user account that runs the triggers which needs to be given privileges?</p> <p>I don't think the problem is with the procedure because it runs fine. I'm also not passing any dynamic data from the trigger so the way I run the procedure under sqldeveloper is exactly the same way the trigger is calling the procedure.</p> <p>Note: sanitized code</p> <pre><code>create or replace TRIGGER mytrig AFTER INSERT ON INVENTORY_TRANSACTION FOR EACH ROW WHEN ((NEW.CLIENT = 'abcwidgets') AND (NEW.CODE = 'Receipt')) BEGIN dschema.mypack.queue_receipt('abcwidgets','999','bbb','1','88'); EXCEPTION WHEN OTHERS THEN NULL; END; PROCEDURE QUEUE_RECEIPT( CLIENTID IN VARCHAR2 , RECEIPTID IN VARCHAR2 , SITEID IN VARCHAR2 , LINEID IN VARCHAR2 , UPDATE_QTY IN VARCHAR2 ) AS ctxHandle dbms_xmlgen.ctxHandle; l_xml xmltype; queueopts DBMS_AQ.ENQUEUE_OPTIONS_T; msgprops DBMS_AQ.MESSAGE_PROPERTIES_T; msgid RAW(16); BEGIN ctxHandle := dbms_xmlgen.newContext('SELECT line_id, user_data_4 FROM inventory '); l_xml := xmltype(dbms_xmlgen.getxml(ctxHandle)); DBMS_AQ.ENQUEUE ('DSCHEMA.ABCWIDGETS_QUEUE', queueopts, msgprops, l_xml, msgid); END QUEUE_RECEIPT; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRalph
    1. USRalph
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle trigger to queue/dequeue doesn't fire
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you explain what "doesn't work" means in this particular case? Do you get an error? If so, what error? If you don't get an error, what behavior are you seeing or not seeing that you expect? Can you post some sample code that replicates the issue?
      singulars
      1. POOracle trigger to queue/dequeue doesn't fire
      1. USJustin Cave
    2. COBy doesn't work I mean that looking at the corresponding queue table, it remains empty. When running the procedure under sqldeveloper I don't get any errors and I see a new entry in the queue table.
      singulars
      1. POOracle trigger to queue/dequeue doesn't fire
      1. USRalph
    3. COI'm not sure whether posting the source code will be useful at this stage because the procedure works fine and also I'm using hard coded values to remove any possibility of invalid inputs.
      singulars
      1. POOracle trigger to queue/dequeue doesn't fire
      1. USRalph
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload