StoredProcedureQuery with Oracle Type fails

Question

Please help. I am not able to call oracle procedure which has Oracle type as input. Also share if any sample available in git. Thanks in advance.

Oracle Type:

create or replace TYPE STUDENT_ID_ARRAY AS TABLE OF NUMBER;

Oracle Proc:

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:

@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();
    }

Getting Below Error:

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

Answer 1

Java's JDBC doesn't directly support Oracle's custom types. You need to use Oracle-specific classes from the oracle.jdbc package.

  @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>

Reference

[1] Oracle JDBC