Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-20
5
Medium Priority
?
2,185 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
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

580 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