Pages: 1

Post reply

  • Index
  •  » Talend Enterprise Data Integration » General questions
  •  » [resolved] Calling Oracle Stored Procedure

#1 2012-06-15 07:22:52

arunshankar
Member
Registered: 2012-05-08
Posts: 32

[resolved] Calling Oracle Stored Procedure

Tags: [oracle]

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.

[statistics] connecting to socket on port 3597
[statistics] connected
[statistics] disconnected
Job Safaricom_requirement ended at 11:00 15/06/2012. [exit code=0]

Thanks in Advance.

Thanks & BR,
Arun


Uploaded Images

Last edited by arunshankar (2012-06-15 07:30:44)

Offline

#2 2012-06-15 07:29:17

pedro
Member
Registered: 2011-11-17
Posts: 3682

Re: [resolved] Calling Oracle Stored Procedure

Hi Arun

This topic may help you. [Forum, topic 7942] [resolved] use of tsybasesp
It's the same with tOracleSP.

Regards,
Pedro

Only Paranoid Survive.

Offline

#3 2012-06-15 08:50:52

arunshankar
Member
Registered: 2012-05-08
Posts: 32

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.

Code:

[statistics] connecting to socket on port 3686
[statistics] 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)
[statistics] disconnected

The SP is as below :

Code:

    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


Uploaded Images

Offline

#4 2012-06-15 09:05:41

pedro
Member
Registered: 2011-11-17
Posts: 3682

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

Only Paranoid Survive.

Offline

#5 2012-06-15 11:52:33

arunshankar
Member
Registered: 2012-05-08
Posts: 32

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 :

Code:

Starting job Safaricom_requirement at 15:13 15/06/2012.

[statistics] connecting to socket on port 3879
[statistics] 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)
[statistics] disconnected
Job Safaricom_requirement ended at 15:13 15/06/2012. [exit code=1]

Can you please advise.

Thanks
Arun


Uploaded Images

Offline

#6 2012-06-15 14:08:52

arunshankar
Member
Registered: 2012-05-08
Posts: 32

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

Offline

Post reply

Pages: 1

  • Index
  •  » Talend Enterprise Data Integration » General questions
  •  » [resolved] Calling Oracle Stored Procedure

Board footer

Powered by FluxBB