[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Exception calling an Oracle Stored Procedure
|
To use a result cursor as the result set for a procedure use,
call.useNamedCursorOutputAsResultSet("P_RESULTSET");
instead of,
call.addNamedOutputArgument("P_RESULTSET", "TYPECURSOR");
The error that you are getting is because the type is wrong for the output
parameter as you did not specify a type. addNamedOutputArgument, also can
take either a Java class type, or a JDBC type. I believe the JDBC type for
cursor on Oracle is -10, but just use useNamedCursorOutputAsResultSet() and
it will return the cursor as the query result.
Oggie wrote:
>
> Hi,
>
> I am trying a simple project that returns REF CURSOR from an Oracle Stored
> Procedure.
>
> Here is the SP,
>
> ...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 FORSELECT country_name, country_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 here is the code snippet of the EJB 3.0 method,
>
> public List<RecordDetail> getCountriesSP(BigDecimal region) throws
> Exception {
>
> List<RecordDetail> list = null;
> EntityManagerFactory emf =
> Persistence.createEntityManagerFactory("OracleStoredProcedureCalling-jpa");
> EntityManager em = emf.createEntityManager();
>
> 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");
> call.addNamedOutputArgument("P_RESULTSET", "TYPECURSOR");
>
> DataReadQuery query = new DataReadQuery();
> query.setCall(call);
> query.addArgument("REGION_ID");
>
> 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);
> }
>
> } catch (Exception ex) {
> throw new Exception(ex);
> }
>
> finally {
> em.close();
> }
>
> return list;
> }
>
> I get the following exception,
>
> java.lang.Exception: Exception [EclipseLink-4002] (Eclipse Persistence
> Services - 2.0.1.v20100213-r6600):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLException: ORA-06550: línea 1, columna 7:
> PLS-00306: wrong number or types of arguments when calling to
> 'COUNTRIES_LIST'
> ORA-06550: línea 1, columna 7:
> PL/SQL: Statement ignored
>
> Error Code: 6550
>
> Call: BEGIN HR.RECORD_SET.COUNTRIES_LIST(P_REGION=>?, P_RESULTSET=>?);
> END;
> bind => [2, => TYPECURSOR]
> Query: DataReadQuery()
>
> at ejb.MyStoredProcedureCall.getCountriesSP(MyStoredProcedureCall.java:67)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at
> org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1056)
> at
> org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1128)
> at
> com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:5292)
> at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:615)
> at
> com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
> at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
> at
> com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doAround(SystemInterceptorProxy.java:157)
> at
> com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:139)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at
> com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
> at
> com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
> at
> com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:367)
> at
> com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5264)
> at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5252)
> at
> com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:201)
> at
> com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:75)
> at $Proxy297.getCountriesSP(Unknown Source)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at
> com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie.dispatchToMethod(ReflectiveTie.java:146)
> at
> com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:176)
> at
> com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:682)
> at
> com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:216)
> at
> com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1841)
> at
> com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1695)
> at
> com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:1078)
> at
> com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:221)
> at
> com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:797)
> at
> com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.dispatch(CorbaMessageMediatorImpl.java:561)
> at
> com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.doWork(CorbaMessageMediatorImpl.java:2558)
> at
> com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.performWork(ThreadPoolImpl.java:492)
> at
> com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:528)
>
> Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services -
> 2.0.1.v20100213-r6600):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLException: ORA-06550: línea 1, columna 7:
> PLS-00306: wrong number or types of arguments when calling to
> 'COUNTRIES_LIST'
> ORA-06550: línea 1, columna 7:
> PL/SQL: Statement ignored
>
> Error Code: 6550
>
> at
> org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:801)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.executeStoredProcedure(DatabasePlatform.java:1942)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:597)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:530)
> at
> org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
> at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:244)
> at
> org.eclipse.persistence.queries.DataReadQuery.executeNonCursor(DataReadQuery.java:188)
> at
> org.eclipse.persistence.queries.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:144)
> at
> org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
> at
> org.eclipse.persistence.queries.DataReadQuery.execute(DataReadQuery.java:130)
> at
> org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:589)
> at
> org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2857)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
> at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
> at ejb.MyStoredProcedureCall.getCountriesSP(MyStoredProcedureCall.java:55)
> ... 41 more
>
> Caused by: java.sql.SQLException: ORA-06550: línea 1, columna 7:
> PLS-00306: wrong number or types of arguments when calling to
> 'COUNTRIES_LIST'
> ORA-06550: línea 1, columna 7:
> PL/SQL: Statement ignored
>
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
> at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:672)
> at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:598)
> at
> oracle.jdbc.driver.T2CCallableStatement.executeForDescribe(T2CCallableStatement.java:572)
> at
> oracle.jdbc.driver.T2CCallableStatement.executeForRows(T2CCallableStatement.java:765)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
> at
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
> at
> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
> at
> oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4245)
> at
> com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:108)
> at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:792)
> ... 59 more
>
> And I guess the question is how to pass the REFCURSOR argument.
>
> Thanks in advance,
> Jose
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
Blog: http://java-persistence-performance.blogspot.com/ Java Persistence
Performance
--
View this message in context: http://old.nabble.com/Exception-calling-an-Oracle-Stored-Procedure-tp31203429p31231312.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
