Issues with ToracleSP and stored procedure who return a dataset

[Anonymous]

Hi,

I've got an error message using the ToracleSP component with a proc who return a DataSet,

below the error message

Exception in component tOracleSP_1
java.sql.SQLException: ORA-06550: Ligne 1, colonne 7 :
PLS-00306: numéro ou types d'arguments erronés dans appel à 'UDX_WEB_SELECT'
ORA-06550: Ligne 1, colonne 7 :
PL/SQL: Statement ignored

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:210)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:53)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5617)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
at wms.getcarrierorders_0_1.getCarrierOrders.tRESTRequest_1_LoopProcess(getCarrierOrders.java:3755)
at wms.getcarrierorders_0_1.getCarrierOrders$RestServiceProviderImpl4TalendJob.processRequest(getCarrierOrders.java:408)
at wms.getcarrierorders_0_1.getCarrierOrders$RestServiceProviderImpl4TalendJob.carrierinfo(getCarrierOrders.java:560)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:189)
at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:99)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:254)
at org.apache.cxf.transport.http_jetty.JettyHTTPDestination.doService(JettyHTTPDestination.java:234)
at org.apache.cxf.transport.http_jetty.JettyHTTPHandler.handle(JettyHTTPHandler.java:70)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1088)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1024)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:255)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
at org.eclipse.jetty.server.Server.handle(Server.java:370)
at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:494)
at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:971)
at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:1033)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:644)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:667)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
at java.lang.Thread.run(Unknown Source)

the parameters are Integer or String, i've checked many times the input parameters and i've found nothing.

here the parameters from the Oracle Proc

PURCHASECODE IN INTEGER
,DATECREATEFROM IN VARCHAR2
,DATECREATETO IN VARCHAR2
,SHIPFROMREF IN VARCHAR2
,SHIPFROMCODE IN VARCHAR2
,SHIPFROMDATEFROM IN VARCHAR2
,SHIPFROMDATETO IN VARCHAR2
,SHIPTOCODE IN VARCHAR2
,DELIVERYNR IN INTEGER
,SHIPTODATEFROM IN VARCHAR2
,SHIPTODATETO IN VARCHAR2
,ORDERNR IN INTEGER
,INVOICEREF IN VARCHAR2

and here the schema for the component tOracleSP

Can someone provide some help for this Issue ?

Thx

David

[lojdr · Accepted solution]

Hello,

You approach is a little bit weird. If you want return results from the Oracle procedure, you should use OUTPUT parameter, not DBMS_SQL package, or you can define PL/SQL function where you can define return value of the function.

https://www.techonthenet.com/oracle/functions.php

https://www.techonthenet.com/oracle/procedures.php

Please see this example how to process function results in Talend Job.

https://help.talend.com/reader/g8zdjVE7fWNUh3u4ztO6Dw/aR365pHJiutijy80OV11Ew

Any other discussions how to process refcursor:

https://community.talend.com/t5/Design-and-Development/resolved-How-to-get-a-Oracle-REF-CURSOR-into-...

https://community.talend.com/t5/Design-and-Development/resolved-use-of-tsybasesp/m-p/106446

Hope this helps.

Regards

Lojdr

[Anonymous]

good it's work with a procedure and an out parameter.

thanx a lot for your help

[lojdr]

You are welcome.