Issues with ToracleSP and stored procedure who return a dataset

Five Stars

Issues with ToracleSP and stored procedure who return a dataset

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

ToracleSP_Schema.jpg

Can someone provide some help for this Issue ?

 

Thx

David


Accepted Solutions
Eight Stars

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

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


All Replies
Eight Stars

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

Hello,

 

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

 

Regards
Lojdr

Five Stars

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

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

Forteen Stars

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

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

 

seems it has compilation errors.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

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

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?

 

 

Forteen Stars

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

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

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

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.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Five Stars

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

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.

Forteen Stars

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

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

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

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

 

Eight Stars

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

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

Five Stars

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

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

thanx a lot for your help

Eight Stars

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

You are welcome.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read