Re: convert collection to cursor

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Feb 1999 14:17:20 GMT
Message-ID: <36bf4e75.3668094@192.86.155.100>

A copy of this was sent to "Ed Zappulla" <zappullae_at_rcn.com> (if that email address didn't require changing) On Thu, 4 Feb 1999 19:29:36 -0500, you wrote:

>Is it possible to convert a collection to a cursor? I have a collection of
>records and wish to return them to a calling entity (JAVA app via JDBC). I
>can send back a cursor fine. How do I get the data from the collection into
>the cursor?
>
>...ed
>
>

if you create the record as an object, you can. Consider:

SQL> create or replace type myScalartype as object   2 ( ename varchar2(25), empno int );   3 /
Type created.

so thats the record....

SQL> create or replace type myTableType as table of myScalarType   2 /
Type created.

now we have a table type of those records...

SQL> create or replace package my_pkg
  2 as

  3          function my_function_as_a_table return myTableType;
  4          pragma restrict_references(my_function_as_a_table,wnds,rnds,wnps);
  5  
  5          procedure init_table;
  6  
  6          pragma restrict_references(my_pkg,wnds,rnds,wnps,rnps);

  7 end;
  8 /
Package created.

Here is a package to has 2 entries. One entry will return that table type and the other entry 'fills it up' with data.

SQL>
SQL> create or replace package body my_pkg   2 as
  3
  3 tmp_table myTableType := myTableType();   4
  4 function my_function_as_a_table return myTableType   5 is
  6 begin
  7 return tmp_table;
  8 end;
  9
  9 procedure init_table
 10 is
 11 begin

 12          tmp_table.extend;
 13          tmp_table(1) := myScalarType( 'Hello', 1 );
 14          tmp_table.extend;
 15          tmp_table(2) := myScalarType( 'World', 2 );

 16 end;
 17
 17 end my_pkg;
 18 /
Package body created.

sample implementation of the package....

SQL> exec my_pkg.init_table
PL/SQL procedure successfully completed.

fill it up

SQL> select *
  2 from the(select cast(my_pkg.my_function_as_a_table() as myTableType)

  3                           from dual)

  4 /

ENAME                          EMPNO
------------------------- ----------
Hello                              1
World                              2

and thats the query that turns the table into a SELECT.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Feb 06 1999 - 08:17:20 CST