Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> 30 result sets via JDBC

30 result sets via JDBC

From: Andreas Baudisch <Andreas.Baudisch_at_icn.siemens.de>
Date: Tue, 12 Feb 2002 18:52:06 +0100
Message-ID: <3C695646.1D00D400@icn.siemens.de>


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



CREATE PROCEDURE reverseSelectionProcedure AS BEGIN

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;

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! Received on Tue Feb 12 2002 - 11:52:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US