Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall  () 1 Vote
How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall [message #760280] Thu, 01 December 2011 18:20 Go to next message
GA  is currently offline 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 )
Re: How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall [message #760999 is a reply to message #760280] Mon, 05 December 2011 19:02 Go to previous messageGo to next message
GA  is currently offline GA
Messages: 6
Registered: December 2011
Junior Member
All
I figured out the problem. I was using EclipseLink 2.3.1 that was not compatible with my version of JDBC. When I used EclipsleLink 2.1.3 it worked.

Thanks
GA
Re: How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall [message #761003 is a reply to message #760280] Mon, 05 December 2011 19:02 Go to previous messageGo to next message
GA  is currently offline GA
Messages: 6
Registered: December 2011
Junior Member
All
I figured out the problem. I was using EclipseLink 2.3.1 that was not compatible with my version of JDBC. When I used EclipsleLink 2.1.3 it worked.

Thanks
GA
Re: How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall [message #761004 is a reply to message #760280] Mon, 05 December 2011 19:04 Go to previous message
GA  is currently offline GA
Messages: 6
Registered: December 2011
Junior Member
All
I figured out the problem. I was using EclipseLink 2.3.1 that was not compatible with my version of JDBC. When I used EclipsleLink 2.1.3 it worked.

Thanks
GA
Previous Topic:How to get a ref cursor/resultset from a StoredFunctionCall or StoredProcedureCall
Next Topic:How to get a list of active CacheCoordination members?
Goto Forum:
  


Current Time: Tue Sep 16 03:11:04 GMT 2014

Powered by FUDForum. Page generated in 0.03362 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software