Re: [eclipselink-users] Exception calling an Oracle Stored Procedure

["Jose Alvarez de Lara" <dakhla.0563@xxxxxxxxxxx>]

Hi,

 

I did what you said and this is what I found in the log,

 

[#|2011-03-25T00:11:28.187+0100|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=28;_ThreadName=Thread-1;|[EL Fine]: 2011-03-25 00:11:28.171--ServerSession(25956612)--Connection(26992611)--Thread(Thread[p: thread-pool-1; w: 5,5,main])--BEGIN HR.RECORD_SET.COUNTRIES_LIST(P_REGION=>?, P_RESULTSET=>?); END;
 bind => [null, => P_RESULTSET]
|#]

 

so I have done new changes in the code and it works fine.

 

public List<RecordDetail> getCountriesSP(BigDecimal region) throws Exception {

 

     EntityManagerFactory emf = Persistence.createEntityManagerFactory("OracleStoredProcedureCalling-jpa");
     EntityManager em = emf.createEntityManager();
     
     List<RecordDetail> list = null;
     try {
      
      JpaEntityManager jpaEntityManager = JpaHelper.getEntityManager(em);
      Session session = jpaEntityManager.getActiveSession();
      StoredProcedureCall call = new StoredProcedureCall();
      call.setProcedureName("HR.RECORD_SET.COUNTRIES_LIST");
      call.addNamedArgumentValue("P_REGION", region);
      call.useNamedCursorOutputAsResultSet("P_RESULTSET");
      
      /*
      DataReadQuery query = new DataReadQuery();
      query.setCall(call);
      query.addArgument("P_REGION");
 
      List<BigDecimal> queryArgs = new ArrayList<BigDecimal>();
      queryArgs.add(region);
      */
      
      List results = (List) session.executeSelectingCall(call);
      
      list = new ArrayList<RecordDetail>();
      for(int i=0; i<results.size(); i++){
       DatabaseRecord record = (DatabaseRecord) results.get(i);
       String name = (String)record.get("COUNTRY_NAME");
       String id = (String)record.get("COUNTRY_ID");
       RecordDetail rd = new RecordDetail(name, id);
       list.add(rd); 
      }
      
     } catch (Exception ex) {
      throw ex;
     }

 

     finally {
      em.close();
     }
     
     return list;
    }

 

Kind regards,

Jose

From: Jose Alvarez de Lara

Sent: Thursday, March 24, 2011 9:07 AM

To: EclipseLink-Users

Subject: [eclipselink-users] Exception calling an Oracle Stored Procedure

Hi,

 

I have done some changes in the code. It leaves as follows,

 

...the specification

CREATE OR REPLACE PACKAGE RECORD_SET
AS
type cursorType is ref cursor;
Procedure COUNTRIES_LIST(p_region IN NUMBER, p_ResultSet out cursorType);
END RECORD_SET ;

...and the body

CREATE OR REPLACE PACKAGE BODY RECORD_SET ISPROCEDURE COUNTRIES_LIST
(
p_region IN NUMBER,
p_ResultSet out cursorType
) IS

BEGIN

OPEN p_ResultSet FOR SELECT country_name, country_id, region_id
FROM countries
WHERE region_id = p_region;

EXCEPTION

When NO_DATA_FOUND then
dbms_output.put_line('No hay datos');

END;

END RECORD_SET ;

And the java code,

 public List<RecordDetail> getCountriesSP(BigDecimal region) throws Exception {

     EntityManagerFactory emf = Persistence.createEntityManagerFactory("OracleStoredProcedureCalling-jpa");
     EntityManager em = emf.createEntityManager();
     
     List<RecordDetail> list = null;
     try {
      
      JpaEntityManager jpaEntityManager = JpaHelper.getEntityManager(em);
      Session session = jpaEntityManager.getActiveSession();
      StoredProcedureCall call = new StoredProcedureCall();
      call.setProcedureName("HR.RECORD_SET.COUNTRIES_LIST");
      call.addNamedArgument("P_REGION", "REGION_ID", BigDecimal.class);
      call.useNamedCursorOutputAsResultSet("P_RESULTSET");
      
      DataReadQuery query = new DataReadQuery();
      query.setCall(call);
      query.addArgument("P_REGION");
 
      List queryArgs = new ArrayList();
      queryArgs.add(region);
     
      List results = (List) session.executeQuery(query, queryArgs);
      
      list = new ArrayList<RecordDetail>();
      for(int i=0; i<results.size(); i++){
       DatabaseRecord record = (DatabaseRecord) results.get(i);
       String name = (String)record.get("country_name");
       String id = (String)record.get("country_id");
       RecordDetail rd = new RecordDetail(name, id);
       list.add(rd); 
      }
      return list;
                 
     } catch (Exception ex) {
      throw ex;
     }

     finally {
      em.close();
     }
    }

But it returns an empty List, and the database table in the schema HR has many records.

It is possible that it be a question of privileges?, the tool I use to manage the database does not let me
query the table.

Thanks.

 

---

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users