Call PL/SQL function that returns list of records from Hibernate

Question

While trying to call such PL/SQL function

type t_messages_rec is record (
   id NUMBER
  ,code VARCHAR2(30)
  ,type VARCHAR2(3)
  ,text VARCHAR2(100)
);
  
type t_messages_tab is table of t_messages_rec;
  
function get_and_clear_messages return t_messages_tab
pipelined;

from java

import javax.persistence.EntityManager;
import oracle.jdbc.OracleTypes;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.jdbc.ReturningWork;

// imports are listed for clarity 
    
    ReturningWork work = connection -> {
      try (CallableStatement function = connection.prepareCall("{ ? = call PACKAGE_NAME.get_and_clear_messages() }")) {
        function.registerOutParameter(1, OracleTypes.REF_CURSOR);
        function.execute();

        return function.getResultSet();
      }
    };

    Session session = entityManager.unwrap(Session.class);
    try {
      return (List<Message>) session.doReturningWork(work);
    } catch (HibernateException e) {
      e.printStackTrace();
    }

unfortunately it results in exception :(

Caused by: Error : 6550, Sql = BEGIN :1 := PACKAGE_NAME.get_and_clear_messages() ; END;,
OriginalSql = { ? = call PACKAGE_NAME.get_and_clear_messages() }, 
Error Msg = ORA-06550: line 61, column 12:
PLS-00653: aggregate/table functions are not allowed

I suspect, (but I can be wrong), that this line

  try (CallableStatement function = connection.prepareCall("{ ? = call PACKAGE_NAME.get_and_clear_messages() }")) {

means I want to store the result of the call in a '?' varaible, what is not allowed with pipelined functions.

What is the proper way to obtain resutls from pipelined function using Hibernate?

Panicking Developer · Accepted Answer · 2021-03-04 10:24:48Z

1

I sorted it out. Apparently pipelined functions can be queried with standard sql :)

Ex:

SELECT * FROM PACKAGE_NAME.GET_AND_CLEAR_MESSAGES() m

answered Mar 4, 2021 at 10:24

Panicking Developer

831 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call PL/SQL function that returns list of records from Hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related