Re: Oracle AQ. Error in Trigger

From: Ray Teale <ray_at_BLAHholly.com.au>
Date: Thu, 6 Nov 2003 16:18:32 +1100
Message-ID: <iNkqb.191$373.3868@nnrp1.ozemail.com.au>

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" <ray_at_BLAHholly.com.au> wrote in message news:PGjqb.177$373.3285_at_nnrp1.ozemail.com.au...
> 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
>
>
>
>
Received on Wed Nov 05 2003 - 23:18:32 CST