Help - getting a RowSet from Oracle Function

From: The Nomad <nobody_at_nowhere.com>
Date: Thu, 05 Apr 2001 20:20:17 GMT
Message-ID: <5s4z6.40178$z4.6873526@typhoon.tampabay.rr.com>

Hey all,

I've been tasked with writing Java that accesses Oracle 8i, MSSQL and DB2 seamlessly. To hide much of the RDBMS logic, I've written many stored procedures whose task it is to retrieve rows and return the rows to the Java methods. Here is what works for DB2/MSSQL:

  try {
    CallableStatement stmt = conn.prepareCall("{call SSSLOG_GETSOURCES}");     try {

      ResultSet rs = stmt.executeQuery();
      try {
        while (rs.next()) {
          System.out.println(rs.getInt(1) + ", " + rs.getString(2));
        }
      } finally {
        rs.close();
      }

    } finally {
   stmt.close();
    }
   } finally {
 conn.close();
  }

SSSLOG_SOURCES is defined in MSSQL as follows:

CREATE PROC SSSLOG_GETSOURCES
AS
 SELECT *
 FROM SSSLOGSOURCES
 ORDER BY LOGSOURCEDESC
GO

In Oracle, I understand (by trying to read the documentation and looking at various posts), that a Stored Procedure can't return a rowset. But, a function can. And, the way to get it to return the data is somehow through a cursor - like:

CREATE OR REPLACE PACKAGE TYPES
AS

    TYPE CURSORTYPE IS REF CURSOR RETURN SSSLOGSOURCES%ROWTYPE; END;
/

/*

---

• Get Log Sources

  ---

  */ CREATE OR REPLACE FUNCTION SSSLOG_GETSOURCES RETURN TYPES.CURSORTYPE AS c1 TYPES.CURSORTYPE; BEGIN OPEN c1 FOR SELECT * FROM SSSLOGSOURCES ORDER BY LOGSOURCEDESC; RETURN c1; END;
  /
  

However, when I run through the Java code above, it claims that there is no such procedure. If I change the code to call another stored procedure I've written that doesn't return a dataset, it is found. What's going on? Also, when I simply invoke it from SQL Plus, SQL Plus complains it's not there. But if I use this syntax:

variable b refcursor
exec :b := SSSLOG_GETSOURCES
print :b

I can see that it works and it's there.

Why can't the Java App above find it? It can find all the other SPs I've written (although the others it finds are CREATE PROCEDURE... and not CREATE FUNCTION...) And how do I get a rowset back from a stored function/procedure in Oracle? I saw another sample that used the refCursor by making oracle-specific references (Oracle.Driver.OracleTypes.CURSOR and other stuff) - I need a generic solution that works for all without having a bunch of "SELECT * FROM <aTable> WHERE ..." in the Java code.

TIA Marc Received on Thu Apr 05 2001 - 15:20:17 CDT