[eclipselink-users] Exception calling an Oracle Stored Procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]

[eclipselink-users] Exception calling an Oracle Stored Procedure

From: "Jose Alvarez de Lara" <dakhla.0563@xxxxxxxxxxx>
Date: Mon, 21 Mar 2011 19:29:48 +0100
Delivered-to: eclipselink-users@xxxxxxxxxxx
Importance: Normal
List-archive: <https://dev.eclipse.org/mailman/private/eclipselink-users>
List-help: <mailto:eclipselink-users-request@eclipse.org?subject=help>
List-subscribe: <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>, <mailto:eclipselink-users-request@eclipse.org?subject=subscribe>
List-unsubscribe: <https://dev.eclipse.org/mailman/options/eclipselink-users>, <mailto:eclipselink-users-request@eclipse.org?subject=unsubscribe>

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

Follow-Ups:
- Re: [eclipselink-users] Exception calling an Oracle Stored Procedure
  - From: James Sutherland

Prev by Date: [eclipselink-users] Auto Reply: Auto Reply: Lazy loading scenario
Next by Date: [eclipselink-users] Auto Reply: Exception calling an Oracle Stored Procedure
Previous by thread: [eclipselink-users] Auto Reply: Auto Reply: Lazy loading scenario
Next by thread: Re: [eclipselink-users] Exception calling an Oracle Stored Procedure
Index(es):
- Date
- Thread

Breadcrumbs