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;

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;

WHERE orders.client_id IN input_client_id_cursor;

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> 

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.