I'm in the same boat. From tidbits I keep hearing, it sounds like it's
possible to use a REF CURSOR to return the result set, but so far I'm
having a dickens of a time finding any examples. According to Oracle's
Support forum (for ODBC), You cannot return a REF Cursor to DAO, but you
can to RDO and ADO.
> -----Original Message-----
> From: lamtse [SMTP:lamtse_at_ims06.netvigator.com]
> Posted At: Tuesday, March 16, 1999 9:15 AM
> Posted To: comp.databases.oracle.server
> Conversation: ResultSet from stored procedure in Oracle
> Subject: 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 - 09:35:50 CST
