ResultSet from stored procedure in Oracle

[kp...@techna.co.in]

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

[Sam Jordan]

kp...@techna.co.in wrote:

> 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:

JDBC itself AFAIK doesn't support returning ResultSets from stored
procedures. I also needed to return ResultSets and I found a solution,
but which is proprietary and most likely only works for Oracle DB's.

The Oracle thin driver classes, which I found inside the Oracle/Linux
distribution, define additional types, one of them is called
OracleTypes.CURSOR. This looks like:

CallableStatement cs = connection.prepareCall("{? = call
PACKAGE.FUNC()}");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet r1 = (ResultSet)cs.getObject(1);

You have to check the documentation of your Oracle JDBC drivers, if
this additional data type is supported.

bye
--
Sam Jordan

[Shawn Ramsey]

There is a way to do it. Microsoft's Oracle ODBC driver supports it in a
rather strange manner.

Check out the Microsoft Developers Network article Q176936, Q175018, Q176086
from the CD, I am not sure that they are included in the internet version of
the MSDN.

kp...@techna.co.in wrote in message <7cin3i$et1$1...@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:

>---------------------------------------------------------------------------

[lamtse]

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

kp...@techna.co.in 撰寫於文章 <7cin3i$et1$1...@nnrp1.dejanews.com>...

[Mladen Gogala]

In article <7clp22$ud4$1...@imsp009a.netvigator.com>,

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

[Rodgers, Tony (CEI-Atlanta)]

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.