Re: how to make a cursor variable from a index-by plsql table?

There is a way, but it isn't very efficient.

1. Create a table dummy_list(n1 number); populate it with numbers from 1 to N
2. Create a function for each 'PL/SQL column' you want to return of the form: function_colX(n in integer) return 'type of pl/sql column X'; begin return pl_sql_table_X(n); end;
3. In your PL/SQL code, generate the pl/sql table and count the entries.
4. Your cursor is then: select function_colA(n1), function_colB(n1) function_colX(n1) from dummy_list where n1 between 1 and {number of rows in plsql table}

Hope this is enough to get you going.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

hchen_at_time-0.com wrote in message <7i1j3n$9vb$1_at_nnrp1.deja.com>...
>Hi,
>This is a re-post of my question yesterday - this problem has been
>driven me crazy. If the results stored in a index-by plsql table can
>not be returned by a cursor, I have to rewrite the whole package!
>Here is the question:
>I have an index-by plsql table of records(We are using Oracle 7.3). It
>is well structured, and can be passed through several functions within
>the package. The last step requires me to make a cursor variable out
>from this table, and return this cursor to my java code, in which this
>plsql package is called.
>I have tried every way I can think of. I did read the plsql manual of
>8i, it said nested plsql tables can be SQL manipulated, but this feature
>is not available in my 7.3.
>Is there any way to work around, or am I hitting the limit of 7.3?
>
>: open return_this_cursor_to_java
>: for select bom_id, bom_type_code
>: from bom
>: where last_userid IN (select userid from
>my_index_by_plsql_table);
>^^^^^^^^^^^^^^^^^^^^^^^
>It is a compile error. This does not work also:
>: where last_userid IN (my_index_by_plsql_table);
>
Received on Thu May 20 1999 - 13:58:21 CDT