Re: Returning Multiple Result Sets for Java Stored Procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2000/02/29
Message-ID: <s29obso3m72ebbh2uau3rtpark7f4ljgni@4ax.com>#1/1

A copy of this was sent to Blaine A Bell <bab22_at_diamond.cs.columbia.edu> (if that email address didn't require changing) On Tue, 29 Feb 2000 11:22:31 -0500, you wrote:

>Do you have any (or know of any) examples for returning Multiple Result
>Sets from Java Stored Procedures? I have implemented your example on your
>web page, but we are not sure about how CURSORS map to java? In your
>example, you return a cursor from the stored procedure, what is the
>equivalent java class?
>
>Thanks in advance,
>
>Blaine
>

You cannot return a result set directly from a JAVA stored procedure to a client. There is a very easy way to do this, but you won't be opening the result set at the java layer, rather you'll have PLSQL open the result set (cursor variable) for you..

The approach is to let the java stored procedure return a string -- this string is the query you would have passed to:

    statement.execute( "select * from emp" );     ResultSet resultSet = statement.getResultSet();

statement.execute (it is query in the above example). You'll just forget the subsequent linesof code as well, we won't need it. Then, since ALL java stored procedures can be wrapped with a SQL binding layer -- we can open the result
set at THAT layer. For example, say you have a class loaded:

class Foo {

    public static void get_result_set(String[] the_result_set) {

        the_result_set[0] = "select * from emp";

    }
}

So, instead of have a result set as an out parameter, this has a String as an out parameter. We set the string to our query (instead of doing the above 4 lines of jdbc calls).

Then, at the sql binding layer we code:

create or replace package Foo
as

    type rc is ref cursor;
    procedure foo( p_refcur in out rc ); end;
/

create or replace package body Foo
as

    procedure get_result_set( p_result_set out varchar2 )     as language java
    name 'Foo.get_result_set( java.lang.String[] )';

    procedure foo( p_refcur in out rc )
    as

        l_query varchar2(1024);
    begin

        get_result_set( l_query );
        open p_refCur for l_query;

    end;
end;
/

So, the stored procedure Foo.foo calls the java, gets the query and makes a ref cursor out of it. We can then code at the java client side something like the following:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class curvar
{
  public static void main (String args []) throws SQLException   {

      String query  = "begin Foo.foo( :1 ); end;";
      int         rsetCount;

    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

      System.out.println( "connecting..." );     Connection conn =

      DriverManager.getConnection
        ("jdbc:oracle:thin:@aria:1521:ora8i", "tkyte", "tkyte");

      System.out.println( "connected..." );

      Statement stmt = conn.createStatement ();

      CallableStatement cstmt = conn.prepareCall(query);
      cstmt.registerOutParameter(1,OracleTypes.CURSOR);
      cstmt.execute();

      ResultSet rset = (ResultSet)cstmt.getObject(1);

      for (rsetCount = 0; rset.next (); rsetCount++ )
         System.out.println( rset.getString( "ename" ) );

      rset.close();
      cstmt.close();

  }
}

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

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

Received on Tue Feb 29 2000 - 00:00:00 CST