How to call oracle function which return sys_refcu

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;

it return the value as cursor Bellow is the result when i execute the query in DB.

{<
ID_POUT_ORDER=2004, 
ID_LEC_USER=39,
ID_POUT_LEC=36, 
ID_SERVICE_REC=20277159, 
POUT_TN=7186846814, 
ID_IPTN=3990, 
POUT_LEC_OCN=9147, 
POUT_LEC_SPID=9147,
POUT_ALI_CODE=9147,
LEC_CONTACT_FNAME=unique, 
LEC_CONTACT_LNAME=user, 
LEC_CONTACT_TN=5165551234, 
LEC_CONTACT_EMAIL=[e-mail], 
DL_LEC_OCN=9147,
CUST_ACCT=0785852664101,
CUST_F_NAME=YYYYYYY,
CUST_L_NAME=ZZZZZZ,
SVC_ADDR_STR=10thav,
SVC_ADDR_NUM=812,
SVC_ADDR_CITY=levittown,
SVC_ADDR_STATE=NY,
SVC_ADDR_ZIP=11756,
POUT_LOA_DATE=11-APR-07, 
POUT_DDD=11-APR-07,
POUT_FOC=11-APR-07,
E_MAIL_SENT_DATE=10-APR-07,
V_MAIL_SENT_DATE=null,
ID_POUT_STATUS=1,
CUSTOMER_TYPE=Residential,
AUTHORIZATION_NAME=unique user,
ID_MASTER_POUT_ORDER=null
>,
}

I am using this to call the function

Query query = this.em.createNativeQuery("SELECT get_port_out_select(2004) FROM DUAL");
query.getSingleResult();

I am having a problem in getting the result. I have checked the connectivity to database all is fine. How do I handle this

Update 1:

Created the entity class

@NamedNativeQueries(value = {
        @NamedNativeQuery(
                name = "functionCall",
                query = "{ SELECT get_port_out_select(2004) FROM DUAL }",
                resultClass = PortOutSelectCursor.class)
       
})

@Entity
public class PortOutSelectCursor{
	@Id
	@Column(name="ID_POUT_ORDER")
	private int ID_POUT_ORDER;
	
	@Column(name="ID_LEC_USER")
	private int ID_LEC_USER;
	
	@Column(name="ID_SERVICE_REC")
	private int ID_SERVICE_REC;
	
	@Column(name="POUT_TN")
	private String POUT_TN;
	
	@Column(name="ID_IPTN")
	private int ID_IPTN;
  
  // added all the attribute 
  // added default constructor
  // added setter and getter
  }
	

Added the mapping in persistence.xml

<persistence-unit name="XOSS_PDB_PROCEDURE_PERSISTENCE_UNIT_1"
		transaction-type="JTA">
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<jta-data-source>myDataSource</jta-data-source>
		<mapping-file>META-INF/procedure.xml</mapping-file>
		 <class>com.amdocs.oss.alt.procedure.response.PortOutSelectCursor</class>
		<properties>
			<property name="javax.persistence.jdbc.driver" value="oracle.jdbc.driver.OracleDriver" />
			<property name="hibernate.show_sql" value="true" />
			<property name="hibernate.format_sql" value="true" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
			<property name="hibernate.hbm2ddl.auto" value="none" />
			<property name="hibernate.transaction.jta.platform"
				value="org.hibernate.service.jta.platform.internal.WeblogicJtaPlatform" />
			<property name="hibernate.proc.param_null_passing" value="true" />
			<property name="alt.procedure_names" value="insertEquipReturn,getPortOutSelect" />
			 <property name="hibernate.archive.autodetection" value="class"/> 
		</properties>
	</persistence-unit>

Dao class look like this

public void getPortOutSelect() {
  try {
    List < Object[] > a = this.em.createNamedQuery("functionCall").getResultList();

    Object[] r = a.get(0);
  } catch (Exception e) {
    e.printStackTrace();
  }
  return null;
}

Still struggling to solve the problem. please help

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