Re: Pass an entire Row to a stored Procedure from a Trigger

steph wrote:

> JB wrote:
> > Hi All,
> >
> > I'd like to do something that seems "simple" in PL/SQL, but
> > somehow I haven't managed it.
> > I'm writing code for an INSERT trigger, and I'd like to use the NEW and
> >
> > OLD special variables to pass them to a procedure.
> > It works fine when I pass the content column by column (e.g. :New.ID,
> > :NEW.NAME, etc) but I'd like to pass the whole row in one go.
> > I'd like to do something like that: PROC_TEST( :NEW );
> >
> > Here's what I've tried so far.
> > The trigger is on a materialized view called CP_GMIS.AUDIT_CLIENT with
> > NAME and ID columns.
> > I've declared the procedure as follow: PROCEDURE PROC_TEST( rClient
> > IN CP_GMIS.MV_CLIENT%ROWTYPE );
> >
> >
> > When I compile my code, I get the error PLS-00049: bad bind variable
> > 'NEW'.
> > I searched for clues everywhere and tried various different options
> > without much luck.
> >
> > Could anybody please tell me if what I'm trying to do is possible at
> > all, and what the correct syntax would be?
> >
> > Thanks
> > JB
> >
> > PS: I've also posted this question to the "Oracle PL/SQL" group. Sorry
> > for double posting but I noticed that this group has much more users.
>
> Hi,
>
> AFAIK you can't pass ':NEW' as a record to a pl/sql procedure. I've
> tried this once, and my workaround was declaring a rowtype in the
> trigger, assign every ':NEW'-column to each column of the rowtype
> variable, and pass this record to the called procedure. A bit painful
> but it works.
>
> br,
> stephan

I was hoping it was possible without going throught all the columns one by one.
Thanks very much for your reply Stephan. JB Received on Wed Jul 19 2006 - 04:50:55 CDT