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.