Spring SimpleJdbcCall and Oracle function which returns custom type TABLE_NAME%rowtype

Hello!

I am trying to use a SimpleJdbcCall to call an Oracle function which returncustom typeTABLE_NAME%rowtype`.

Oracle function:

function getOneRowFromTbale( applicationId in integer )
return table_name%rowtype;

After call:

public VerificationRequest getLastVerificationRequest(Integer applicationId) {
    simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withCatalogName("pkg_NAME")
            .withFunctionName("getOneRowFromTable");
    return simpleJdbcCall.executeFunction(VerificationRequest.class, new MapSqlParameterSource()
            .addValue("applicationId", applicationId));
}

get error:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call pkg_NAME.getOneRowFromTable(?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111

after changing:

public VerificationRequest getLastVerificationRequest(Integer applicationId) {
    simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withCatalogName("pkg_NAME")
            .withFunctionName("getOneRowFromTable")
            .declareParameters(
                    new SqlParameter(
                            "applicationId",
                            OracleTypes.NUMBER
                    ),
                    new SqlInOutParameter(
                            "result",
                            OracleTypes.STRUCT
                    )
            );
    Map<String, Object> out =  simpleJdbcCall.execute(new MapSqlParameterSource()
            .addValue("applicationId", applicationId));
    return (VerificationRequest) out.get("result");
}

get another:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call pkg_NAME.getOneRowFromTable(?)}]; SQL state [99999]; error code [17068]; Invalid argument(s) in call; nested exception is java.sql.SQLException: Invalid argument(s) in call

Thanks

Comment From: jhoeller

As far as I can see, there is nothing specific about Spring's stored procedure support here. This rather seems to be a problem with instructing the Oracle JDBC driver properly which we are not experts on either. Pretty much the only thing I can spot above is that SqlInOutParameter might be better off as a SqlOutParameter. For everything else, you might want to try StackOverflow instead.