Re: cannot pass entire records to function in SQL command

> 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...http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overvi...http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
>>
>> 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!
>

You can pass a record to a function, but that is not what you are doing.

> select rec.a, funcB(rec) bulk collect into a_values, f_values from
> tabx rec;

is really select tabx.a, funcB(tabx) bulk collect into a_values, f_values from tabx

so what your passing to funcB is a table name, not data selected from the table!

I think hour logic is flawed anyway. Part of the problem is your mising up the sql and plsql layers. While there are clear intefaces that allow you to pass data from one layer to the other, you hav enot got that - you have a circular dependency -

    The select depends on the return value from funcB     The return value from funcB depends on the argument to funcB     The argument to funcB depends on the select     The select depends on the return value from funcB

and around we go.....

Tim

-- 
tcross (at) rapttech dot com dot au