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

1 ACCEPTED SOLUTION

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

12 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

Twelve 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
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?

 

 

Twelve 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
Twelve 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
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.

Twelve 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
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.