Returning ResultSet from SQLJ stored procedure

From: <nsmyth7939_at_my-deja.com>
Date: Tue, 21 Sep 1999 12:51:50 GMT
Message-ID: <7s7v12$8ms$1@nnrp1.deja.com>

Hi!,

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/FUNCTION statement? I have successfully created this procedure within a package, but then had run-time errors regarding the second parameter when trying to call it.

These procedures work under DB2, so it must be something to do with the CREATE
PROCEDURE/FUNCTION. 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;

Any help would be greatly appreciated.

Thanks,

Neil.

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