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