Using a RECORD as an IN parameter in a FUNCTION [message #271693] |
Tue, 02 October 2007 09:25  |
harveyb
Messages: 5 Registered: October 2007
|
Junior Member |
|
|
Hi All,
I'm fairly new to PL/SQL
What I'm trying to do is return a record set from a procedure (depending on certain logic) and then use that returned record set to pass through to another plsql function (passing via a parameter). Then the function returns a record set as well based on a SQL statement with the WHERE part finds all records IN the recordset I am passing through via the parameter.
This is what i'm doing:
Procedure 1
CREATE OR REPLACE PACKAGE GetClientID
AS
TYPE client_data_record IS RECORD
(
client_id clientv.client_id%TYPE
);
TYPE client_data_type IS REF CURSOR RETURN client_data_record;
PROCEDURE ReturnClientID
(
client_data_cursor IN OUT client_data_type,
input_account IN clients.client_group%TYPE
);
END GetClientID;
This will return a recordset of client ID's
e.g.
cursor IS GetClientID.client_data_record
ReturnClientID(cursor, "shops");
now cursor will contain
Client_ID
butchers
bakers
green grocers
Now with his returned cursor I want to pass it into another function and use it in my select statement.
Function 2
CREATE OR REPLACE PACKAGE BODY GetOrders
AS
FUNCTION ReturnOrders
(input_client_id_cursor IN GetClientID.client_data_record)
RETURN order_head_data_type IS order_head_return_rows order_head_data_type;
BEGIN
OPEN order_head_return_rows FOR
SELECT * FROM orders
WHERE orders.client_id IN input_client_id_cursor;
RETURN order_head_return_rows;
END;
END GetOrders;
Now doing this
ReturnOrders(cursor);
should now find me all orders where the client_id is in the first returned rows. So basically its saying show me all orders for account of "shops".
The problem is, is it says my WHERE statement
WHERE orders.client_id IN input_client_id_cursor;
it says my input_client_id_cursor is of the wrong type, so its basically saying it doesnt recognise the passed record as a table to query.
Do I need to cast this passed parameter or do i need to pass it in via a different datatype?
I hope i've explained my problem well
|
|
|
Re: Using a RECORD as an IN parameter in a FUNCTION [message #271718 is a reply to message #271693] |
Tue, 02 October 2007 11:29   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hi,
it is a pity, you did not post scripts, from which a testcase could be run. So I will post my own testcase not related to your packages, just to show the possible way.
As stated in documentation, you can pass only expression list or subquery to IN condition. So you shall chose the second option (TABLE) by converting your parameter. I doubt it is possible to do with a CURSOR type (and surely not with RECORD type, as you passed client_data_record type). However it is possible with SQL collection type (TABLE OF or VARRAY), as demonstrated below. But you cannot use PL/SQL type (also demonstrated) nor package type (you use), however you may try it, maybe I am wrong with the last type.
SQL> create table t1 ( c1 integer );
Table created.
SQL> insert into t1 select level from dual connect by level <= 10;
10 rows created.
SQL> declare
2 type tp1 is table of integer;
3 x tp1;
4 y tp1 := tp1( 2, 4, 6, 8, 10 );
5 begin
6 select c1 bulk collect into x
7 from t1
8 where c1 in (select column_value
9 from table( cast( y as tp1 ) ) );
10 dbms_output.put_line( to_char( x.count ) );
11 end;
12 /
from table( cast( y as tp1 ) ) );
*
ERROR at line 9:
ORA-06550: line 9, column 30:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored
SQL> create type tp1 is table of integer;
2 /
Type created.
SQL> declare
2 x tp1;
3 y tp1 := tp1( 2, 4, 6, 8, 10 );
4 begin
5 select c1 bulk collect into x
6 from t1
7 where c1 in (select column_value
8 from table( cast( y as tp1 ) ) );
9 dbms_output.put_line( to_char( x.count ) );
10 end;
11 /
5
PL/SQL procedure successfully completed.
SQL> drop type tp1;
Type dropped.
SQL> drop table t1;
Table dropped.
SQL>
|
|
|
Re: Using a RECORD as an IN parameter in a FUNCTION [message #272614 is a reply to message #271693] |
Fri, 05 October 2007 13:31  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
or possibly something like this:
drop package pkg1;
drop type c_rowset_2;
drop type c_rowset_1;
drop type o_rowset_2;
drop type o_rowset_1;
create or replace type o_rowset_1 is object (a_number number,a_date date,a_string varchar2(30))
/
create or replace type c_rowset_1 is table of o_rowset_1
/
create or replace type o_rowset_2 is object (a_string varchar2(30),a_date date,a_number number)
/
create or replace type c_rowset_2 is table of o_rowset_2
/
create or replace package pkg1 is
function f1 return c_rowset_1;
function f2 (c_rowset_1_p in c_rowset_1) return c_rowset_2;
end;
/
show errors
create or replace package body pkg1 is
function f1 return c_rowset_1 is
begin
return (c_rowset_1(o_rowset_1(1,sysdate,'abc'),o_rowset_1(2,sysdate,'xyz')));
end;
function f2 (c_rowset_1_p in c_rowset_1) return c_rowset_2 is
c_rowset_2_v c_rowset_2;
begin
select cast(multiset(select * from (
select a_string,a_date,a_number
from table(cast(c_rowset_1_p as c_rowset_1))
)
) as c_rowset_2
)
into c_rowset_2_v
from dual;
return c_rowset_2_v;
end;
end;
/
show errors
select *
from table(cast(pkg1.f2(pkg1.f1) as c_rowset_2))
/
SQL> select *
2 from table(cast(pkg1.f2(pkg1.f1) as c_rowset_2))
3 /
A_STRING A_DATE A_NUMBER
------------------------------ --------- ----------
abc 05-OCT-07 1
xyz 05-OCT-07 2
2 rows selected.
Good luck, Kevin
|
|
|