Resolving Invalid column type: 1111 Error When Calling Oracle Stored Procedures with Spring SimpleJdbcCall

Background and Error Analysis

When using Spring Framework's SimpleJdbcCall to invoke Oracle database stored procedures, developers often encounter the SQL state [99999]; error code [17004]; Invalid column type: 1111 exception. This error typically stems from a mismatch between the parameter types declared in Java code and those defined in the database stored procedure. Specifically, error code 1111 corresponds to OracleTypes.OTHER in the Oracle JDBC driver, indicating an unrecognized type, often due to incorrect declaration of OUT parameters or complex data types (e.g., arrays, object types).

Core Issue: Parameter Declaration Mismatch

In the provided case, the stored procedure get_all_system_users defines an OUT parameter po_system_users of type T_SYSTEM_USER_TAB, an Oracle array type. However, in the initial Java code, parameter declaration only used SqlParameter, which is suitable for IN parameters but not for OUT or INOUT parameters. When SimpleJdbcCall attempts to register the OUT parameter, the JDBC driver cannot process it due to ambiguous typing, leading to the Invalid column type exception.

Solution: Using SqlInOutParameter and Type Handlers

To resolve this issue, OUT parameters must be declared correctly. For Oracle array-type OUT parameters, use SqlInOutParameter (if the parameter is INOUT) or SqlOutParameter (if purely OUT), and specify an appropriate type handler. Below is the corrected code example:

public class ManualSaleStoredProcedureDao {
    private SimpleJdbcCall getAllSytemUsers;

    public void setDataSource(DataSource dataSource) {
        getAllSytemUsers = new SimpleJdbcCall(dataSource)
                .withSchemaName("SCHEMA_NAME")
                .withProcedureName("get_all_system_users")
                .declareParameters(
                        new SqlParameter("pi_client_code", OracleTypes.VARCHAR),
                        new SqlInOutParameter("po_system_users", OracleTypes.ARRAY, "T_SYSTEM_USER_TAB", new OracleSystemUser())
                );
    }

    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("po_system_users");
        return systemUsers;
    }
}

Key improvements:

• In declareParameters, use SqlInOutParameter to declare the po_system_users parameter, specifying the type as OracleTypes.ARRAY and type name as T_SYSTEM_USER_TAB.
• Add a custom type handler OracleSystemUser (which should implement Spring's SqlReturnType or ParameterizedType interface) to handle the conversion from Oracle array to Java List.
• When executing the call, do not manually add OUT parameters to MapSqlParameterSource, as SimpleJdbcCall automatically handles OUT parameter registration and retrieval.

Additional Considerations

Based on supplementary answers, the following points are also noteworthy:

• Parameter Data Type Consistency: Ensure that parameter types declared in Java code (e.g., OracleTypes.VARCHAR) match the database stored procedure definitions. For instance, if the stored procedure expects NUMBER but Java uses VARCHAR, similar errors may occur.
• Schema Name Qualification: For complex data type names (e.g., arrays), include the schema name (e.g., SCHEMA.T_SYSTEM_USER_TAB) to avoid database parsing errors.
• Function vs. Procedure Distinction: For Oracle functions, use withReturnValue() and declare SqlOutParameter for the return value; otherwise, missing OUT parameter declarations can trigger Invalid column type errors.

Deep Dive into Type Handlers

The custom type handler OracleSystemUser plays a crucial role in mapping the Oracle array type T_SYSTEM_USER_TAB to Java's List<SystemUser>. This typically involves the following steps:

public class OracleSystemUser implements SqlReturnType {
    @Override
    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
        Array array = cs.getArray(paramIndex);
        if (array == null) return Collections.emptyList();
        Object[] data = (Object[]) array.getArray();
        List<SystemUser> users = new ArrayList<>();
        for (Object obj : data) {
            Struct struct = (Struct) obj;
            Object[] attributes = struct.getAttributes();
            SystemUser user = new SystemUser();
            user.setId((BigDecimal) attributes[0]);
            user.setName((String) attributes[1]);
            users.add(user);
        }
        return users;
    }
}

This ensures seamless data conversion from the database to Java objects, preventing type mismatch issues.

Conclusion and Best Practices

The Invalid column type: 1111 error often indicates improper parameter declaration when using Spring SimpleJdbcCall to invoke Oracle stored procedures. By correctly using SqlInOutParameter or SqlOutParameter, specifying precise data types and type handlers, and ensuring parameter consistency, this issue can be effectively resolved. Developers should always verify that parameter declarations align with database definitions and leverage Spring's type handling mechanisms to simplify operations with complex data types, thereby enhancing code reliability and maintainability.