Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 30 result sets via JDBC
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).
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 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