Re: Oracle AQ. Error in Trigger

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" <ray_at_BLAHholly.com.au> wrote in message news:iNkqb.191$373.3868_at_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:38:17 CST