In article <7clp22$ud4$1_at_imsp009a.netvigator.com>,
"lamtse" <lamtse_at_ims06.netvigator.com> wrote:
> 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
>
>
Oracle cursor *** IS *** the result set that you are talking about. Once
you receive open cursor, all you need to do (or all that your tool needs
to do) is to fetch from it, via PRO*xxx, OCI or PL/SQL.
Mladen Gogala
-----------== 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:38:49 CST
