Re: ResultSet from stored procedure in Oracle

I have the same problem when using PowerBuilder over Oracle. With Sybase SQL Server, I can have a simple Select statement in a stored procedure which returns a result set to be used as the data source of a data window in PowerBuilder. In oracle, I find no way to do so. I don't quite understand how a cursor will help in giving a result set from a stored procedure. Anyone has any idea?

D. Lam

kpaul_at_techna.co.in ¼¶¼g©ó¤å³¹ <7cin3i$et1$1_at_nnrp1.dejanews.com>...
>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 Tue Mar 16 1999 - 08:15:04 CST