How to call Oracle function (via SimpleJdbcCall) which return custom type TABLE_NAME%rowtype

334 Views Asked by At

I am trying to use a function simpleJdbcCall to call Oracle function which return custom type

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 error:

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

0

There are 0 best solutions below