Re: cannot pass entire records to function in SQL command

From: Tim X <timx_at_nospam.dev.null>
Date: Sat, 22 Jan 2011 11:43:31 +1100
Message-ID: <8739olahu4.fsf_at_puma.rapttech.com.au>



Frank Bergemann <FBergemann_at_web.de> writes:

> 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!
>

I should have also pointed out that you could get around your circular definition by either explicitly listing the columns you need for your function as individual arguments and calling it as funcB(rec.colx, rec.coly) or possibly by using a %rowtype combined with a tabx.* or rec.* i.e. specify columns, not just table name.

Unless it is a large number of columns, I would tend to go with explicit parameter definition for your function as this provides useful documentation and I think is a little safer should the underlying definition of your table change. The %rowtype is very useful, but I think you should be quite specific with parameter definitions for functions. Having said that, I do tend to use %type in my definitions and reserve %rowtype for records used in things like select loops.

Consider the one who will follow you and needs to debug some problem. Are they going to spend hours trying to work out why you pass all these values into the function or is your intent going to be nice and clear and easy to understand? Can they make an educated guess as to what the function's intention is from its name and arguments?

If you do require all the columns for your function, then I would suspect some other problem and would question why you needed all of them to determine join conditions and why this could not just be specified in the where conditions of the joining sql (which has the advantage of making the logic explicit and obvious).

Tim

-- 
tcross (at) rapttech dot com dot au
Received on Fri Jan 21 2011 - 18:43:31 CST

Original text of this message