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

From: <hchen_at_time-0.com>
Date: Thu, 20 May 1999 21:45:56 GMT
Message-ID: <7i1vqj$jtd$1@nnrp1.deja.com>

Thank you, Jonathan!
This is really a smart idea. It got me out of a deadlock. I didn't do it because I though plsql function should be "stateless", therefore it can not take non-static values. In other words, this function can only be called within this package, not just from sqlplus. I didn't create a dummy number table. I just get the sequence number by ROWNUM from a select to a big table.

Thanks again.
Hang Chen

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

Create a table dummy_list(n1 number); populate it with numbers from 1 to N
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;
In your PL/SQL code, generate the pl/sql table and count the entries.
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 In article <7i1j3n$9vb$1_at_nnrp1.deja.com>, hchen_at_time-0.com wrote:
> 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);
>
> Any hint would be approciated!
> Thanks a LOT.
> Hang Chen
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Thu May 20 1999 - 16:45:56 CDT