如何通过SimpleJdbcCall调用返回TABLE_NAME%rowtype的Oracle函数

问题场景 #

要通过Spring的SimpleJdbcCall调用Oracle包中返回TABLE_NAME%ROWTYPE类型的函数，函数定义如下：

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

首次尝试与报错 #

调用代码：

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

触发错误：

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

二次调整与报错 #

修改后的代码：

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

仍报错：

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

解决方案 #

核心原因 #

Oracle的TABLE_NAME%ROWTYPE属于匿名STRUCT类型，Spring无法自动识别并完成映射，必须手动指定STRUCT的具体类型名称，同时配置对应的类型处理器。

步骤1：确认STRUCT类型名称 #

TABLE_NAME%ROWTYPE对应数据库内的系统生成类型名，可通过以下SQL查询获取：

SELECT type_name FROM user_types WHERE type_code = 'OBJECT';

通常这类名称为TABLE_NAME_ROWTYPE，也可直接查看表相关元数据确认。

步骤2：修改SimpleJdbcCall配置 #

这里提供两种可行的配置写法：

写法一：使用SqlOutParameter指定类型与映射器

public VerificationRequest getLastVerificationRequest(Integer applicationId) {
    // 替换为你查询到的实际STRUCT类型名
    String rowTypeName = "TABLE_NAME_ROWTYPE";

    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withCatalogName("pkg_NAME")
            .withFunctionName("getOneRowFromTable")
            .declareParameters(
                    new SqlParameter("applicationId", OracleTypes.NUMBER),
                    // 指定STRUCT类型名，并用BeanPropertyRowMapper自动映射字段
                    new SqlOutParameter("result", OracleTypes.STRUCT, rowTypeName,
                            new BeanPropertyRowMapper<>(VerificationRequest.class))
            );

    Map<String, Object> resultMap = simpleJdbcCall.execute(
            new MapSqlParameterSource().addValue("applicationId", applicationId)
    );

    return (VerificationRequest) resultMap.get("result");
}

写法二：使用SqlReturnStruct手动处理映射

public VerificationRequest getLastVerificationRequest(Integer applicationId) {
    String rowTypeName = "TABLE_NAME_ROWTYPE";

    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withCatalogName("pkg_NAME")
            .withFunctionName("getOneRowFromTable")
            .declareParameters(new SqlParameter("applicationId", OracleTypes.NUMBER))
            // 注册返回值的STRUCT类型与实体类
            .declareParameters(new SqlReturnStruct(rowTypeName, VerificationRequest.class))
            .withReturnValue();

    Map<String, Object> resultMap = simpleJdbcCall.execute(
            new MapSqlParameterSource().addValue("applicationId", applicationId)
    );

    return (VerificationRequest) resultMap.get("result");
}

注意：如果数据库字段名和实体属性名不匹配，建议手动实现RowMapper完成字段映射，避免自动映射失败。

步骤3：可选优化——改用自定义OBJECT类型 #

如果觉得上述配置繁琐，可以修改Oracle函数，返回自定义OBJECT类型而非%ROWTYPE：

-- 先创建自定义对象类型
CREATE OR REPLACE TYPE verification_request_obj AS OBJECT (
    ID INTEGER,
    APPLICATION_ID INTEGER,
    -- 按表结构添加其他字段
);

-- 修改函数返回类型
function getOneRowFromTbale( applicationId in integer )
return verification_request_obj;

这种情况下，Spring的类型映射会更简单，直接将上述代码中的rowTypeName替换为verification_request_obj即可。

内容的提问来源于stack exchange，提问作者AL mir