pl/sql ref cursor question

Hi,

I'm having trouble getting an oracle plsql reference cursor variable to return data stored in a user defined table.

I have code which defines a reference cursor variable as such:

TYPE gain_record is RECORD (xvalue DATE, yvalue NUMBER); TYPE gain_table is table of gain_record
  INDEX BY BINARY_INTEGER;
TYPE out_cursor IS REF CURSOR RETURN gain_record;

In my stored procedure, I declare variables as such:

gaintable gain_table;

gain        NUMBER;
num         NUMBER;

and do the following:

num := 1;
FOR ref IN ( ...some sql query... )
LOOP
  BEGIN
    perform_calc(gain)
    insert into gaintable (xvalue, yvalue) values (num, gain);     num := num + 1;
  END;
END LOOP; OPEN outData FOR SELECT * from gaintable ORDER BY xvalue;

When I try to start the package, I get the following error:

665/12
PLS-00950: In this version, PL/SQL tables can not be used in this SQL statement.  

What is the problem with what I am doing? I am using Weblogic's JDBC package which does not support stored procedures which return pl/sql tables; they deal only with reference cursor variables.

I can get the ref cursor to select out of a database table (ie -- if gaintable was a table defined in oracle) just fine but can't make it get the data from a table which I define inside my pl/sql package.

Are there any efficient workarounds to this sitution if there is not a direct way to get this configuration to work?

Any help or ideas are greatly appreciated. Please email to eshneken_at_cdidc.org.

thanks,
Ed.

-- 


---------------------------------------------------------------------

Edward A. Shnekendorf           |  U. of Maryland _at_ College Park Alum
Software Engineer               |
Center for Monitoring Research  |   http://www.cdidc.org/~eshneken 


---------------------------------------------------------------------