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

check if this helps http://www.coderanch.com/t/489862/Spring/Calling-Stored-Procedure-Spring-StoredProcedure .Did you try calling the procedure in sql prompt ? Did it work ? — Lakshmi, Mar 08 '13 at 11:15
There is no error in the procedure it working fine, The error might in java side. — Krushna, Mar 08 '13 at 11:45
It worked for me only use CallableStatement. It is strange that in other similar procedures there are no problems. — Alex78191, Oct 17 '19 at 16:13
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. — Ziaullhaq Savanur, Nov 16 '21 at 07:02

score 4 · Answer 1 · answered Mar 08 '13 at 12:47

4

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

answered Mar 08 '13 at 12:47

Lakshmi

2,204
3
29
49

sometimes it could be enum type value that interferes with data type. – Smart Coder Apr 08 '22 at 15:40

score 1 · Answer 2 · answered Mar 08 '13 at 11:30

1

Probably, you need to insert schema identifier here:

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

answered Mar 08 '13 at 11:30

Egor Skriptunoff

23,359
2
34
64

@KrushnaCh.Dash - Are parameters for `new SqlParameter()` correct? – Egor Skriptunoff Mar 08 '13 at 12:05
Yes adding SQL parameter is optional as i have mention the type at the top – Krushna Mar 08 '13 at 12:11

Krushna · Accepted Answer · 2015-10-24T08:10:36.337

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()));

}

Why do you not include how you solved it instead of pasting code? — Murat Derya Özen, Jun 26 '14 at 13:06
Please see the edits, The error will happen due to mismatch of IN/OUT parameter as declare in procedure and java code — Krushna, Oct 24 '15 at 08:11

score 1 · Answer 4 · answered Jun 21 '17 at 18:49

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!
    }; 
}

score 0 · Answer 5 · answered May 31 '19 at 15:49

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.

score 0 · Answer 6 · answered Jun 18 '20 at 13:02

0

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")

answered Jun 18 '20 at 13:02

Michael

41,989
11
82
128

score 0 · Answer 7 · answered Oct 06 '22 at 11:22

0

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);

answered Oct 06 '22 at 11:22

Geórgia Maria

1

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

7 Answers7

Linked