REF CURSOR data type

REF CURSOR is the Oracle data type for a cursor variable. Because JDBC does not support a cursor variable data type, the Oracle driver returns REF CURSOR output parameters and return values to the application as result sets. The Oracle driver converts the REF CURSOR data to a result set that can be returned using getResultSet() or getMoreResults(). Because REF CURSOR data is returned as result sets and not as output parameters, REF CURSOR output parameters are not included in results from DatabaseMetaData.getProcedureColumns() calls.

In your application, omit any parameter markers for the REF CURSOR as shown in the following examples and do not declare an output parameter for the REF CURSOR. These examples reference the following stored procedure definition:

CREATE PACKAGE foo_pkg AS
   TYPE EmpCurTyp IS REF CURSOR RETURN fooTbl%ROWTYPE;
   PROCEDURE selectEmployeeManager(empId IN INT, empCursor OUT EmpCurTyp,
   mgrCursor out EmpCurTyp);
   FUNCTION selectEmployee2 (empId IN INT) return EmpCurTyp;
END foo_pkg;

// Call a function that accepts an input parameter and returns a
// REF CURSOR as the return value.
// Omit the placeholder for the refcursor return value parameter.
// The REF CURSOR is returned as a result set.
sql = "{call foo_pkg.selectEmployee2(?)}";
callStmt = con.prepareCall(sql);
callStmt.setInt(1, 2);
moreResults = callStmt.execute();
while (true) {
   if (moreResults) {

      // Get the result set that represents the REF CURSOR
      resultSet = callStmt.getResultSet();
      displayResults(resultSet);

      resultSet.close();
      resultSet = null;

      System.out.println();
   }
   else {

         updateCnt = callStmt.getUpdateCount();
         if (updateCnt == -1) {
            break;
         }
         System.out.println("Update Count: " + updateCnt);
    }
    moreResults = callStmt.getMoreResults();
}

// Call the stored procedure that accepts an input parameter and
// returns two REF CURSORs.
// Omit the placeholder for REF CURSOR parameters.
// The REF CURSORs are returned as result sets.
sql = "{call foo_pkg.selectEmployeeManager(?)}";
callStmt = con.prepareCall(sql);
callStmt.setInt(1, 2);
moreResults = callStmt.execute();
while (true) {
   if (moreResults) {

      // Get the result set that represents the REF CURSOR
      resultSet = callStmt.getResultSet();
      displayResults(resultSet);
      resultSet.close();
   }
   else {

         updateCnt = callStmt.getUpdateCount();
         if (updateCnt == -1) {
            break;
         }
      }

      moreResults = callStmt.getMoreResults();
}