SQL state [99999]; error code [17004]; Invalid column type: 1111 With Spring SimpleJdbcCall

Question

Hi All I am using spring simple JDBC template to call the oracle procedure the below are my code.

The procedure

create or replace
PROCEDURE get_all_system_users(
pi_client_code IN VARCHAR2,
po_system_users OUT T_SYSTEM_USER_TAB,
po_error_code        OUT NUMBER,
po_error_description OUT VARCHAR2)
IS
ctr NUMBER;
sysUser SYSTEM_USER_OBJ;
BEGIN
ctr:=0;
po_system_users:= t_system_user_tab();
end

The Spring Dao class

public class ManualSaleStoredProcedureDao {

private SimpleJdbcCall getAllSytemUsers;

public List<SystemUser> getAllSytemUsers(String clientCode) {

    MapSqlParameterSource in = new MapSqlParameterSource();
    in.addValue("pi_client_code", clientCode);
    in.addValue("po_system_users", null,
            OracleTypes.ARRAY, "T_SYSTEM_USER_TAB");

    Map<String, Object> result = getAllSytemUsers.execute(in);

    return null;

}

public void setDataSource(DataSource dataSource) {

    getAllSytemUsers = new SimpleJdbcCall(dataSource)
            .withSchemaName("SChemaName")
            .withProcedureName("get_all_system_users")

            .declareParameters(

                    new SqlParameter(
                            "pi_client_code",
                            OracleTypes.VARCHAR,
                            "pi_client_code"));

}

When I am calling Map<String, Object> result = getAllSytemUsers.execute(in); Iam getting the below exception

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call VSC.GET_ALL_SYSTEM_USERS(?, ?, ?, ?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:351)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
at com.budco.vsc.dao.ManualSaleStoredProcedureDao.getAllSytemUsers(ManualSaleStoredProcedureDao.java:30)
at com.budco.vsc.dao.ManualSaleStoredProcedureDaoITest.getCustomerNotes(ManualSaleStoredProcedureDaoITest.java:64)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
   Caused by: java.sql.SQLException: Invalid column type: 1111
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:95)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:95)
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:198)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1008)
... 35 more

Schaumberger

check if this helps coderanch.com/t/489862/Spring/… .Did you try calling the procedure in sql prompt ? Did it work ? – Hoofbeat

There is no error in the procedure it working fine, The error might in java side. – Schaumberger

It worked for me only use CallableStatement. It is strange that in other similar procedures there are no problems. – Bellows

If you use IN clause in sql query, then you can't pass list of values in place of this directly it need to converted to comma separated strings or need to be handled smartly. – Revelationist

Answer 1

Finally I solve the issues using below code. This type of error will happen when there is a mismatch between In/Out parameter as declare in procedure and in java code declareParameters. Here we need to defined oracle return tab

public class ManualSaleStoredProcedureDao {

private SimpleJdbcCall getAllSytemUsers;

public List<SystemUser> getAllSytemUsers(String clientCode) {

    MapSqlParameterSource in = new MapSqlParameterSource();
    in.addValue("pi_client_code", clientCode);
    Map<String, Object> result = getAllSytemUsers.execute(in);
    @SuppressWarnings("unchecked")
    List<SystemUser> systemUsers = (List<SystemUser>) result
            .get(VSCConstants.GET_SYSTEM_USER_OUT_PARAM1);
    return systemUsers;

}

public void setDataSource(DataSource dataSource) {

    getAllSytemUsers = new SimpleJdbcCall(dataSource)
            .withSchemaName(VSCConstants.SCHEMA)
            .withProcedureName(VSCConstants.GET_SYSTEM_USER_PROC_NAME)
            .declareParameters(
                    new SqlParameter(
                            "pi_client_code",
                            OracleTypes.NUMBER,
                            "pi_client_code"),
                    new SqlInOutParameter(
                            "po_system_users",
                            OracleTypes.ARRAY,
                            "T_SYSTEM_USER_TAB",
                            new OracleSystemUser()));

}

Schaumberger

Why do you not include how you solved it instead of pasting code? – Calia

Please see the edits, The error will happen due to mismatch of IN/OUT parameter as declare in procedure and java code – Schaumberger

hey I don't know if you still have the code but can you show me the code of OracleSystemUser() class – Procambium

Sorry Bro, I do not have the code with me now – Schaumberger

Answer 2

I think the problem is with the datatype of the data you are passing Caused by: java.sql.SQLException: Invalid column type: 1111 check the datatypes you pass with the actual column datatypes may be there can be some mismatch or some constraint violation with null

Hoofbeat

sometimes it could be enum type value that interferes with data type. – Schwann

Answer 3

Probably, you need to insert schema identifier here:

in.addValue("po_system_users", null, OracleTypes.ARRAY, "your_schema.T_SYSTEM_USER_TAB");

Slogan

@KrushnaCh.Dash - Are parameters for new SqlParameter() correct? – Slogan

Yes adding SQL parameter is optional as i have mention the type at the top – Schaumberger

Answer 4

I have a function which returns a CLOB and I was seeing the above error when I'd forgotten to declare the return value as an output parameter. Initially I had:

protected SimpleJdbcCall buildJdbcCall(JdbcTemplate jdbcTemplate)
{
    SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
        .withSchemaName(schema)
        .withCatalogName(catalog)
        .withFunctionName(functionName)
        .withReturnValue()          
        .declareParameters(buildSqlParameters());

    return call;
}

public SqlParameter[] buildSqlParameters() {
    return new SqlParameter[]{
        new SqlParameter("p_names", Types.VARCHAR),
        new SqlParameter("p_format", Types.VARCHAR),
        new SqlParameter("p_units", Types.VARCHAR),
        new SqlParameter("p_datums", Types.VARCHAR),
        new SqlParameter("p_start", Types.VARCHAR),
        new SqlParameter("p_end", Types.VARCHAR),
        new SqlParameter("p_timezone", Types.VARCHAR),
        new SqlParameter("p_office_id", Types.VARCHAR),
        };
}

The buildSqlParameters method should have included the SqlOutParameter:

public SqlParameter[] buildSqlParameters() {
    return new SqlParameter[]{
        new SqlParameter("p_names", Types.VARCHAR),
        new SqlParameter("p_format", Types.VARCHAR),
        new SqlParameter("p_units", Types.VARCHAR),
        new SqlParameter("p_datums", Types.VARCHAR),
        new SqlParameter("p_start", Types.VARCHAR),
        new SqlParameter("p_end", Types.VARCHAR),
        new SqlParameter("p_timezone", Types.VARCHAR),
        new SqlParameter("p_office_id", Types.VARCHAR),
        new SqlOutParameter("l_clob", Types.CLOB)  // <-- This was missing!
    }; 
}

Bara

Answer 5

We had the same issue when we had a typo in the mybatis mapping file like

        ....
        #{column1Name,          jdbcType=INTEGER},
        #{column2Name,          jdbcType=VARCHAR},
        #{column3Name,      jdbcTyep=VARCHAR}  -- do you see the typo ?
        .....

So check this kind of typos as well. Unfortunately, it can not understand the typo in compile/build time, it causes an unchecked exception and booms in runtime.

Underact

Answer 6

I had this problem, and turns out the problem was that I had used

new SimpleJdbcCall(jdbcTemplate)
    .withProcedureName("foo")

instead of

new SimpleJdbcCall(jdbcTemplate)
    .withFunctionName("foo")

Colloid

Answer 7

I had this problem. The cause was a table of record indexed type.

This

TYPE tp_tb IS TABLE OF VARCHAR2(21) INDEX BY PLS_INTEGER;

Instead of

TYPE tp_tb IS TABLE OF VARCHAR2(21);

Divergency