Re: Returning a cursor to a PL/SQL table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 25 Feb 1999 20:39:12 GMT
Message-ID: <36d7b3ff.2805413@192.86.155.100>

A copy of this was sent to hvdbulk_at_my-dejanews.com (if that email address didn't require changing) On Thu, 25 Feb 1999 19:01:40 GMT, you wrote:

>Hi,
>
>I have succesfully returned a cursor based on a query from a stored
>procedure. I'm returning it to a RougeWave C++ client that can use the cursor
>to do the fetches. However in my next challange I'm trying to return a cursor
>to a PL/SQL table. Does anybody know how to do this?
>
>Your help is appreciated.
>

database versions (why doesn't anyone ever put that in their posts?)???

In oracle8 you can:

SQL> set serveroutput on
SQL> 
SQL> REM instead of putting a type in a spec, do this:
SQL> 
SQL> create or replace type myTableType as table of number;

2 /

Type created.

SQL> 
SQL> 
SQL> create or replace function getMyTableType return myTableType
  2  as
  3          l_x     myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );
  4  begin
  5          return l_x;

6 end;
7 /

Function created.

SQL> 
SQL> 
SQL> REM here we go... selecting from it:
SQL> 
SQL> select a.column_value  val

2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a 3 /

VAL

9 rows selected.

SQL> 
SQL> 
SQL> REM just another example of the same....
SQL> 
SQL> create or replace package myTableTypePkg
  2  as
  3          function get( p_x in number ) return myTableType;
  4          pragma restrict_references( get, WNDS, WNPS );
  5  
  5          pragma restrict_references( myTableTypePkg, wnds, rnds, wnps, rnps

);
6 end;
7 /

Package created.

SQL> 
SQL> 
SQL> create or replace package body myTableTypePkg

  2 as
  3
  3
  3 function get( p_x in number ) return myTableType   4 is
  5 l_x myTableType;
  6 begin
  7
  7 select cast( multiset( select user_id from all_users where rownum < p_x )

  8                                   AS myTableType )
  9            into l_x
 10            from dual;
 11  
 11          return l_x;

12 end;
13
13 end;
14 /

Package body created.

SQL>
SQL> select a.column_value val
2 from THE ( select cast( myTableTypePkg.get(20) as mytableType ) from dual ) a
3 /

VAL

19 rows selected.

In Oracle7, there is a sort of way to do it. See http://www.dejanews.com/[ST_rn=ps]/getdoc.xp?AN=365353297.1 for that one...

>Thanks,
>
>Henri
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

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 Received on Thu Feb 25 1999 - 14:39:12 CST