Home » Eclipse Projects » EclipseLink » How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall(StoredFunctionCall/StoredProcedureCall with ref cursor)
() 1 Vote
How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall [message #760278] |
Thu, 01 December 2011 18:20 |
GA Messages: 6 Registered: December 2011 |
Junior Member |
|
|
Hi All
I am trying to write a StoredFunctionCall that fetches a ref cursor from the DB. I was unsuccessful as I got a wrong type error. Then I converted stored function into a procedure and used StoredProcedureCall. This time I got "wrong number or types of arguments in call". So can anybody please post a working example of either function. Details as below. I am using the oracle thin driver with Oracle 11.2 database. I have been as verbose as possible.
I have listed 1. The Stored Procedure Exercise and 2. The Stored Function Exercise.
1. The Stored Procedure Exercise
/***Stored Procedure part is***/
CREATE OR REPLACE package DiscoveryGUIPkg as
TYPE cursortype IS REF CURSOR;
end;
create or replace procedure getDiscoveryRowsProc
(v_name IN varchar2,
v_location IN varchar2,
vCursor OUT DiscoveryGUIPkg.cursortype
)
as
search_criteria varchar2(20000);
sqlStmt varchar2(32000);
declare
p_name varchar2(100);
p_location varchar2(100);
begin
/* all parameters will be passed in as a single space or with wildcard '*' */
/* replace space and * with % */
p_name := replace(replace(v_name, '*', '%'), ' ', '%');
p_location := replace(replace(v_location, '*', '%'), ' ', '%');
/* Prepare the search criteria to be used in the where clause.
** Put nvl() in the 2nd condition, hoping it would use the index
** on the 1st condition without the nvl().
*/
search_criteria :=
' where (name like ''' || p_name ||
''' or nvl(name, '' '') like ''' || p_name || ''')' ||
' and (tid like ''' || p_location ||
''' or nvl(tid, '' '') like ''' || p_location || ''')';
/* construct the sql statement */
sqlStmt := 'select * from discovery_template';
sqlStmt := sqlStmt || search_criteria;
delete from guru_debug;
insert into guru_debug values(sqlStmt);
commit;
open vCursor for sqlStmt;
end;
/*****Java code with EclipseLink JPA is******/
/**
* Find all DiscoveryTemplate entities using stored function getDiscoveryRowsProc.
*
* @return List<DiscoveryTemplate> all DiscoveryTemplate entities
*/
@SuppressWarnings("unchecked")
public List<DiscoveryTemplate> findAllByProcedure(String templateName, String tid) {
EntityManagerHelper.log("finding all DiscoveryTemplate instances",
Level.INFO, null);
try {
StoredProcedureCall functionCall = new StoredProcedureCall();
functionCall.setProcedureName("GETDISCOVERYROWSPROC");
functionCall.addNamedArgument("V_NAME");
functionCall.addNamedArgument("V_LOCATION");
functionCall.useNamedCursorOutputAsResultSet("V_CURSOR");
UnitOfWorkImpl uowl = (UnitOfWorkImpl)((EntityManagerImpl)getEntityManager().getDelegate()).getSession();
UnitOfWork uow = uowl.acquireUnitOfWork();
uow.beginEarlyTransaction();
DataReadQuery query = new DataReadQuery();
query.setCall(functionCall);
query.addArgument("V_NAME");
query.addArgument("V_LOCATION");
List<String> args= new ArrayList<String>();
args.add(templateName);
args.add(tid);
Object objres = uow.executeQuery(query, args);
List<DiscoveryTemplate> result = (List<DiscoveryTemplate>)uow.executeQuery(query, args);
uow.commit();
uow.release();
return result;
} catch (RuntimeException re) {
EntityManagerHelper.log("find all failed", Level.SEVERE, re);
throw re;
}
}
/*******Output of runtime******/
[EL Warning]: 2011-12-01 13:08:14.568--UnitOfWork(10232210)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.1.v20111018-r10243): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETDISCOVERYROWSPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Error Code: 6550
Call: BEGIN GETDISCOVERYROWSPROC(V_NAME=>?, V_LOCATION=>?, V_CURSOR=>?); END;
bind => [3 parameters bound]
Query: DataReadQuery()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2. The Stored Function Exercise
/***Stored Function is***/
create or replace function getDiscoveryRows
(v_name IN OUT varchar2,
v_location IN OUT varchar2
)
return sys_refcursor
as
vCursor sys_refcursor;
search_criteria varchar2(20000);
sqlStmt varchar2(32000);
begin
/* all parameters will be passed in as a single space or with wildcard '*' */
/* replace space and * with % */
v_name := replace(replace(v_name, '*', '%'), ' ', '%');
v_location := replace(replace(v_location, '*', '%'), ' ', '%');
/* Prepare the search criteria to be used in the where clause.
** Put nvl() in the 2nd condition, hoping it would use the index
** on the 1st condition without the nvl().
*/
search_criteria :=
' where (name like ''' || v_name ||
''' or nvl(name, '' '') like ''' || v_name || ''')' ||
' and (tid like ''' || v_location ||
''' or nvl(tid, '' '') like ''' || v_location || ''')';
/* construct the sql statement */
sqlStmt := 'select * from discovery_template';
sqlStmt := sqlStmt || search_criteria;
delete from guru_debug;
insert into guru_debug values(sqlStmt);
commit;
open vCursor for sqlStmt;
return vCursor;
end;
/*****Java code with EclipseLink JPA is******/
/**
* Find all DiscoveryTemplate entities using stored function getDiscoveryRows.
*
* @return List<DiscoveryTemplate> all DiscoveryTemplate entities
*/
@SuppressWarnings("unchecked")
public List<DiscoveryTemplate> findAllByFunction(String templateName, String tid) {
EntityManagerHelper.log("finding all DiscoveryTemplate instances",
Level.INFO, null);
try {
StoredFunctionCall functionCall = new StoredFunctionCall();
functionCall.setProcedureName("GETDISCOVERYROWS");
functionCall.addNamedArgument("V_NAME");
functionCall.addNamedArgument("V_LOCATION");
functionCall.setResult("CURSOR_out");
UnitOfWorkImpl uowl = (UnitOfWorkImpl)((EntityManagerImpl)getEntityManager().getDelegate()).getSession();
UnitOfWork uow = uowl.acquireUnitOfWork();
uow.beginEarlyTransaction();
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(DiscoveryTemplate.class);
query.setCall(functionCall);
query.addArgument("V_NAME");
query.addArgument("V_LOCATION");
List<String> args= new ArrayList<String>();
args.add(templateName);
args.add(tid);
List<DiscoveryTemplate> result = (List<DiscoveryTemplate>)uow.executeQuery(query, args);
uow.commit();
uow.release();
return result;
} catch (RuntimeException re) {
EntityManagerHelper.log("find all failed", Level.SEVERE, re);
throw re;
}
}
/*******Output of runtime******/
[EL Warning]: 2011-11-30 16:29:05.822--UnitOfWork(28011548)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.1.v20111018-r10243): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Error Code: 6550
Call: BEGIN ? := GETDISCOVERYROWS(V_NAME=>?, V_LOCATION=>?); END;
bind => [3 parameters bound]
Query: ReadAllQuery(referenceClass=DiscoveryTemplate )
|
|
| |
Goto Forum:
Current Time: Tue Sep 24 07:38:10 GMT 2024
Powered by FUDForum. Page generated in 0.03076 seconds
|