ResultSet from stored procedure in Oracle

We are in a desparate need to write stored procedure in Oracle from which ResultSet can be retrieved.

In SQL Server 6.5 we can write a simple SQL statement like "Select * from temp" within a stored procedure. It has an associated implicit cursor which can be retrieved and stored in a RecordSet object in aVB client using ODBC.

But we are facing problems when we try to use the similar technique in Oracle. First, it is not possible to write a statement like "Select * from temp" within an Oracle procedure; we have to use explicit cursor for that. Even though we declare an explicit cursor like ... cursor c1 is select * from temp; begin open c1; ...

we cannot find a way so that the content of c1 can be stored in a ResultSet through JDBC in java class. The client code looks like following:

• DriverManager.registerdriver(new sun.jdbc.odbc.JdbcOdbcdriver()); String url = "jdbc:odbc:myhost"; Connection con = DriverManager.getConnection(url,"scott","tiger"); CallableStatement cstmt = con.prepareCall("begin someproc; end;"); ResultSet rs = cstmt.executeQuery(); while(rs.next()){ String s = rs.getString(1); ... }

  ---

  
  ---
  

If we use cursor in the Oracle procedure someproc as written above,we are getting an java.sql.SQLException stating "No ResultSet was produced"  at the assignment

                ResultSet rs = cstmt.executeQuery();

It would be of great help to us if somebody comes up with a solution and provide the way to write the body of the procedure someproc so that cursor (implicit/explicit) can be stored in a ResultSet as in the java code written above.

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Mar 15 1999 - 04:23:51 CST