One Star

[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
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

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
5 REPLIES
One Star

Re: [resolved] Calling Oracle Stored Procedure

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

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
One Star

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
One Star

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