topic Re: Pass array to Oracle SP in Talend Studio

Pass array to Oracle SP

Anonymous — Fri, 20 Jul 2012 09:24:20 GMT

Hi all,
Can someone please guide me on the way to pass an array to tOracleSP i.e. to a Stored Procedure which takes array as the input.
Thanks,
Arun

Re: Pass array to Oracle SP

Anonymous — Fri, 20 Jul 2012 10:02:37 GMT

Hi
You might try to create a job as the following images.
Regards,
Pedro

Re: Pass array to Oracle SP

Anonymous — Fri, 20 Jul 2012 13:05:04 GMT

Hi Pedro,
Thank you so much for your reply, now I think I tFixedFlow is passing the Array to Oracle SP.
But, am getting the below error in tOracleSP component :
Exception in component tOracleSP_1
java.sql.SQLException: Invalid column type
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.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7937)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7517)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8174)
at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4094)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
disconnected
at arun.mtn_0_1.MTN.tFixedFlowInput_1Process(MTN.java:551)
at arun.mtn_0_1.MTN.tJava_1Process(MTN.java:341)
at arun.mtn_0_1.MTN.runJobInTOS(MTN.java:784)
at arun.mtn_0_1.MTN.main(MTN.java:652)
Am using a User Defined TYPE "B_VARCHAR2" which is VARRAY(10000) OF VARCHAR2(255);
Can you Please suggest the Data Type to be used for the input Variable in Stored Procedure in the package. Please suggest if I need to use some other Data type in the Input for Oracle SP.
Sorry for asking too many questions.
Can you please suggest.
Thank you,
Arun

Re: Pass array to Oracle SP

Anonymous — Mon, 23 Jul 2012 03:19:32 GMT

Hi
The data type used for the input variable should be Object.
Regards,
Pedro

Re: Pass array to Oracle SP

Anonymous — Mon, 23 Jul 2012 07:37:08 GMT

Hi Pedro,
Please see my PLSQL procedure :

CREATE OR REPLACE
TYPE "B_VARCHAR2" AS VARRAY(10000) OF VARCHAR2(255);
/
CREATE OR REPLACE PACKAGE return_output_talend as
        TYPE VARCHAR_LIST IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
        PROCEDURE return_output (p_input_iccid_array IN B_VARCHAR2);
END return_output_talend;
/
SHOW ERRORS;
CREATE OR REPLACE PACKAGE BODY return_output_talend AS
        PROCEDURE return_output (p_input_iccid_array IN B_VARCHAR2)
        AS
                va_iccid VARCHAR_LIST;
        BEGIN
                BEGIN
                        FOR idx IN p_input_iccid_array.first..p_input_iccid_array.last LOOP
                                INSERT INTO TEMP_TAb1 VALUES (p_input_iccid_array(idx));
                        END LOOP;
                        COMMIT;
                EXCEPTION
                        WHEN OTHERS THEN
                                DBMS_OUTPUT.PUT_LINE('Exception occured');
                END;
        END return_output;
END return_output_talend;
/
SHOW ERRORS;
EXIT

In above code, for the IN Parameter in PLSQL code, am using User defined data type B_VARCHAR2.
Can you please let me know if above Parameters are correct in PLSQL part, because still am getting the same error as I described in my above post. I have setup the job as you advised.
Thanks,
Arun

Re: Pass array to Oracle SP

Anonymous — Mon, 23 Jul 2012 07:38:52 GMT

Hi Arun
Please report it on BugTracker.
Regards,
Pedro

Re: Pass array to Oracle SP

Anonymous — Mon, 23 Jul 2012 08:39:06 GMT

Hi Pedro,
Thank you. I have opened a bug.
http://jira.talendforge.org/browse/TDI-22045
Thanks,
Arun

Re: Pass array to Oracle SP

Anonymous — Mon, 29 Jul 2013 23:12:53 GMT

Hi All,
Is there version in which this problem is fixed?
I'm using 5.1.1 and it still fails with "Invalid column type".
On Ora side I have type:
liststringtype is table of varchar2(256);
procedure test(strings liststringtype) as ....
On TOS I have:
tJava:
String[] s = new String;
s = "Test string 1";
s = "Test string 2";
globalMap.put("strings", s);
tFixedFlowInput with 1 field "strings" in schema of type object
rOracleSP
with 1 field "strings" in schema of type object and 1 parameter"strings" DBType = "AUTO-MAPPING" Custom Type = "ARRAY" Custom Name = "LISTSTRINGTYPE"
If I look into java code generated:
if (row1.strings == null) {
statement_tOracleSP_2.setNull(1, java.sql.Types.ARRAY,
"LISTSTRINGTYPE");
} else {
statement_tOracleSP_2.setObject(1, row1.strings);
}
statement_tOracleSP_2.execute();

So, if I pass null parameter - it works. If array is assigned - it returns invalid column type.