Re: 30 result sets via JDBC

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 12 Feb 2002 11:08:37 -0800
Message-ID: <a4bp7l021cm@drn.newsguy.com>

In article <3C695646.1D00D400_at_icn.siemens.de>, Andreas says...
>
>First of all, sorry for this simple question but I spent
>many hours and I failed to find more detailed information.
>
>I need to send multiple result sets (I mean not a single one)
>to a java application via JDBC. I know there is an example on
>asktom-site but it's only demonstrating how to send a single
>result set to the application but I need up to 30.
>
>I found an example on http://www.javaworld.com/javaworld/
>jw-02-2000/jw-02-ssj-jdbc2.html which is demonstrating the
>needed feature (TransactSQL-oriented database).
>

You do this with formal parameters to a procedure. Code PLSQL like this:

create or replace package types
as

    type cursorType is ref cursor;
end;
/

create or replace

procedure get_many_results( p_cur1 in out types.cursortype,
                            p_cur2 in out types.cursortype,
                            p_cur3 in out types.cursortype )

as
begin

    open p_cur1 for select ename, empno from emp order by ename;     open p_cur2 for select dname, deptno from dept order by dname; open p_cur3 for select dname, ename from emp, dept where emp.deptno=dept.deptno; end;
/

and java like this:

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

class curvar
{

public static void main (String args []) throws SQLException, ClassNotFoundException {

   String query =

          "begin get_many_results( ?,?,? ); end;";

   DriverManager.registerDriver

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

   Connection conn=

       DriverManager.getConnection
       ("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
         "scott", "tiger");

   CallableStatement cstmt=null;

   cstmt = conn.prepareCall(query);

   cstmt.registerOutParameter(1,OracleTypes.CURSOR);
   cstmt.registerOutParameter(2,OracleTypes.CURSOR);
   cstmt.registerOutParameter(3,OracleTypes.CURSOR);

   ResultSet rset=null;

   cstmt.execute();
   for( int result_set_no = 1; result_set_no <= 3; result_set_no++ )    {

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

      for(int i = 0;  rset.next(); i++ )
        System.out.println( "Resultset " + result_set_no + ": " +
                             rset.getString(1) + "," + rset.getString(2) );

      rset.close();

   }
   cstmt.close();
}
}

>
>---------------------------------------------
>CREATE PROCEDURE reverseSelectionProcedure AS
>BEGIN
> -- First select statement,
> -- producing a 1-column JDBC ResultSet
> select fName from testlogin.Test;
>
> -- Second select statement,
> -- producing a 2-column JDBC ResultSet
> select * from testlogin.Test;
>END
>---------------------------------------------
>
>
>PL/SQL (I use SQLPlus 8.1.7.2.0) sends me the error message:
>PLS-00428: an INTO clause is expected in this SELECT statement
>
>I guess this is OK because the procedure is not made for PL/SQL.
>
>My question is:
>Could you please give me an example how to "translate"
>this little procedure to PL/SQL? It would help me
>really a lot! I tried by my own but... please have a look:
>
>
>---------------------------------------------
>create or replace package types
>as
> type cursorType is ref cursor;
>end;
>/
>show errors;
>
>create or replace function sp_ListMyTables return types.cursortype
>as
>l_cursor types.cursorType;
>begin
> open l_cursor for select * from my_table1;
> open l_cursor for select * from my_table2;
> open l_cursor for select * from my_table3;
> -- ...
>return l_cursor;
>end;
>/
>show errors;
>---------------------------------------------
>
>
>This is not working because when I reopen a cursor variable
>for a different query, the previous query is lost. How
>can I solve this?
>
>In the java application I would like to use something
>like this:
>
>...
>CallableStatement c = conn.con.prepareCall("begin :1 := sp_ListMyTables;
>end;");
>c.registerOutParameter(1,OracleTypes.CURSOR);
>boolean bResultSet = c.execute();
>
>while (true) {
> if (bResultSet) {
> ResultSet rs = c.getResultSet();
> //???ResultSet rs = (ResultSet)c.getObject(1);
> while (rs.next()) {
> ..
> }
> }
> else {
> int nUpdateCount = c.getUpdateCount();
> if (nUpdateCount == -1)
> break; //No more update counts or result sets - time to stop..
> }
> bResultSet = c.getMoreResults();
>}
>
>Thank you very much!
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Feb 12 2002 - 13:08:37 CST