Re: Convert table to cursor in stored proc

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/04
Message-ID: <8ero2a$l9p$1@nnrp1.deja.com>#1/1

In article <8eq3o7$sna$1_at_nnrp1.deja.com>,   tomjt1_at_my-deja.com wrote:
> I've got a fairly complicated stored procedure that builds a table of
> records. I want to return this table to my Java program. Is there a
> way to do this? Can I convert the table to a cursor somehow?
>
> TIA,
>
> Tom
>
> For clarification:
> My procedure creates a type based on my 'task' table..
> TYPE T_TABLE_ARRAY IS TABLE OF task%ROWTYPE INDEX BY BINARY_INTEGER;
> My result array is
> v_result T_TABLE_ARRAY;
>
> At the end of my procedure, I've filled v_result with a lot of
 records.
> Ideally, I'd like to cast this somehow into a cursor that can be
 parsed
> by my Java app. It's seems unlikely that the JDBC would translate my
> PL/SQL table into a valid Java object.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If instead of defining the RECORD and TABLE OF RECORDS in plsql, you did it in SQL using a "scalar" record type and a "table" table like like this:

ops$tkyte_at_8i> create or replace type myScalarType as object

  2  (       x int,
  3          y date,
  4          z varchar2(25)

  5 )
  6 /

Type created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace type myTableType as table of

myScalarType;
  2 /

Type created.

You can then easily return a variable of myTableType from PLSQL to any language as a result set. It might look like this:

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package my_pkg
  2  as
  3          type refCur is ref cursor;
  4
  5          procedure get_data( p_inputs in varchar2, p_cursor in out

refCur );
  6 end;
  7 /

Package created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body my_pkg   2 as
  3
  4
  5 procedure get_data( p_inputs in varchar2, p_cursor in out refcur )   6 is
  7 l_data myTableType := myTableType();   8 begin
  9
 10

 11          for i in 1 .. 5 loop
 12                  l_data.extend;
 13                  l_data(l_data.count) := myScalarType( i,
sysdate+i, 'The input ' || p_inputs );
 14          end loop;
 15
 16          open p_cursor for select * from the ( select cast( l_data

as myTableType ) from dual );
 17 end;
 18
 19 end;
 20 /

Package body created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> set autoprint on
ops$tkyte_at_8i> variable x refcursor
ops$tkyte_at_8i>
ops$tkyte_at_8i> exec my_pkg.get_data( 'hello world', :x )

PL/SQL procedure successfully completed.

         X Y Z

---------- --------- -------------------------
         1 05-MAY-00 The input hello world
         2 06-MAY-00 The input hello world
         3 07-MAY-00 The input hello world
         4 08-MAY-00 The input hello world
         5 09-MAY-00 The input hello world

If you are not sure of how to deal with ref cursors (result sets) in JDBC, see http://osi.oracle.com/~tkyte/ResultSets/index.html

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu May 04 2000 - 00:00:00 CDT