Hello Tom,

Please help me I need to get collection records in cursor so that it can be return as a out parameters but I am getting following errors:

Error(30,6): PL/SQL: SQL Statement ignored
Error(30,20): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Error(30,26): PLS-00642: local collection types not allowed in SQL statements

Also below is my procedure:

create or replace procedure POC_PROC (cv_1 OUT SYS_REFCURSOR)as

type tt_SearchMatch_object is record(
ID number,
TRADEID number,
source number,
productID number ,
swapIndicator number,
UPDATEINDICATOR number
);

type tt_SearchMatch_nst is table of tt_SearchMatch_object ;
v_tt_SearchMatch_nst tt_SearchMatch_nst := tt_SearchMatch_nst();

begin
DBMS_OUTPUT.PUT_LINE(to_char(systimestamp,'HH24:MI:SS:FF3')||' Start ');

execute immediate 'create table abc(vid number , v_tt_SearchMatch_nst tt_SearchMatch_nst)
nested table v_tt_SearchMatch_nst store as v_tt_SearchMatch_nst_nt';

select th.id,
th.tRADEID,
th.source,
th.productID,
th.swapindicator,
th.UPDATEINDICATOR bulk collect into v_tt_SearchMatch_nst
from Tradeheader th;

open cv_1 for
select * from table(v_tt_SearchMatch_nst); --Giving errors:Error(30,6): PL/SQL: SQL Statement ignored
--Error(30,20): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
--Error(30,26): PLS-00642: local collection types not allowed in SQL statements

--update table ( select c.v_tt_SearchMatch_nst from abc c) t
-- set t.id = 5;



DBMS_OUTPUT.PUT_LINE(to_char(systimestamp,'HH24:MI:SS:FF3')||' Finish ');

end;