Calling oracle stored procedure with input parameter using SqlInOutParameter gives assert exception with ojdbc8

Question

I have a stored procedure with one in and one out parameter. The input parameter is defined by SqlInOutParameter and output by SqlOutParameter. Everything works fine with ojdbc6 but with ojbc8 it gives assert exception in tests. Since I don't have assert enabled in my application it is working fine there.

Following is the declaration of the stored procedure class

class CustomStoredProcedure extends org.springframework.jdbc.object.StoredProcedure {

    private static final String PROCEDURE_NAME = "MY_PROCEDURE";
    private static final String INPUT_PARAM_1= "INPUT_PARAM_1";
    public static final String OUTPUT_PARAM_1 = "OUTPUT_PARAM_1";

    public CustomStoredProcedure (DataSource dataSource) {
        super(dataSource, PROCEDURE_NAME);
        declareParameter(new SqlInOutParameter(INPUT_PARAM_1, OracleTypes.NUMERIC));
        declareParameter(new SqlOutParameter(OUTPUT_PARAM_1 , OracleTypes.CURSOR));
        compile();
    }
}

Following is the trace of exception that I got

Caused by: java.lang.AssertionError: Length not positive
    at oracle.jdbc.driver.NumberCommonAccessor.getBigDecimal(NumberCommonAccessor.java:2330)
    at oracle.jdbc.driver.NumberCommonAccessor.getObject(NumberCommonAccessor.java:3075)
    at oracle.jdbc.driver.T4CVarnumAccessor.getObject(T4CVarnumAccessor.java:248)
    at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:1753)
    at oracle.jdbc.driver.OracleCallableStatementWrapper.getObject(OracleCallableStatementWrapper.java:639)
    at sun.reflect.GeneratedMethodAccessor226.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy597.getObject(Unknown Source)
    at net.ttddyy.dsproxy.listener.OracleOutputParameterLogEntryCreator.getOutputParameters(OracleOutputParameterLogEntryCreator.java:65)
    at net.ttddyy.dsproxy.listener.OracleOutputParameterLogEntryCreator.getOutputParametersInDefaultFormat(OracleOutputParameterLogEntryCreator.java:46)
    at net.ttddyy.dsproxy.listener.OracleOutputParameterLogEntryCreator.getLogEntry(OracleOutputParameterLogEntryCreator.java:25)
    at net.ttddyy.dsproxy.listener.AbstractQueryLoggingListener.getEntry(AbstractQueryLoggingListener.java:32)
    at net.ttddyy.dsproxy.listener.AbstractQueryLoggingListener.afterQuery(AbstractQueryLoggingListener.java:24)
    at net.ttddyy.dsproxy.proxy.PreparedStatementProxyLogic.invoke(PreparedStatementProxyLogic.java:185)
    at net.ttddyy.dsproxy.proxy.jdk.CallableStatementInvocationHandler.invoke(CallableStatementInvocationHandler.java:29)
    at com.sun.proxy.$Proxy598.execute(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1144)
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1141)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1089)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1141)
    at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:123)

Following is the sample stored procedure for reference

create or replace Procedure MY_PROCEDURE
    (   INPUT_PARAM_1    IN NUMBER
      ,OUTPUT_PARAM_1    OUT NUMBER
) AS
BEGIN   
    SELECT INPUT_PARAM_1 + 1 INTO OUTPUT_PARAM_1 FROM dual;
END MY_PROCEDURE;

Jars I have used are ojdbc8-12.2.0.1.0 and spring-jdbc-4.3.13

I tried extending ResultSetSupportingSqlParameter to create my own SqlInParameter that fails with the exception.

I also looked into other SqlParameter classes of spring but there is not class which only works for only IN parameter of Oracle Stored procedure. Help me out in pointing what am i doing wrong over here.

Please have a look at [this](https://stackoverflow.com/help/mcve) article and reframe your question — Prashant, Jan 29 '18 at 08:55
Please include the definition of the stored procedure being called. — Mark Rotteveel, Jan 29 '18 at 11:03
@MarkRotteveel Added. Actually it is not the problem with SP execution. it executes successfully and when the out parameters are translated. It expects in parameter to be present and have a non zero length(This I debugged in the code) and since In parameter wont be treated out it fails.. — Java Samurai, Jan 30 '18 at 10:44

score 0 · Accepted Answer · answered Feb 12 '18 at 08:44

0

The problem got solved with using org.springframework.jdbc.core.SqlParameter for in parameters of stored procedures.

answered Feb 12 '18 at 08:44

Java Samurai

77
6

Calling oracle stored procedure with input parameter using SqlInOutParameter gives assert exception with ojdbc8

1 Answers1