title: How to call oracle function which return sys_refcursor using java + hibernate
body: <p>I am having a problem while calling oracle function,
  the function look like bellow</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>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 &lt; 95;
  
  RETURN lc_cursor;
  END;</code></pre>
  </div>
  </div>
  </p>
  
  <p>it return the value as cursor 
  Bellow is the result when i execute the query in DB.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>{&lt;
  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=michael21474@gmail.com, 
  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
  &gt;,
  }</code></pre>
  </div>
  </div>
  
  I am using this to call the function</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>Query query = this.em.createNativeQuery("SELECT get_port_out_select(2004) FROM DUAL");
  query.getSingleResult();</code></pre>
  </div>
  </div>
  </p>
  
  <p>I am having a problem in getting the result.
  I have checked the connectivity to database all is fine. 
  How do I handle this</p>
  
  <p>Update 1:</p>
  
  <p>Created the entity class</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>@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
    }
   </code></pre>
  </div>
  </div>
  </p>
  
  <p>Added the mapping in <code>persistence.xml</code></p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;persistence-unit name="XOSS_PDB_PROCEDURE_PERSISTENCE_UNIT_1"
    transaction-type="JTA"&gt;
    &lt;provider&gt;org.hibernate.jpa.HibernatePersistenceProvider&lt;/provider&gt;
    &lt;jta-data-source&gt;myDataSource&lt;/jta-data-source&gt;
    &lt;mapping-file&gt;META-INF/procedure.xml&lt;/mapping-file&gt;
     &lt;class&gt;com.amdocs.oss.alt.procedure.response.PortOutSelectCursor&lt;/class&gt;
    &lt;properties&gt;
     &lt;property name="javax.persistence.jdbc.driver" value="oracle.jdbc.driver.OracleDriver" /&gt;
     &lt;property name="hibernate.show_sql" value="true" /&gt;
     &lt;property name="hibernate.format_sql" value="true" /&gt;
     &lt;property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" /&gt;
     &lt;property name="hibernate.hbm2ddl.auto" value="none" /&gt;
     &lt;property name="hibernate.transaction.jta.platform"
      value="org.hibernate.service.jta.platform.internal.WeblogicJtaPlatform" /&gt;
     &lt;property name="hibernate.proc.param_null_passing" value="true" /&gt;
     &lt;property name="alt.procedure_names" value="insertEquipReturn,getPortOutSelect" /&gt;
      &lt;property name="hibernate.archive.autodetection" value="class"/&gt; 
    &lt;/properties&gt;
   &lt;/persistence-unit&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>Dao class look like this </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>public void getPortOutSelect() {
    try {
      List &lt; Object[] &gt; a = this.em.createNamedQuery("functionCall").getResultList();
  
      Object[] r = a.get(0);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }</code></pre>
  </div>
  </div>
  </p>
  
  <p>Still struggling to solve the problem. please help</p>