Issues with ToracleSP and stored procedure who return a dataset

[damiel]

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

[lojdr]

Hello,

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

Regards
Lojdr

[damiel]

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

[manodwhb]

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

seems it has compilation errors.

[damiel]

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?

[manodwhb]

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

[manodwhb]

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.

[damiel]

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.

[manodwhb]

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

[damiel]

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

[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

[damiel]

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

thanx a lot for your help

[lojdr]

You are welcome.