PL/SQL populating a cursor from a pl/sql table
Posted on 2003-03-20
I want to populate a cursor from a pl/sql table. The components are defined like this:
TYPE rtype is RECORD(
TYPE ttype is TABLE OF rtype INDEX BY BINARY INTEGER;
TYPE ref_cursor_type is REF CURSOR;
CREATE OR REPLACE PROCEDURE dtp (cur_in IN OUT ref_cursor_type)
dtp_temp_tab ttype := ttype();
-- Fill up the record, this happens lots of times in this procedure in varying ways under different conditions
rtype.col1 := value1;
rtype.coln := valuen;
-- Insert the record into the table (this may happen many times in the procedure)
dtp_temp_tab(dtp_temp_tab.last) := dtp_temp_row;
-- Finally at the end of the procedure and want to return the rows in the table using a cursor
OPEN cur_in FOR 'SELECT * FROM TABLE(CAST(dtp_temp_tab AS ttype))';
I get back:
ORA-00904: invalid column name
I'm running Oracle 8i 8.1.7.
I don't want to use a table of objects because I want the table in my procedure to be able to change as the columns in the tables in the database change. I don't want to use a VARRAY because I cannot reasonably set an upper limit. 10,000 is probably the upper limit but that is an extraordinary amount of space to reserve.
I thought that the TABLE(CAST()) combo worked on collections, but I'm suspicious that the pl/sql table is not considered a collection, or I haven't called it properly.
Thanks in advance...This is my first question, so if I have been incomplete or screwed it up somehow, don't hesitate to tell me.