0

I have a procedure I'm calling using a JDBC callable statement. The first parameter, v_type, has a default value I want to use. The proc/code is simplified.

PROCEDURE select_type(v_type   IN     VARCHAR2 DEFAULT 'All',
                      cv_1     OUT SYS_REFCURSOR)
AS
BEGIN
   IF (v_type = 'All')
   THEN
      OPEN cv_1 FOR
         SELECT DISTINCT TYPE,
           FROM type_source;
   ELSE
      IF (v_type = 'NotAll')
      THEN
         OPEN cv_1 FOR
            SELECT DISTINCT type, desc,
              FROM OTHER_TYPE_SOURCE;
   END IF;
END;

This is the code I attempted, assuming null would trigger the default.

callablestatement = connection.prepareCall("{ CALL select_type(?,?) } ") ;
callablestatement.setString(1, null);
callablestatement.registerOutParameter(2, OracleTypes.CURSOR);
callablestatement.execute();
resultset = (ResultSet)callablestatement.getObject(2); 

The last line .getObject() throws a cursor closed exception, presumably, due to the null parameter not matching the IF structure in the procedure and the cursor never being opened.

Chait
  • 1,052
  • 2
  • 18
  • 30
Patrick J Abare II
  • 1,129
  • 1
  • 10
  • 31

0 Answers0