StoredProcedureQuery с типом Oracle завершается с ошибкой

Пожалуйста помоги. Я не могу вызвать процедуру Oracle, которая имеет тип Oracle в качестве входных данных. Также поделитесь, если какой-либо образец доступен в git. Заранее спасибо.

Тип оракула:

create or replace TYPE STUDENT_ID_ARRAY AS TABLE OF NUMBER;

Процесс Oracle:

create or replace NONEDITIONABLE PACKAGE student_pkg AS
    -- Define RefCursor type for returning cursors from procedures
    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

    -- Implementation of the GetStudentInfo procedure
    PROCEDURE GetStudentInfo(p_department_id IN department.id%TYPE, p_student_ids IN STUDENT_ID_ARRAY, p_student_info OUT RefCursor)
    IS
    BEGIN
        -- Call the existing procedure to get student details by department
        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
@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");

        // Set parameters
        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        : Initializing Servlet 'dispatcherServlet'
2024-05-30T06:32:42.167+05:30  INFO 24604 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 1 ms
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 Error: 6550, SQLState: 65000
2024-05-30T06:32:42.385+05:30 ERROR 24604 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETSTUDENTINFO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

https://docs.oracle.com/error-help/db/ora-06550/
2024-05-30T06:32:42.396+05:30 ERROR 24604 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: Error calling CallableStatement.getMoreResults [ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETSTUDENTINFO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Вы когда-нибудь пробовали исходный тип номера, а не тип номера Java, предоставляемый ojdbc?

Peng 03.06.2024 10:03

@Peng Спасибо за ответ, можете ли вы уточнить, что вы подразумеваете под типом исходного номера? Также предоставьте любую ссылку на то же самое.

sunleo 04.06.2024 15:02
0
2
102
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Java JDBC не поддерживает напрямую пользовательские типы Oracle. Вам необходимо использовать специфичные для Oracle классы из пакета oracle.jdbc.

  @Override
    public List<Object[]> getStudentInfo(int departmentId, List<Integer> studentIds) throws SQLException {
        // Obtain Oracle connection
        Connection connection = entityManager.unwrap(Connection.class);
        OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);

        // Convert List<Integer> to java.sql.Array
        Integer[] studentIdsArray = studentIds.toArray(new Integer[0]);
        Array oracleArray = oracleConnection.createOracleArray("STUDENT_ID_ARRAY", studentIdsArray);

        // Create stored procedure query
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("student_pkg.GetStudentInfo");

        // Set parameters
        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);
        query.setParameter("p_student_ids", oracleArray);

        query.execute();

        return query.getResultList();
    }
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>19.8.0.0</version>
</dependency>

Ссылка

[1] Oracle JDBC

Я проверю и отвечу

sunleo 06.06.2024 09:51

Попробуйте объявить входной массив как:

TYPE STUDENT_ID_ARRAY AS TABLE OF NUMBER INDEX BY PLS_INTEGER;

Но основная часть заключается в том, что ваш код использует ORM Hibernate для вызова определенной процедуры PL/SQL.

В концепции ORM следует остерегаться такого кода. Он не портативен. Лучше использовать обычный SELECT, чем вызов процедуры.

В противном случае вы просто тратите ресурсы, используя Hibernate или другие ORM, вместо прямой работы через драйвер происходит гораздо быстрее и используется меньше памяти.

Спасибо, но в моем случае это наследие, с которым мне придется справиться.

sunleo 25.07.2024 12:37

Другие вопросы по теме