Hi,

i wonder why this doesn't work:

--------------------- snip ----------------------------------------

set serveroutput on

drop table tabx;
/

drop table taby;
/

create table tabx (
a number,
b number
);
/

create table taby (
a number,
b number
);
/

drop function func;
/

create function func(rec in tabx%rowtype)
return number
is
begin
return 1;
end;
/

declare
begin
for i in 1..1000 loop
insert into tabx values (i, i);
insert into taby values (i, i);
end loop;
commit;

select rec.a, func(rec)
from tabx rec;

end;
/

--------------------- snap ----------------------------------------

i get:
select rec.a, func(rec)
*
ERROR at line 9:
ORA-06550: line 9, column 21:
PL/SQL: ORA-00904: "REC": invalid identifier
ORA-06550: line 9, column 2:
PL/SQL: SQL Statement ignored


Why is it not possible to pass the whole record to a function?

- thanks!

rgds,
Frank

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/B....htm#sthref394
http://download.oracle.com/docs/cd/B....htm#sthref169
http://download.oracle.com/docs/cd/B...b14261/toc.htm

HTH
Thomas

Frank Bergemann wrote:
and if you try func(rec.*) ?

On 12 Jan., 15:25, Thomas Olszewicki <Thom... (AT) cpas (DOT) com> wrote:
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

On Jan 13, 6:57*am, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
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

Frank Bergemann <FBergemann (AT) web (DOT) de> writes:

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

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

Frank Bergemann <FBergemann (AT) web (DOT) de> writes:

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