topic Re: [resolved] Calling Oracle Stored Procedure in Talend Studio

[resolved] Calling Oracle Stored Procedure

Anonymous — Fri, 15 Jun 2012 06:22:52 GMT

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

Re: [resolved] Calling Oracle Stored Procedure

Anonymous — Fri, 15 Jun 2012 06:29:17 GMT

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

Re: [resolved] Calling Oracle Stored Procedure

Anonymous — Fri, 15 Jun 2012 07:50:52 GMT

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

Re: [resolved] Calling Oracle Stored Procedure

Anonymous — Fri, 15 Jun 2012 08:05:41 GMT

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

Re: [resolved] Calling Oracle Stored Procedure

Anonymous — Fri, 15 Jun 2012 10:52:33 GMT

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

Re: [resolved] Calling Oracle Stored Procedure

Anonymous — Fri, 15 Jun 2012 13:08:52 GMT

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