调用含Oracle自定义类型的存储过程失败求助

我无法调用带有Oracle自定义类型作为输入参数的存储过程，请求协助解决，同时若有相关Git示例也请分享，谢谢。

Oracle类型定义 #

create or replace TYPE STUDENT_ID_ARRAY AS TABLE OF NUMBER;

Oracle存储过程包 #

create or replace NONEDITIONABLE PACKAGE student_pkg AS
    -- 定义用于返回游标的RefCursor类型
    TYPE RefCursor IS REF CURSOR;

    PROCEDURE GetStudentInfo(p_department_id IN department.id%TYPE, p_student_ids IN STUDENT_ID_ARRAY, p_student_info OUT RefCursor);
END student_pkg;

create or replace NONEDITIONABLE PACKAGE BODY student_pkg AS

    -- GetStudentInfo存储过程实现
    PROCEDURE GetStudentInfo(p_department_id IN department.id%TYPE, p_student_ids IN STUDENT_ID_ARRAY, p_student_info OUT RefCursor)
    IS
    BEGIN
        -- 通过部门ID获取学生详情
        OPEN p_student_info FOR
            SELECT s.name AS student_name, d.name as department_name
            FROM student s
            INNER JOIN department d ON s.department_id = d.id
            WHERE d.id = p_department_id
            AND s.id in (SELECT COLUMN_VALUE FROM TABLE(p_student_ids));
    END GetStudentInfo;
END student_pkg;

Spring Data JPA Repository实现 #

@Repository
@RequiredArgsConstructor
public class TypeRepositoryImpl implements TypeRepository {

    private final EntityManager entityManager;

    @Override
    public List<Object[]> getStudentInfo(int departmentId, List<Integer> studentIds) {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("student_pkg.GetStudentInfo");

        // 注册参数
        query.registerStoredProcedureParameter("p_department_id", Integer.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("p_student_ids", Object.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("p_student_info", void.class, ParameterMode.REF_CURSOR);

        query.setParameter("p_department_id", departmentId);
        query.setParameter("p_student_ids", studentIds);
        
        query.execute();
        return query.getResultList();
    }
}

错误信息 #

2024-05-30T06:32:42.166+05:30  INFO 24604 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : 初始化Servlet 'dispatcherServlet'
2024-05-30T06:32:42.167+05:30  INFO 24604 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : 初始化完成，耗时1ms
2024-05-30T06:32:42.249+05:30 DEBUG 24604 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    {call student_pkg.GetStudentInfo(?, ?, ?)}
Hibernate: 
    {call student_pkg.GetStudentInfo(?, ?, ?)}
2024-05-30T06:32:42.385+05:30  WARN 24604 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL错误: 6550, SQL状态: 65000
2024-05-30T06:32:42.385+05:30 ERROR 24604 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-06550: 第1行, 第7列:
PLS-00306: 调用'GETSTUDENTINFO'时参数数量或类型错误
ORA-06550: 第1行, 第7列:
PL/SQL: 语句被忽略

2024-05-30T06:32:42.396+05:30 ERROR 24604 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : 路径为[]的上下文里，Servlet [dispatcherServlet]的service()方法抛出异常 [请求处理失败: org.springframework.dao.InvalidDataAccessResourceUsageException: 调用CallableStatement.getMoreResults出错 [ORA-06550: 第1行, 第7列:
PLS-00306: 调用'GETSTUDENTINFO'时参数数量或类型错误
ORA-06550: 第1行, 第7列:
PL/SQL: 语句被忽略

问题原因 #

直接传入List<Integer>无法被Oracle识别为自定义的STUDENT_ID_ARRAY类型，需要将Java集合转换为Oracle对应的ARRAY对象；同时参数注册时使用Object.class未明确指定类型，也会导致类型不匹配。

解决方案 #

步骤1：修改Repository实现，转换Java集合为Oracle ARRAY #

需要获取底层的OracleConnection，通过它创建对应自定义类型的ARRAY对象：

@Repository
@RequiredArgsConstructor
public class TypeRepositoryImpl implements TypeRepository {

    private final EntityManager entityManager;

    @Override
    public List<Object[]> getStudentInfo(int departmentId, List<Integer> studentIds) {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("student_pkg.GetStudentInfo");

        // 注册参数，REF_CURSOR使用Oracle内置游标类型
        query.registerStoredProcedureParameter("p_department_id", Integer.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("p_student_ids", ARRAY.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("p_student_info", void.class, ParameterMode.REF_CURSOR);

        query.setParameter("p_department_id", departmentId);

        // 将List转换为Oracle ARRAY
        try {
            Session session = entityManager.unwrap(Session.class);
            Connection connection = session.doReturningWork(Connection::getConnection);
            OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
            
            // 自定义类型名称要和Oracle中定义的完全一致（Oracle默认大写）
            ARRAY studentIdArray = oracleConnection.createARRAY("STUDENT_ID_ARRAY", studentIds.toArray());
            query.setParameter("p_student_ids", studentIdArray);
        } catch (SQLException e) {
            throw new RuntimeException("转换学生ID数组失败", e);
        }
        
        query.execute();
        return query.getResultList();
    }
}

步骤2：确保依赖正确 #

确保项目中引入了对应版本的Oracle JDBC驱动：

<!-- Maven依赖示例 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.13.0.0</version>
</dependency>

相关Git示例思路 #

核心逻辑是通过EntityManager获取OracleConnection，创建对应自定义类型的ARRAY对象：

• 重点注意自定义类型名称与Oracle中的定义完全一致（区分大小写）
• 处理JDBC连接时需正确unwrap为OracleConnection
• 异常捕获要覆盖数组转换的可能报错场景

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