[resolved] Calling Oracle Stored Procedure

Six Stars

[resolved] Calling Oracle Stored Procedure

Hi Talend Gurus,
I am calling Oracle Stored Procedure which does not require any input parameters. So, below is the setup :
tOracleSP_1 --> tLogRow_1 --> tFileOutputDelimited_1
The configuration and parameters used for tOracleSP_1 is as attached.
I am not getting any Errors, but output of SP (which is DBMS_OUTPUT) is not passed to tLogRow_1
In the SP Name for tOracleSP_1, I have used "ni_fetch_sfc_reports_sx.safaricom_reports_simex" and no configuration done for Parameters part (since I don't have any input/output parameters)
The output of the t_OracleSP_1 just shows 1 rows Fetched (Please see attached Screenshot)
Please let me know if there is something like Set Serveroutput on to be done for the DBMS_OUTPUT to be displayed.
The output in execution is as below :
Starting job Safaricom_requirement at 11:00 15/06/2012.
connecting to socket on port 3597
connected
disconnected
Job Safaricom_requirement ended at 11:00 15/06/2012.
Thanks in Advance.
Thanks & BR,
Arun

Accepted Solutions
Six Stars

Re: [resolved] Calling Oracle Stored Procedure

Hi Pedro,
Thank you for your help. The problem was with the Stored Procedure returning the Cursor. Issue Resolved.
Thanks a Bunch..
Thanks & BR,
Arun

All Replies
Highlighted
One Star

Re: [resolved] Calling Oracle Stored Procedure

Hi Arun
This topic may help you. 7942
It's the same with tOracleSP.
Regards,
Pedro
Six Stars

Re: [resolved] Calling Oracle Stored Procedure

Hi Pedro,
Thank you so much for your reply.. I tried the same implementation. But, am getting error as below :
Starting job Safaricom_requirement at 11:48 15/06/2012.
 connecting to socket on port 3686
connected
Exception in component tOracleSP_1
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ARUN_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3857)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at arun.safaricom_requirement_0_1.Safaricom_requirement.tOracleSP_1Process(Safaricom_requirement.java:555)
at arun.safaricom_requirement_0_1.Safaricom_requirement.runJobInTOS(Safaricom_requirement.java:905)
at arun.safaricom_requirement_0_1.Safaricom_requirement.main(Safaricom_requirement.java:773)
disconnected

The SP is as below :
	PROCEDURE arun_proc AS
va_dn VARCHAR_LIST;
va_curr_state VARCHAR_LIST;
BEGIN
BEGIN
SELECT dn,current_state BULK COLLECT INTO va_dn,va_curr_state
FROM ni_dn
WHERE rownum < 10;
IF va_dn.count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No DNs found');
GOTO the_end;
END IF;
FOR idx IN va_dn.first..va_dn.last LOOP
DBMS_OUTPUT.PUT_LINE('DN : '||va_dn(idx)||' Current_state : '||va_curr_state(idx));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM);
END;
<<the_end>>
NULL;
END arun_proc;
END call_arun;

I have done the same setup as shown in above example..
Please find the attached Screen shots.
Please let me know for any mistake.
Thanks & BR,
Arun
One Star

Re: [resolved] Calling Oracle Stored Procedure

Hi Arun
After reading the code of your Oracle PROCEDURE, I see two output parameters(va_dn,va_curr_state).
Just add two columns in tOracleSP and select 'OUT' type of 'Parameters' on tOracleSP.
Regards,
Pedro
Six Stars

Re: [resolved] Calling Oracle Stored Procedure

Hi Pedro,
Thank you so much for your help so far, but, I modified the t_Oracle_SP and ParseRecord such that the OUT Parameters are defined. But, I got the below error :
Starting job Safaricom_requirement at 15:13 15/06/2012.
connecting to socket on port 3879
connected
Exception in component tOracleSP_1
java.sql.SQLException: Invalid column type: 1111
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3521)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:126)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:288)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:379)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1568)
at arun.safaricom_requirement_0_1.Safaricom_requirement.tOracleSP_1Process(Safaricom_requirement.java:538)
at arun.safaricom_requirement_0_1.Safaricom_requirement.runJobInTOS(Safaricom_requirement.java:891)
at arun.safaricom_requirement_0_1.Safaricom_requirement.main(Safaricom_requirement.java:759)
disconnected
Job Safaricom_requirement ended at 15:13 15/06/2012.

Can you please advise.
Thanks
Arun
Six Stars

Re: [resolved] Calling Oracle Stored Procedure

Hi Pedro,
Thank you for your help. The problem was with the Stored Procedure returning the Cursor. Issue Resolved.
Thanks a Bunch..
Thanks & BR,
Arun

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download