Oracle AQ. Error in Trigger

[Ray Teale]

Oracle Advanced Queueing. RDBMS Enterprise Edition Version 8.1.7.0.0
OS is Solaris for x86

I have an Oracle row level trigger which enqueues a message to a queue on
insert, update or delete.

It works fine if I have an AFTER trigger - but the DBMS_AQ.ENQUEUE()
procedure raises a -604 error if I try to do the same thing on a BEFORE
trigger. (There are specific reasons why I need a BEFORE trigger which
aren't relevant here).

If I comment out the DBMS_AQ.ENQUEUE statement it runs OK so its definitely
the problem.
The payload of the message does contain both :new and :old references

Has anybody seem anything like this before?

Regards

Ray Teale

[Ray Teale]

I realise its bad form to answer your own questions but ........

I have discovered that the problem is related to the fact that the payload
includes a BLOB which is assigned during the trigger execution from a column
in the table:

BEGIN
message := messtype(:new.key, :new.blobcol);

DBMS_AQ.ENQUEUE(queue_name => 'queue1,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
END

... take away the blob (or move the trigger to AFTER) and it works fine. No
idea why.

Regards

Ray

"Ray Teale" <r...@BLAHholly.com.au> wrote in message
news:PGjqb.177$373....@nnrp1.ozemail.com.au...

[Anurag Varma]

Ray,

Pick up the 8i application developers Guide on LOB's.

There you promptly skip to the chapter listing "LOB Restrictions".
You would find:

* You can refer to only the :old values of LOB column in a before trigger
* You can refer to both :new and :old values of LOB column in an after trigger.
* You cannot write to a :new value in either before or after triggers.

I have not checked .. but I believe the restriction has been lifted in 9i.
At least it is no longer listed in the doco as a limitation.

HTH

Anurag

"Ray Teale" <r...@BLAHholly.com.au> wrote in message news:iNkqb.191$373....@nnrp1.ozemail.com.au...

[Ray Teale]

Thanks for that. Its always nice to get a definitive answer.
I made the mistake of assuming it was an AQ problem.

Regards

Ray

"Anurag Varma" <av...@hotmail.com> wrote in message
news:d7lqb.3975$ax....@news02.roc.ny...