Hi Tom,

I am trying to write a procedure which would take a given table and bulk collect into 'table of rowtype' variables along with rowids (table of rowid). Something like below.

create table vtest1 (a number, b number);

1. Works fine

declare
type refCursor is ref cursor;
type rowidTab  is table of rowid;
type vtest1Tab is table of vtest1%rowtype;
rc refCursor;
v_vtest1 vtest1Tab;
v_rowidtab rowidTab;
begin
    open rc for 'select * from vtest1';
    fetch rc bulk collect into v_vtest1;
    null; ---- No actions yet.
    close rc;
end;
/

2. Does not work

declare
type refCursor is ref cursor;
type rowidTab  is table of rowid;
type vtest1Tab is table of vtest1%rowtype;
rc refCursor;
v_vtest1 vtest1Tab;
v_rowidtab rowidTab;
p_batchsize number:=500;
begin
    open rc for 'select *,rowid from vtest1';
    fetch rc bulk collect into v_vtest1,v_rowidtab limit p_batchsize;
    null; ---- No actions yet.
    close rc;
end;
/

fetch rc bulk collect into v_vtest1,v_rowidtab;
*
ERROR at line 10:
ORA-06550: line 10, column 32:
PLS-00597: expression 'V_VTEST1' in the INTO list is of wrong type
ORA-06550: line 10, column 5:
PL/SQL: SQL Statement ignored

However in (2) when I use individual columns of the table in the SELECT and corresponding variables in the INTO clause, then this works fine.

Idea is to write a generic procedure which takes any given table and bulk collects (the key is unknown until tablename is known). At some point it needs to delete the selected rows (hence rowids).

What am I missing in the second case?

Thanks Tom!

Venkatesh