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.
