topic Re: Issues with ToracleSP and stored procedure who return a dataset in Talend Studio

Issues with ToracleSP and stored procedure who return a dataset

Anonymous — Sat, 16 Nov 2024 08:41:28 GMT

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

Re: Issues with ToracleSP and stored procedure who return a dataset

lojdr — Fri, 16 Feb 2018 17:39:45 GMT

Hello,

can you please post here definition of the SP from Oracle?

Regards
Lojdr

Re: Issues with ToracleSP and stored procedure who return a dataset

Anonymous — Mon, 19 Feb 2018 07:57:34 GMT

here, the SP

create or replace PROCEDURE UDX_WEB_SELECT
(
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
)
IS
RETURNDATAGRID SYS_REFCURSOR;

SQL_REQUEST VARCHAR2(2000);
SQL_PARAM VARCHAR2(2000);

BEGIN

if nvl(PURCHASECODE,0) != 0 then
SQL_PARAM := ' and "purchaseCode" = ' || PURCHASECODE;
end if;

if nvl(ORDERNR,0) != 0 then
SQL_PARAM := SQL_PARAM||' and "orderNr" = ' || ORDERNR;
end if;
if nvl(DELIVERYNR,0) != 0 then
SQL_PARAM := SQL_PARAM||' and "deliveryNr" = '||DELIVERYNR;
end if;
if nvl(SHIPFROMCODE,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM||' and "shipFromCode" = '''||SHIPFROMCODE||'''';
end if;
if nvl(SHIPTOCODE,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "shipToCode" = '''||SHIPTOCODE||'''';
end if;
if nvl(INVOICEREF,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "invoiceRef" = '''||INVOICEREF ||'''';
end if;
if nvl(SHIPFROMREF,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "shipFromRef" = ''' ||SHIPFROMREF||'''';
end if;

/* test dateCreate */
if nvl(DATECREATEFROM,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "dateCreate" >= ''' ||DATECREATEFROM ||'''';
end if;
if nvl(DATECREATETO,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "dateCreate" <= ''' || DATECREATETO||'''';
end if;
/* test shipFromDate */
if nvl(SHIPFROMDATEFROM,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "shipFromDate" >= ''' ||SHIPFROMDATEFROM ||'''';
end if;
if nvl(SHIPFROMDATETO,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "shipFromDate" <= ''' || SHIPFROMDATETO ||'''';
end if;
/* test shipToDate */
if nvl(SHIPTODATEFROM,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "shipToDate" >= ''' || SHIPTODATEFROM ||'''';
end if;
if nvl(SHIPTODATETO,'NC') != 'NC' then
SQL_PARAM := SQL_PARAM ||' and "shipToDate" <= ''' ||SHIPTODATETO ||'''';
end if;

SQL_REQUEST := 'select * from "UDX_WebView"';
SQL_REQUEST := SQL_REQUEST || ' where (1=1)';
SQL_REQUEST := SQL_REQUEST || SQL_PARAM ;

OPEN RETURNDATAGRID FOR SQL_REQUEST;
dbms_sql.return_result(RETURNDATAGRID);

END UDX_WEB_SELECT;

thx

Re: Issues with ToracleSP and stored procedure who return a dataset

manodwhb — Mon, 19 Feb 2018 09:13:35 GMT

are you able to execute same procedure from orcale not from Talend.

seems it has compilation errors.

Re: Issues with ToracleSP and stored procedure who return a dataset

Anonymous — Mon, 19 Feb 2018 09:38:08 GMT

i test it directly on PL/SQL Developper and it works fine

where do you see problems in the SP? i usually developp on SQL server so perhaps i made some mistakes with this SP on Oracle.

Is it perhaps the Sys_Refcursor that is not accepted by talend for a dataset result?

Re: Issues with ToracleSP and stored procedure who return a dataset

manodwhb — Mon, 19 Feb 2018 09:40:58 GMT

As per "ORA-06550: Ligne 1, colonne 7 :", there are compilation errors.

Re: Issues with ToracleSP and stored procedure who return a dataset

manodwhb — Mon, 19 Feb 2018 09:44:18 GMT

can you try to excute in Oralce DB.there you will get the errors,that is problem with pre-defined function will be vary from one database to othre database.

Re: Issues with ToracleSP and stored procedure who return a dataset

Anonymous — Mon, 19 Feb 2018 10:07:20 GMT

Sorry i'm new to Oracle , with which program do you want me to test the SP? i've got no access to the server and to the Oracle DB install.

Re: Issues with ToracleSP and stored procedure who return a dataset

manodwhb — Mon, 19 Feb 2018 10:10:19 GMT

even i do not have oracle in my system to test procedure for you.

Re: Issues with ToracleSP and stored procedure who return a dataset

Anonymous — Mon, 19 Feb 2018 10:34:13 GMT

In PL/SQL developper, i execute the SP like this :

begin
UDX_WEB_SELECT(9999,'NC','NC','NC','NC','NC','NC','NC',0,'NC','NC',0,'NC');
end;

and i've got this result

Procédure PL/SQL terminée.

ResultSet #1

aucune ligne sélectionnée

Re: Issues with ToracleSP and stored procedure who return a dataset

lojdr — Tue, 20 Feb 2018 13:40:13 GMT

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-Talend/m-p/109527

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

Hope this helps.

Regards

Lojdr

Re: Issues with ToracleSP and stored procedure who return a dataset

Anonymous — Wed, 21 Feb 2018 10:45:35 GMT

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

thanx a lot for your help

Re: Issues with ToracleSP and stored procedure who return a dataset

lojdr — Wed, 21 Feb 2018 13:47:53 GMT

You are welcome.