Re: Oracle AQ. Error in Trigger

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" <avdbi_at_hotmail.com> wrote in message news:d7lqb.3975$ax.1467_at_news02.roc.ny...
>
> 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 Thu Nov 06 2003 - 21:16:33 CST