How do I use an IN OUT CLOB parameter defined in a Stored Proc in Oracle JDBC?

Question

I have the following stored procedure defined:

CREATE OR REPLACE PROCEDURE NOTIFY_INSERT (
  FLAG IN VARCHAR2,
  MESSAGE IN OUT CLOB,
  SEQ_NO OUT NUMBER
) 
AS
BEGIN
...
END;

Now I'm trying to call this stored proc in JDBC. However, I get an exception saying "java.sql.SQLException: Parameter Type Conflict". This is raised from the line

call.execute();

I'm guessing it has something to do with the second parameter, which is a CLOB.

Does anyone have an idea what I'm doing wrong?

Thanks for your help.

Connection connection = dataSource.getConnection();
CallableStatement call = null;
try {
    call = connection.prepareCall("{ call NOTIFY_INSERT (?,?,?) }");
    call.setString(1, flag);
    call.registerOutParameter(2, Types.CLOB);
    call.setString(2, message);
    call.registerOutParameter(3, Types.NUMERIC);
    call.execute();
    sequenceNo = call.getLong(3);
    message = call.getString(2);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    if (call != null) {
        try { call.close(); } catch (SQLException sqle) {}
    }
}

JDBC Exception:

java.sql.SQLException: Parameter Type Conflict
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2480)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4356)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4595)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:10100)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:5693)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

Answer 1

I don't think that you can pass a String directly to a parameter of type CLOB. When attempting to bind a CLOB parameter, you can do the following:

If you already have a Clob:

call.setClob(1, clob);

If you want to convert a String into a Clob:

call.setCharacterStream(1, new StringReader(string), string.length());

If you want to set a null CLOB:

call.setNull(1, Types.CLOB);

You can also see this solution.