Home » SQL & PL/SQL » SQL & PL/SQL » Using a RECORD as an IN parameter in a FUNCTION
Using a RECORD as an IN parameter in a FUNCTION [message #271693] Tue, 02 October 2007 09:25 Go to next message
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 Smile
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: detailed datafile size
Next Topic: ORA-00902: invalid datatype
Goto Forum:
  


Current Time: Sat Feb 15 11:30:27 CST 2025