Re: Help - getting a RowSet from Oracle Function

From: The Nomad <nobody_at_nowhere.com>
Date: Thu, 05 Apr 2001 23:01:33 GMT
Message-ID: <hP6z6.38365$Lz6.6523439@typhoon.tampabay.rr.com>

> Your assertion a stored procedure can't return a resultset is simply
 untrue.
> Period.
> If I read exactly the same documentation I will be using
> procedure(c1 in out REF CURSOR)
> and that's all there is to it.
> Secondly, in your function you use a strongly typed cursor, which you
 assign
> to a weakly type cursor in your anonymous block. I expect that won't work
> exactly because of this reason. Try changing the cursor definition in your
> package to a REF CURSOR without type.

I think my assertion is that it can't return a rowset in the same manner that other products return a rowset. I can't have my .java file using Oracle libraries because my product may be installed to use DB2 or MSSQL7/MSSQL2000. For the other databases, I can use the code fragment I showed above, and they work. That is:

  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();
  }

Oracle doesn't provide rowsets in this manner (at least I can't discover any way for it to - that's why I'm asking for help). What Oracle 8i seems to need is something like this:

...
import com.oracle....

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

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

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

My classes need to be generic, not specific to any one driver. Make sense? FYI - my code is using a strongly-typed cursor. It was shown as:

CREATE OR REPLACE PACKAGE TYPES
AS

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

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;
/

Note that c1 is TYPES.CURSORTYPE which is strongly typed as a cursor returning SSSLOGSOURCES%ROWTYPE. Received on Thu Apr 05 2001 - 18:01:33 CDT