Hello,
first please consider following working, simplified example:
create table test_table
(
field_1 number,
field_2 varchar2(4000)
);
/
create or replace package test_pkg
as
function test_f(
test_row test_table%rowtype
) return varchar2;
procedure test_p;
end test_pkg;
/
create or replace
package body test_pkg
as
function test_f(
test_row test_table%rowtype
) return varchar2
is
begin
return 'I only accept rowtype!';
end test_f;
procedure test_p
is
str varchar2(32767);
begin
for test_rec in (select * from test_table)
loop
str := test_f(test_rec);
end loop;
end test_p;
end test_pkg;
/
Now I need to extend the package because I need the number of records returned by the select query (lets say the application first wants to know how many records are returned before loading them or not loading them if there are to many records).
So, i just extend the loop in the procedure test_p like:
for test_rec in (select count(*) over() as number_of_recs, t.* from test_table t)
loop
str := test_f(test_rec);
end loop;
Then I get following error:
Error(22,12): PLS-00306: wrong number or types of arguments in call to 'TEST_F'
Right, because the function only accepts test_table%rowtype. My idea was to create a record in the package like this:
create or replace
package body test_pkg
as
--1. Created a type
type test_type is record
(number_of_recs number,
test_table_row test_table%rowtype);
--This function remains as it is
function test_f(
test_row test_table%rowtype
) return varchar2
is
begin
return 'I only accept rowtype!';
end test_f;
procedure test_p
is
str varchar2(32767);
--2. Created a local variable
l_record test_type;
begin
for test_rec in (select count(*) over() as number_of_recs, t.* from test_table t)
loop
--Cannot compile the lines below because I'm getting
--Error(29,17): PLS-00382: expression is of wrong type
--l_record := test_rec;
--l_record := cast (test_rec as test_type);
--This was my intention: to do l_record.test_table_row because this is test_table%rowtype
--str := test_f(l_record.test_table_row);
null;
end loop;
end test_p;
end test_pkg;
(1) Could you please explain me why I'm getting this error messages. Actually, the select returns the number of records and row of a table. That is exactly the information a defined in the test_type.
Now I try the other way around:
create or replace
package test_pkg
as
--1. Created a type in the package header
type test_type is record
(number_of_recs number,
test_table_row test_table%rowtype);
Now the body:
...
--2. This time I've changed the the datatype of the IN parameter
-- need to change the package header first before compiling it in the body
function test_f(
--test_row test_table%rowtype
test_in test_type
) return varchar2
is
begin
...
procedure test_p
is
str varchar2(32767);
--2. Created a local variable
l_record test_type;
begin
for test_rec in (select count(*) over() as number_of_recs, t.* from test_table t)
loop
--l_record := test_rec;
--l_record := cast (test_rec as test_type);
--Trying to compile the line below I get
--Error(35,12): PLS-00306: wrong number or types of arguments in call to 'TEST_F'
--str := test_f(test_rec);
--Trying to compile the line below I get:
--Error(39,25): PLS-00382: expression is of wrong type
str := test_f(cast (test_rec as test_type));
null;
end loop;
end test_p;
...
Same thing actually like above. Could you please explain why it is not the same type? From a more experienced colleague I heard that plsql type is somehow known at compile time (yes, it is part of the source code actually). But %rowtype needs to be somehow "resolved" later because it is a kind of "schema dependent" type.
I have already seen some similar posts/blog entires. The answer is usually - name the columns explicitely. I really don't like this solution since rowtype gives much more flexibility: if new columns come in only need to change the fuction's body without touching the function's signature.
The workarounds I see is to replicate the sql statement selecting once "select count(*) ..." second tme "select * ...". Or make a dynmic sql statement selecting count(*) instead of * if necessary.
Any more elegant workaround is appreciated.
Thank you very much in advance for dealing with a lot of text. Any hint appreciated.
Kind regards,
Anton