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

java oracle spring jdbctemplate
Krushna picture Krushna · Mar 8, 2013 · Viewed 124.1k times · Source

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

Answer

Lakshmi picture Lakshmi · Mar 8, 2013

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

Related questions

How to fix "Driver does not support get/set network timeout for connections" while connecting to oracle database from spring boot app?

I was trying to connect to my table and insert some data.We are using oracle database. In the code I have used oracle thin driver ojdbc14.I am getting 2018-12-27 11:08:58.810 INFO 16548 --- [ main] com.zaxxer.hikari.pool.PoolBase : …

Read More
How to read a ARRAY of types returned from a stored proc using java?

This is a continuation of the question posted under the following location: Java program to pass List of Bean to a oracle stored procedure - Pass entire list at one shot rather than appending objects one after the other I …

Read More
Spring Boot Actuator /health endpoint does not show database or file system information

I cannot get database information or filesystem information to show up on the /health endpoint. I only can get: { "status": "UP" } Details about my setup and configuration: - Spring Boot 1.3.3 - Running the WAR on JBoss EAP 6.4 - Datasource is …

Read More