bfish
asked on
PL/SQL populating a cursor from a pl/sql table
I want to populate a cursor from a pl/sql table. The components are defined like this:
TYPE rtype is RECORD(
the_key another_schema.another_tab le.key_col umn%TYPE,
...);
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)
IS
dtp_temp_tab ttype := ttype();
dtp_temp_row rtype;
BEGIN
-- 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.extend;
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))';
END;
/
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.
TYPE rtype is RECORD(
the_key another_schema.another_tab
...);
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)
IS
dtp_temp_tab ttype := ttype();
dtp_temp_row rtype;
BEGIN
-- 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.extend;
dtp_temp_tab(dtp_temp_tab.
...
-- 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))';
END;
/
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.
You can't SELECT from PL/SQL tables, only from tables based on TYPEs defined in the server, e.g.
SQL> CREATE TYPE rtype AS OBJECT ( the_key NUMBER );
2 /
Type created.
SQL> CREATE TYPE ttype AS TABLE OF rtype;
2 /
Type created.
Now base your procedure variables on these types and it will work.
SQL> CREATE TYPE rtype AS OBJECT ( the_key NUMBER );
2 /
Type created.
SQL> CREATE TYPE ttype AS TABLE OF rtype;
2 /
Type created.
Now base your procedure variables on these types and it will work.
ASKER
rajeXsh--that didn't work, same errors. Although this "extends" without a compile time error.
andrewst--I have a feeling that you are correct, that I cannot TABLE(CAST()) a PL/SQL table. The solution you offered, however, doesn't solve the problem as I can't use "%TYPE" in an OBJECT definition.
andrewst--I have a feeling that you are correct, that I cannot TABLE(CAST()) a PL/SQL table. The solution you offered, however, doesn't solve the problem as I can't use "%TYPE" in an OBJECT definition.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Andrew. This was my suspicion. I rewrote it this morning to pass back the PL/SQL table as the "%TYPE" is very important.
I also tried CAST()ing the PL/SQL table to a VARRAY, but got an invalid type when I tried that. <sigh>
I also tried CAST()ing the PL/SQL table to a VARRAY, but got an invalid type when I tried that. <sigh>
TYPE ttype is TABLE OF rtype;
-- rajeXsh