Re: cannot pass entire records to function in SQL command

From: Frank Bergemann <FBergemann_at_web.de>
Date: Thu, 13 Jan 2011 03:57:10 -0800 (PST)
Message-ID: <4c84a96b-46b9-4802-b7f1-31dbe61b58bf_at_v17g2000yqv.googlegroups.com>

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!

rgds,
Frank Received on Thu Jan 13 2011 - 05:57:10 CST