Re: cannot pass entire records to function in SQL command

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Thu, 13 Jan 2011 12:17:08 -0800 (PST)
Message-ID: <59ab327c-7d0a-4f65-9ffe-68795d454189_at_l7g2000vbv.googlegroups.com>



On Jan 13, 6:57�am, Frank Bergemann <FBergem..._at_web.de> wrote:
> On 12 Jan., 15:25, Thomas Olszewicki <Thom..._at_cpas.com> wrote:
>
>
>
>
>
> > >>Why is it not possible to pass the whole record to a function? <<
>
> > It is, but you are not passing a record to your function.
> > In your code "rec" is an alias for the table tabx and not a record.
> > Second problem, in Pl/Sql block you cannot just use "select...".
> > You must use cursor or "select...into".
> > Change your code to:
>
> > declare
> > � �sRec tabx%rowtype;
> > begin
> > � � � � for i in 1..1000 loop
> > � � � � � � � � insert into tabx �values (i, i);
> > � � � � � � � � insert into taby �values (i, i);
> > � � � � end loop;
> > � � � � commit;
>
> > � � � � select * into sRec from tabx where a=1; make sure this select
> > fetches only one row.
> > � � � � func( sRec);
>
> > end;
>
> >http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundam...
>
> > HTH
> > Thomas
>
> You decoupled the function invocation from the SQL command
> (first SQL, then function)
> I know that this works.
> But i want to be able to invoke the function right away in the SQL.
>
> let me re-write a bit the example:
> -------------------------- snip
> -----------------------------------------
> set serveroutput on
>
> drop table tabx;
> /
>
> create table tabx (
> � � � � a number,
> � � � � b number
> );
> /
>
> drop function funcA;
> /
>
> create function funcA(rec in tabx.a%type)
> return number
> is
> begin
> � � � � return 1;
> end;
> /
>
> drop funcB;
> /
>
> create function funcB(rec in tabx%rowtype)
> return number
> is
> begin
> � � � � return 1;
> end;
> /
>
> declare
> � � � � type ton is table of number;
> � � � � a_values ton;
> � � � � f_values ton;
> begin
> � � � � for i in 1..1000 loop
> � � � � � � � � insert into tabx �values (i, i);
> � � � � � � � � insert into taby �values (i, i);
> � � � � end loop;
> � � � � commit;
>
> -- � � �-- this one works
> -- � � �select rec.a, funcA(rec.a) bulk collect into a_values, f_values
> from tabx rec;
>
> � � � � -- this does not work
> � � � � select rec.a, funcB(rec) bulk collect into a_values, f_values �from
> tabx rec;
>
> -- � � �-- this also does not work
> -- � � �select rec.a, funcB(rec.*) bulk collect into a_values, f_values
> from tabx rec;
>
> end;
> /
> -------------------------- snap
> -----------------------------------------
>
> I need this within the SQL in order to be able to evaluate the result
> of function call directly in the SQL as well - e.g. to drive joining
> more tables properly.
>
> So is there any way to pass an entire records to a function?
> If not: why not?
>
> - thanks!
>
> rgds,
> Frank- Hide quoted text -
>
> - Show quoted text -

Frank,
I'm not aware of any possibility of doing it the way you wanted. I would pass more values from the table into a functions as parameters:
select rec.a, rec.b, funcC( rec.a, rec.b,...) from tabx rec; As for the questions "why" - I don't know. Maybe because record is pl/sql construct and cannot be automatically created by SQL engine.
HTH
Thomas Received on Thu Jan 13 2011 - 14:17:08 CST

Original text of this message