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.
