Re: ResultSet from Java Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 10 Nov 1999 15:34:11 -0500
Message-ID: <K9MpOEgYsnVBbw=IF96GUENlvsY4@4ax.com>

A copy of this was sent to sherman_mohler_at_my-deja.com (if that email address didn't require changing) On Wed, 10 Nov 1999 19:22:07 GMT, you wrote:

>I am brushing up on what Oracle has done with Java, and am pretty
>impressed overall. One thing I cannot find however, is how to return a
>RecordSet to a Java Servlet from a Java Stored Procedure (through the
>Packaged call of course). I have returned ResultSets to a Java Servlet
>from an Oracle ref cursor before, but don't see any appropriate mapping
>from a Cursor to any of the oracle.sql.* Java datatypes.
>
>If you can direct me to any good examples of getting back a ResultSet
>from a Java Stored Procedure, I would really appreciate it!
>
>Regards,
>
>-- Sherman Mohler
> sherman_mohler_at_yahoo.com
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

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.

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

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

prepareCall (it is query in the above example). You'll just forget the subsequent 3 lines of code as well, we won't need them. Then, since ALL java stored procedures are 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 Wed Nov 10 1999 - 14:34:11 CST