SQL & PL/SQL

Hi,

I've got a challenge for you! :-)

I've got a procedure that has a lot of logic to determine what data should be loaded into a table variable. Because of various application constraints, i can not create a global temporary table. Instead, i'd like to create a table variable and populate it with stuff as i go through the procedure.

The end result of the procedure is that i must be able to pass the results back as a sys_refcursor. This is a requirement that is beyond my control as well.

Is there a way to make this sort of procedure work?

Create Or Replace Procedure Xtst
(
Mu_Cur In Out Sys_Refcursor
)
Is
Type Xdmlrectype Is Record (Col1 Varchar2(66));
Type Xdmltype Is Table Of Xdmlrectype;
Rtn Xdmltype;
Begin
Select Internal_Id Bulk Collect Into Rtn From Zc_State;
open mu_cur for select col1 from table(rtn);
end;
/
---
11/42 PLS-00642: local collection types not allowed in SQL statements
11/36 PL/SQL: ORA-22905: cannot access rows from a non-nested table item
11/19 PL/SQL: SQL Statement ignored
Show Errors;