topic Re: [resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle in Talend Studio

[resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle

soowork — Sat, 16 Nov 2024 11:25:56 GMT

We have a Talend job which has been running happily nightly for 6 months. This job takes data from a Vertica database and loads it into an Oracle table (does straight Inserts).
We are using Enterprise Big Data 5.4.1.r.111943 and Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
The error was -

Exception in component tOracleOutput_1
java.lang.ArrayIndexOutOfBoundsException: -32043
	at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:2677)
	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9270)
	at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:210)
	at pdwserver.j_lcp_dmart_pbssummary_refund_0_1.j_LCP_DMART_PBSSUMMARY_REFUND.tVerticaInput_2Process(j_LCP_DMART_PBSSUMMARY_REFUND.java:23969)
	at pdwserver.j_lcp_dmart_pbssummary_refund_0_1.j_LCP_DMART_PBSSUMMARY_REFUND.runJobInTOS(j_LCP_DMART_PBSSUMMARY_REFUND.java:27036)
	at pdwserver.j_lcp_dmart_pbssummary_refund_0_1.j_LCP_DMART_PBSSUMMARY_REFUND.runJob(j_LCP_DMART_PBSSUMMARY_REFUND.java:26878)
	at pdwserver.s_lcp_dmart_pbssummary_0_1.s_LCP_DMART_PBSSUMMARY.tRunJob_2Process(s_LCP_DMART_PBSSUMMARY.java:2746)
	at pdwserver.s_lcp_dmart_pbssummary_0_1.s_LCP_DMART_PBSSUMMARY.tRunJob_6Process(s_LCP_DMART_PBSSUMMARY.java:2564)
	at pdwserver.s_lcp_dmart_pbssummary_0_1.s_LCP_DMART_PBSSUMMARY.tRunJob_1Process(s_LCP_DMART_PBSSUMMARY.java:2298)
	at pdwserver.s_lcp_dmart_pbssummary_0_1.s_LCP_DMART_PBSSUMMARY.tRunJob_8Process(s_LCP_DMART_PBSSUMMARY.java:1730)
	at pdwserver.s_lcp_dmart_pbssummary_0_1.s_LCP_DMART_PBSSUMMARY.runJobInTOS(s_LCP_DMART_PBSSUMMARY.java:3056)
	at pdwserver.s_lcp_dmart_pbssummary_0_1.s_LCP_DMART_PBSSUMMARY.main(s_LCP_DMART_PBSSUMMARY.java:2892)

The "commit every" setting was 1000. Last night was the first time that we were writing more than 1000 rows.
I read other comments in the forum that suggested people got this kind of error when they wrote their second chunk of data - and this seemed to fit our case (as we were just > 1000 records for the first time). We tested this theory by bumping the commit level to 1200 and running the job again. In this mode, the job was successful.
What is causing this error to occur? Obviously we are lucky in this job because we are writing such small amounts of data - but we don't want to just keep changing the commit level to stay greater than our data volume. And we have other jobs that also write to Oracle that have data volumes that exceed the commit level. So what is causing this one to have trouble - any ideas?
TIA

Re: [resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle

Anonymous — Tue, 11 Nov 2014 07:24:28 GMT

Hi,
This issue is probably limited to some particular job. Would you mind uploading your job design screenshots into forum?
Best regards
Sabrina

Re: [resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle

soowork — Tue, 11 Nov 2014 14:10:48 GMT

Thanks Sabrina. Attached please see the job flow. The table that is being loaded (PBS_TRANSACTION_TMP) is truncated in a previous step.

Re: [resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle

soowork — Wed, 19 Nov 2014 03:07:58 GMT

Hi Sabrina, do you have any ideas for me? TIA

Re: [resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle

Anonymous — Wed, 19 Nov 2014 07:14:19 GMT

Hi,
We don't find any error in your job design.
For your issue, could you please take a look at: http://stackoverflow.com/questions/24400006/talend-etl-job-error-in-toracleoutput-component?
Best regards
Sabrina

Re: [resolved] ArrayIndexOutOfBoundsException error when writing data to Oracle

Anonymous — Wed, 19 Nov 2014 08:35:20 GMT

hi,
Are you using oracle driver ojdbc6 ?
I guess it comme from a bug with that driver :
https://jira.talendforge.org/browse/TDI-30221
try & update your driver used by output oracle component.
some ways to do that ... one of them is to change the driver called :

<IMPORT NAME="ORACLE_11" REQUIRED_IF="(DB_VERSION == 'ORACLE_11') AND (USE_EXISTING_CONNECTION == 'false') AND (SPECIFY_DATASOURCE_ALIAS == 'false')" UrlPath="platform:/plugin/org.talend.libraries.jdbc.oracle/lib/ojdbc6.jar" MODULE="ojdbc6.jar" BundleID=""/>

remove o"jdbc6.jar" by another one (of course it have to be in your library).

hope it helps
regards
laurent