Returning Multiple Result Sets for Java Stored Procedures 
Author Message
 Returning Multiple Result Sets for Java Stored Procedures
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



Wed, 18 Jun 1902 08:00:00 GMT
 Returning Multiple Result Sets for Java Stored Procedures


(if that email address didn't require changing)

Quote:

>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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. return multiple result sets from a stored procedure

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

3. Stored Procedure/Multiple Return Results/Set Statement

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

5. multiple result sets returned from a stored prodedure

6. Java Stored Proc and Multiple Result Sets.

7. returning result sets from stored procedures

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

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

10. Returning result set from function to stored procedure is very slooooooooow

11. best way to return a paged result set from a stored procedure

12. Returning a result set from a Stored Procedure


 
Powered by phpBB® Forum Software