Link to home
Start Free TrialLog in
Avatar of bfish
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_table.key_column%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.
Avatar of rajeXsh
rajeXsh

Can you redefine as ttype and remove the index by binary integer

TYPE ttype is TABLE OF rtype;

-- rajeXsh


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.

Avatar of bfish

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.
ASKER CERTIFIED SOLUTION
Avatar of andrewst
andrewst

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfish

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>