Re: Returning ResultSet from SQLJ stored procedures

Hello Neil:

I am a Microsoft SQL Server and Sybase DBA and am undergoing the mind- shattering career transition of becoming an Oracle DBA.

This is apparently a grave weakness of Oracle. You cannot return result sets from stored procedures. You can return a cursor variable --  but only using Oracle Objects for OLE or PRO*C, not ODBC.

Using ODBC (Microsoft or Oracle driver), you can call Oracle stored procedures, but they cannot return result sets.

See Microsoft Knowledge Base article Q174679 and related article for more information.

Microsoft provides code-intensive, restriction ridden workaround that is unsatisfactory, in my opinion.

We would choose to leave our SELECT statements outside of stored procedures. If necessary, we would encapsulate them outside of the Oracle server, within some other means.

May I ask someone more familiar with Oracle to try to formulate a counter-argument?

In article <7s7v14$8mv$1_at_nnrp1.deja.com>,   nsmyth7939_at_my-deja.com wrote:
> I'm in the process of migrating some SQLJ stored
> procedures from DB2 to Oracle 8.1.5. A number of
> these return ResultSets. Enclosed is a sample
> procedure.
>
> -- HostTest.sqlj
> import java.sql.*;
> import oracle.jdbc.driver.*;
> import oracle.sql.*;
> import sqlj.runtime.*;
> import sqlj.runtime.ref.*;
>
> #sql iterator HostTest_Cursor1 ( short );
>
> public class HostTest
> {
> public static void getHostId (String ipAddr,
> ResultSet[] rs)
> throws SQLException, Exception
> {
> HostTest_Cursor1 cursor1 = null;
> #sql cursor1 =
> {
> SELECT HOST_ID FROM HOSTS
> WHERE
> (
> (
> IP_ADDRESS = :ipAddr
> )
> )
> };
> rs[0] = cursor1.getResultSet();
> }
> }
>
> What is the best way of defining this procedure
> using the CREATE PROCEDURE statement? I have
> successfully created this procedure within a
> package, but then had run-time errors regarding
> the second parameter.
>
> Enclosed are the statements that I have used to
> create the procedure:
>
> create or replace package hostpkg as
> type host_curtype is ref cursor;
> procedure get_host_id(ipaddr in varchar2,
> rs out
> host_curtype);
> end hostpkg;
> create or replace package body hostpkg as
> procedure get_host_id(ipAddr in varchar2,
> rs out host_curtype)
> is language java name
> 'HostTest.getHostId(java.lang.String,
> java.sql.ResultSet
> [])';
> end hostpkg;
>
> Thanks,
>
> Neil.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Sep 21 1999 - 11:50:12 CDT