?
Solved

PL/SQL populating a cursor from a pl/sql table

Posted on 2003-03-20
5
Medium Priority
?
2,147 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:bfish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:rajeXsh
ID: 8177825
Can you redefine as ttype and remove the index by binary integer

TYPE ttype is TABLE OF rtype;

-- rajeXsh


0
 
LVL 15

Expert Comment

by:andrewst
ID: 8179923
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.

0
 

Author Comment

by:bfish
ID: 8180940
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.
0
 
LVL 15

Accepted Solution

by:
andrewst earned 270 total points
ID: 8181042
> 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.

No, you will have to use Oracle datatypes like NUMBER, VARCHAR2(10), DATE, as I did in my example.  %TYPE is a PL/SQL thing, and you can't TABLE(CAST()) a PL/SQL collection, so that's the best you can do.  There is no solution that gives you both %TYPE and TABLE(CAST()) !
0
 

Author Comment

by:bfish
ID: 8181072
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>
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question