How to call oracle function which return sys_refcursor using java + hibernate

Question

I am having a problem while calling oracle function, the function look like bellow

CREATE OR REPLACE FUNCTION get_port_out_select
  (pi_id_pout_order IN PORT_OUT_ORDER.ID_POUT_ORDER % TYPE)
RETURN sys_refcursor
IS
lc_cursor sys_refcursor;
BEGIN

OPEN lc_cursor FOR
SELECT ID_POUT_ORDER, ID_LEC_USER, ID_POUT_LEC, ID_SERVICE_REC, POUT_TN, ID_IPTN, POUT_LEC_OCN, POUT_LEC_SPID, POUT_ALI_CODE,
LEC_CONTACT_FNAME, LEC_CONTACT_LNAME, LEC_CONTACT_TN, LEC_CONTACT_EMAIL,
DL_LEC_OCN, CUST_ACCT, CUST_F_NAME, CUST_L_NAME,
SVC_ADDR_STR, SVC_ADDR_NUM, SVC_ADDR_CITY, SVC_ADDR_STATE, SVC_ADDR_ZIP,
POUT_LOA_DATE, POUT_DDD, POUT_FOC, E_MAIL_SENT_DATE, V_MAIL_SENT_DATE, ID_POUT_STATUS, CUSTOMER_TYPE, AUTHORIZATION_NAME, ID_MASTER_POUT_ORDER
FROM PORT_OUT_ORDER
WHERE ID_POUT_ORDER = pi_id_pout_order
AND ID_POUT_STATUS < 95;

RETURN lc_cursor;
END;

Answer 1

This is how to get the result in a Java class (hopefully you can port it to Hibernate using this question as an exemplar):

Oracle Setup:

CREATE FUNCTION get_ref_cursor RETURN SYS_REFCURSOR
IS
  out_cursor SYS_REFCURSOR;
BEGIN
  OPEN out_cursor FOR
    SELECT 123 AS col1 FROM DUAL UNION ALL
    SELECT 456 FROM DUAL UNION ALL
    SELECT 789 FROM DUAL;

  RETURN out_cursor;
END;
/

Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class GetRefCursorFromFunction
{
  public static void main( final String[] args ){
    try{
      Class.forName( "oracle.jdbc.OracleDriver" );

      Connection con = DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:orcl",
          "USERNAME",
          "PASSWORD"
      );

      OracleCallableStatement st =
        (OracleCallableStatement) con.prepareCall( "BEGIN :1 := get_Ref_Cursor(); END;" );
      st.registerOutParameter( 1, OracleTypes.CURSOR );
      System.out.println( st.execute() );
      ResultSet rs = st.getCursor( 1 );
      while ( rs.next() )
      {
        System.out.println( rs.getInt( 1 ) );
      }

      st.close();
      con.close();
    } catch (ClassNotFoundException | SQLException ex) {
      System.out.println( ex.getMessage() );
      ex.printStackTrace();
    }
  }
}

(Note: this assumes you are using Oracle's driver to connect to the database.)

Output:

123
456
789