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

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 Received on Wed Jul 19 2006 - 03:34:19 CDT