I've used "select from table_type" in my program and I follow exactly your syntax. I can compile it but when I run it, the following error returns. Can you tell me why?

ORA-00600: internal server code, arguments:[15419], [severe error during PL/SQL execution]
ORA-06544: PL/SQL: internal error
ORA-06553: PLS-801: internal error
create or replace package body pk_test is

procedure sp_test
is
type myTableType is table of char(2);
l_data myTableType := myTabletype();

ls_item_desc char(20);
begin
dbms_output.enable(50000);

l_data.extend;
l_data(1) := '01';

select item_desc
into ls_item_desc
from item
where item_code in
( select * from
table(cast(l_data as mytableType )));

dbms_output.put_line('item desc: ' || ls_item_desc);

end sp_test;

end pk_test;

don't know what version you are using but it is probably related to this:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure sp_test
  2  is
  3       type myTableType is table of char(2);
  4       l_data    myTableType := myTabletype();
  5
  6       ls_item_desc char(20);
  7  begin
  8       dbms_output.enable(50000);
  9
 10       l_data.extend;
 11       l_data(1) := '01';
 12
 13       select dummy item_desc
 14         into ls_item_desc
 15         from dual
 16        where dummy in
 17          ( select * from
 18             table(cast(l_data as mytableType )));
 19
 20       dbms_output.put_line('item desc: ' || ls_item_desc);
 21
 22  end sp_test;
 23  /

Warning: Procedure created with compilation errors.

ops$tkyte@ORA817DEV.US.ORACLE.COM> show err
Errors for PROCEDURE SP_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/6     PL/SQL: SQL Statement ignored
18/18    PLS-00642: local collection types not allowed in SQL statements


<b>
the types must be defined at the SQL level for SQL to operate on them.
</b>


create or replace type myTableType as table of char(2)
/

create or replace procedure sp_test ......


create the type OUTSIDE the procedure/package. 

Is it possible to cast from a PLSQL RECORD to a SQL TYPE having the same fields, and/or from a PLSQL "index-by" TABLE to a SQL "nested" table?

These would be very useful to have. I suppose I could write a function to do it, but in the case of the table this would be pretty inefficient.

Tom,

The following PL/SQL block gives me an error :
create or replace type myRecordType
as object
( x int,
y date,
z varchar2(25)
)
/
create or replace type myTableType
as table of myRecordType
/
declare
some_data mytabletype := mytabletype();
my_data myrecordtype;
begin
some_data.extend;
some_data(1) := myrecordtype(1,sysdate,'test');
some_data.extend;
some_data(2) := myrecordtype(2,sysdate,'test');
some_data.extend;
some_data(3) := myrecordtype(3,sysdate,'test');

22 : select *
into my_data
24 : from table (cast (some_data as mytabletype))
where rownum = 1;
end;
/
ORA-06550: line 24, column 4:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 22, column 2:
PL/SQL: SQL Statement ignored

However, the following code works correctly and prints :

1 10-FEB-05 test

declare
some_data mytabletype := mytabletype();
my_data myrecordtype;
begin
some_data.extend;
some_data(1) := myrecordtype(1,sysdate,'test');
some_data.extend;
some_data(2) := myrecordtype(2,sysdate,'test');
some_data.extend;
some_data(3) := myrecordtype(3,sysdate,'test');

for x in (
select a.*
from table (cast (some_data as mytabletype)) a
where rownum = 1 )
loop
dbms_output.put_line(x.x || ' ' || x.y || ' ' || x.z);
end loop;
end;
/

Pls advise

Nice solution.

It solves most of my problems, but I would like to know if is it possible to fill a table type with a select query directly.

Thanks!

ops$tkyte@ORA9IR2> create or replace type X as object (a int, b int)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2> create or replace type Y as table of X
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data Y;
  3  begin
  4          select x( rownum, user_id )
  5            bulk collect into l_data
  6            from all_users;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
 

Thanks for the prompt answer.

Hi Tom,

When we try to create following package we got error:

CREATE OR REPLACE PACKAGE FONDSRPT.query_idx_tst AUTHID DEFINER
AS
TYPE idx_his_wgh_1_t IS RECORD (
idx_cus VARCHAR (10),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR (10),
fx_cur VARCHAR (3),
wgh NUMBER,
asof DATE,
val NUMBER,
fx_rate NUMBER
);

TYPE idx_his_wgh_1_ref_t IS REF CURSOR
RETURN idx_his_wgh_1_t;

TYPE idx_his_wgh_1_tab_t IS TABLE OF idx_his_wgh_1_t;

TYPE idx_his_yld_t IS RECORD (
idx_cus VARCHAR2 (10 BYTE),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR2 (10 BYTE),
asof DATE,
yield NUMBER
);

TYPE idx_his_yld_tab_t IS TABLE OF idx_his_yld_t;

TYPE fun_his_dis_t IS RECORD (
--fund history distribution type (Fonds Zeitreihe Ausschüttung)
fon_nr NUMBER,
tra_id NUMBER,
as_of DATE,
price NUMBER,
factor NUMBER
);

TYPE fun_his_dis_ref_t IS REF CURSOR
RETURN fun_his_dis_t;

TYPE fun_his_dis_tab_t IS TABLE OF fun_his_dis_t;

--History Calendar
TYPE his_cal_t IS RECORD(
his_asof DATE,
his_YTD DATE,
his_12M DATE,
his_06M DATE,
his_03M DATE,
his_01M DATE,
his_05D DATE,
his_02D DATE);

TYPE his_cal_tab_t IS TABLE OF his_cal_t;

TYPE his_cal_in_t IS RECORD(his_from DATE, his_to DATE);

TYPE his_cal_in_ref_t IS REF CURSOR RETURN his_cal_in_t;

--

--History Index Calendar
TYPE his_idxcal_in_t IS RECORD (
idx VARCHAR2(10),
asof DATE);

TYPE his_idxcal_out_t IS RECORD (
idx VARCHAR2(10),
asof DATE,
asof_YTD DATE,
asof_12M DATE);

TYPE his_idxcal_in_ref_t IS REF CURSOR RETURN his_idxcal_in_t;

TYPE his_idxcal_out_tab_t IS TABLE OF his_idxcal_out_t;

--

FUNCTION idx_his_wgh_1 (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_wgh_1_tab_t PIPELINED;

FUNCTION idx_his_yld (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_yld_tab_t PIPELINED;

FUNCTION fun_his_dis (c_fun_his_dis fun_his_dis_ref_t)
RETURN fun_his_dis_tab_t PIPELINED;

FUNCTION his_cal(c_his_cal_in_ref_t his_cal_in_ref_t)
RETURN his_cal_tab_t PIPELINED;

FUNCTION his_cal_param (d_from IN DATE, d_to IN DATE)
RETURN his_cal_tab_t PIPELINED;

-- FUNCTION his_idxcal(c_asof IN his_idxcal_in_t, c_YTD IN his_idxcal_in_t) return his_idxcal_out_t PIPELINED;--, c_YTD IN his_idxcal_in_t, c_xxx IN his_idxcal_in_t)
-- RETURN his_idxcal_out_t PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE FONDSRPT.query_idx_tst AUTHID DEFINER
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kokvxsql1], [], [], [], [], [], [],
[]
ORA-00600: internal error code, arguments: [17012], [0x13C4D820], [], [], [],
[], [], []

But this was as existing package which was working fine. recently our development team report they have above problem and they have done nothing changed on the system.

So could you please give me the hint what could be the reason for above error.

Note:
----
We are running oracle on Windows 2000 advanced server. Version as :
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production, Linux

I'm trying to pass a collection to a pipelined function, bulk collecting the results into another collection. I was getting ORA-00947: not enough values; however, applying the solution above gives ORA-00904: invalid identifier.

create type xmfr_rect is object(client_co_id    number,
                                valcheck varchar2(1));
                            
create type xmfr_tt is table of xmfr_rect;

create type isp_rect is object(client_co_id    number,
                                message varchar2(30));

create type isp_tabt is table of isp_rect;

create or replace package pkg_test
as

function xfmr_coll(p_xmfr_coll in xmfr_tt)
return isp_tabt pipelined;

end pkg_test;
/

create or replace package body pkg_test
as

function xfmr_coll(p_xmfr_coll in xmfr_tt)
return isp_tabt pipelined
is

   v_step              varchar2(30);
   vrec_test isp_rect;
   vtab_test isp_tabt;

begin

   vtab_test := isp_tabt();
  
   v_step := 'Starting...';
   
   for l_rec in p_xmfr_coll.first .. p_xmfr_coll.last
   loop
   
      vtab_test.extend;
               
      vtab_test(vtab_test.last).client_co_id := rpad(p_xmfr_coll(l_rec).client_co_id, 3,3);
      vtab_test(vtab_test.last).message := case when p_xmfr_coll(l_rec).valcheck = 'Y' then 'YES' else 'NO' end;
                  
   end loop;
      
   for l_rec in vtab_test.first .. vtab_test.last
   loop
               
      vrec_test := vtab_test(l_rec);
      pipe row (vrec_test);
                  
   end loop;
   
   return;

end xfmr_coll;
end pkg_test;
/

I then run:

declare

   vrec_isp_tt    isp_tabt  := isp_tabt();
   vrec_xfmr_tt   xmfr_tt   := xmfr_tt();     
   v_cnt number;
   
begin
   
   vrec_xfmr_tt.extend;
   
   vrec_xfmr_tt(vrec_xfmr_tt.last) := xmfr_rect(10, 'Y');

   vrec_xfmr_tt.extend;
   
   vrec_xfmr_tt(vrec_xfmr_tt.last) := xmfr_rect(20, 'N');
   
   for lx in 1 .. vrec_xfmr_tt.count
   loop
      dbms_output.put_line ('client_co_id: ' || vrec_xfmr_tt(lx).client_co_id);
      dbms_output.put_line ('valcheck: ' || vrec_xfmr_tt(lx).valcheck);
   end loop;
   
   select isp_rect(client_co_id, message) 
   bulk collect into vrec_isp_tt
   from table(cast(pkg_test.xfmr_coll(vrec_xfmr_tt) as xmfr_tt)); 
   
   if vrec_isp_tt.count > 0
   then
      for lx in 1 .. vrec_isp_tt.count
      loop
         dbms_output.put_line('client_id: ' || vrec_isp_tt(lx).client_co_id || '~' || vrec_isp_tt(lx).message);
      end loop;
      
   else
      dbms_output.put_line('no rows returned');
      
   end if;
   
   vrec_xfmr_tt.delete;

end;
/

I've tried varied permutations of writing the bulk collection query, but just haven't hit upon the right one. What am I not seeing or understanding correctly?
Thank you.

ops$tkyte%ORA11GR2> create or replace package body pkg_test
  2  as
  3  
  4  function xfmr_coll(p_xmfr_coll in xmfr_tt) return isp_tabt pipelined
  5  is
  6  begin
  7      for l_rec in p_xmfr_coll.first .. p_xmfr_coll.last
  8      loop
  9          PIPE ROW ( isp_rect( rpad(p_xmfr_coll(l_rec).client_co_id, 3,3), case when p_xmfr_coll(l_rec).valcheck = 'Y' then 'YES' else 'NO' end ) );
 10      end loop;
 11     return;
 12  end xfmr_coll;
 13  
 14  end pkg_test;
 15  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2     vrec_isp_tt    isp_tabt;
  3     vrec_xfmr_tt   xmfr_tt   := xmfr_tt( xmfr_rect(10,'Y'), xmfr_rect(20,'N') );
  4  begin
  5      for lx in 1 .. vrec_xfmr_tt.count
  6      loop
  7          dbms_output.put_line ('client_co_id: ' || vrec_xfmr_tt(lx).client_co_id);
  8          dbms_output.put_line ('valcheck: ' || vrec_xfmr_tt(lx).valcheck);
  9      end loop;
 10  
 11      select isp_rect(client_co_id, message)
 12        bulk collect into vrec_isp_tt
 13        from table( (pkg_test.xfmr_coll(vrec_xfmr_tt) ));
 14  
 15      dbms_output.put_line( 'printing ' || nvl(vrec_isp_tt.count,0) || ' records...' );
 16      for lx in 1 .. nvl(vrec_isp_tt.count,0)
 17      loop
 18          dbms_output.put_line('client_id: ' || vrec_isp_tt(lx).client_co_id || '~' || vrec_isp_tt(lx).message);
 19      end loop;
 20  end;
 21  /
client_co_id: 10
valcheck: Y
client_co_id: 20
valcheck: N
printing 2 records...
client_id: 103~YES
client_id: 203~NO

PL/SQL procedure successfully completed.

Thanks!
I was able to apply your method successfully to my complete code.

In reply to your questions, for those reading through this thread...
What I was trying to do is convert an existing pipe-lined function, which had 8 parameters as input, to take a collection as input. I was building upon existing code that filled the array and piped the element, I don't know why this had been done, but only can guess they didn't want to list out the elements. However, I found the listing of elements helpful in that I can see what is being sent.

Thanks again,
Steve

Hi Tom;
I need to select from a PL collection type, so I tried this

CREATE OR REPLACE PROCEDURE prc_get_employees_data (
result_cursor IN OUT sys_refcursor
)
AS
TYPE rec$employee IS RECORD (
employee_id NUMBER (6),
first_name VARCHAR2 (20 BYTE),
last_name VARCHAR2 (25 BYTE),
email VARCHAR2 (25 BYTE),
phone_number VARCHAR2 (20 BYTE),
hire_date DATE,
job_id VARCHAR2 (10 BYTE),
salary NUMBER (8, 2),
commission_pct NUMBER (2, 2),
manager_id NUMBER (6),
department_id NUMBER (4)
);

TYPE table$employee IS TABLE OF rec$employee INDEX BY pls_INTEGER;
--
vreftype rec$employee;
vreftable table$employee ;--:= table$employee();
--
j NUMBER := 0;
x NUMBER;

--
CURSOR cur
IS
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date,
e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id
FROM employees e;

--
BEGIN
--vreftype.delete;
FOR rec IN cur
LOOP
j := j + 1;
vreftype.employee_id := rec.employee_id;
vreftype.first_name := rec.first_name;
vreftype.last_name := rec.last_name;
vreftype.email := rec.email;
vreftype.phone_number := rec.phone_number;
vreftype.hire_date := rec.hire_date;
vreftype.job_id := rec.job_id;
vreftype.salary := rec.salary;
vreftype.commission_pct := rec.commission_pct;
vreftype.manager_id := rec.manager_id;
vreftype.department_id := rec.department_id;

--vreftable.extend;
vreftable (j) := vreftype;
END LOOP;

/*OPEN result_cursor FOR
SELECT *
FROM TABLE (CAST (vreftable AS table$employee));*/ -->> PL/SQL: ORA-00902: invalid datatype

OPEN result_cursor FOR
SELECT rownum,
vreftable(rownum).employee_id employee_id,
vreftable(rownum).first_name first_name,
vreftable(rownum).last_name last_name,
vreftable(rownum).email email,
vreftable(rownum).phone_number phone_number,
vreftable(rownum).hire_date hire_date,
vreftable(rownum).job_id job_id,
vreftable(rownum).salary salary,
vreftable(rownum).commission_pct commission_pct,
vreftable(rownum).manager_id manager_id,
vreftable(rownum).department_id department_id
FROM DUAL
CONNECT BY ROWNUM <= j
;
END;
/

But it I get ORA-01403: no data found
and after Investigating the problem I foun that the "rownum" is always equal zero when used as subscript of "vreftable", though if I used it as a column even in the same row with "vreftable(rownum)" it shows the right value.

I tried this because I don't want to use any object out of the procedure, like using TABLE(CAST) with database object but I need everything in one object.

Thank You..

TYPE         SYS_PLSQL_108114_24_1
             SYS_PLSQL_108114_9_1
             SYS_PLSQL_108114_DUMMY_1