Returning Multiple Result Sets for Java Stored Procedures

Returning Multiple Result Sets for Java Stored Procedures

Post by Blaine A Bel » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Returning Multiple Result Sets for Java Stored Procedures

Post by Thomas Kyt » Sun, 31 Dec 1899 09:00:00



(if that email address didn't require changing)


>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";

    }

Quote:}

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

      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();
  }

Quote:}

--
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


Oracle Service Industries     Reston, VA   USA

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

 
 
 

1. return multiple result sets from a stored procedure

Does SQL only pass back once recordset from a stored procedure?

I would like to return multiple recordsets into ADODB and use the
Recordset.NextRecordset method to retrieve them into my application.

The syntax of Recordset.Open() shows multiple SQL statements, but I need the
return of multiple recordsets with the specification of only one SQL
statement(stored procedure).

My original design relied on passing a single parameter of dynamic criteria
and returning 8 recordsets... each one filtered with a temp table created
using the passed criteria.

Thanks.

2. DBA Competency Model

3. How not to return multiple result sets from a stored procedure

4. SQL And Rushmore

5. Stored Procedure/Multiple Return Results/Set Statement

6. Long MDX help

7. Stored Procedures from Java/VB returning Result Sets(via ADO)

8. sideways printing in foxpro2.5

9. multiple result sets returned from a stored prodedure

10. Java Stored Proc and Multiple Result Sets.

11. returning result sets from stored procedures

12. Returning result sets in oracle stored procedure (OLEDB)

13. Can't update result set returned from stored procedure using RDO