Re: * HELP * - Using :NEW in a trigger

I don't know about triggers, but I haven't had a problem returning a row as the second poster indicates. Maybe I didn't understand, but here's a quick sample:

create or replace function abc(pCLLI varchar2) return fac%rowtype is   arow fac%rowtype;
begin
  select * into arow from fac where clli8 = pCLLI;   return arow;
end;
/   

Function created.  

SQL> declare
  2 myrow fac%rowtype;
  3 begin
  4 myrow := abc('MNCHNHCO');
  5 dbms_output.put_line(myrow.city);
  6 end;
  7 .
SQL> set serveroutput on
SQL> /
Manchester  

PL/SQL procedure successfully completed.

You'll notice function ABC *is* returning a row. As a matter of fact, a describe shows:

SQL> descr abc
FUNCTION abc RETURNS RECORD

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
   FAC#                         NUMBER(5)               OUT
   CLLI8                        VARCHAR2(8)             OUT
   MAX_FLOOR                    NUMBER(3)               OUT
   ADDR                         VARCHAR2(30)            OUT
   CITY                         VARCHAR2(20)            OUT
   ST                           VARCHAR2(2)             OUT
   ZIP                          VARCHAR2(5)             OUT
   SBU#                         NUMBER(2)               OUT
   FT_CODE                      VARCHAR2(6)             OUT
   NXT_FR#                      NUMBER(5)               OUT
 PCLLI                          VARCHAR2                IN

I hope this helps more than it confuses.

Sincerely,

Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1B-23
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216

In article 2F4B66E9_at_tek.com, Spencer.H.Moore_at_tek.com (Spencer H Moore) writes:
>In article <3idlkn$3ks_at_alterdial.UU.NET> Morgan Skinner <morgan_at_odo.fisons-lims.com> writes:
>>From: Morgan Skinner <morgan_at_odo.fisons-lims.com>
>>Subject: Re: * HELP * - Using :NEW in a trigger
>>Date: 21 Feb 1995 21:23:35 GMT
 

>>bill.holmes_at_gsa.gov (Bill Holmes) wrote:
>>>
>>> :new and :old enable your trigger to access column values, e.g.
>>> 'if :new.sal > 10000' or 'if :new.sal < :old.sal'.
 

>>Yep, I know that. What I want to do is to be able to pass the whole
>>record to another stored procedure, where I will access any of the
>>field values as necessary. I need to know if it is possible to pass
>>:NEW from a trigger to another stored procedure.
 

>>Thanks in advance.
>
>This isn't possible (at least if it is, I'd like to know!). Nor is it
>possible to pass a record declared as <table>%ROWTYPE to another procedure or
>function. That is, I haven't been able to do it yet.
>
>Spencer Moore
Received on Fri Feb 24 1995 - 02:36:45 CET