My Oracle Support Banner

'7000 : null : com.sunopsis.jdbc.driver.file.d.i' Error When Unloading Clob Data To A File In ODI (Doc ID 1285274.1)

Last updated on JUNE 16, 2022

Applies to:

Oracle Data Integrator - Version 10.1.3.6.0 and later
Information in this document applies to any platform.

Symptoms

The error below occurs when trying to unload an Oracle table containing CLOB data to a file using IKM SQL To File Append:

7000 : null : com.sunopsis.jdbc.driver.file.d.i com.sunopsis.jdbc.driver.file.d.i
at com.sunopsis.jdbc.driver.file.d.d.setCharacterStream(d.java)
at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
at com.sunopsis.sql.SnpsQuery.addBatch(SnpsQuery.java)


The above error can be reproduced with this test case on ODI 10.1.3.6.4 or even later version of ODI 10.1.3.6 for example ODI 10.1.3.6.8:

On Oracle:

1. create table <SOURCE_SCHEMA>.clobdatatbl (clobcol1 CLOB)

2. SQL> insert into <SOURCE_SCHEMA>.clobdatatbl values('AZERTY QWERTY');

1 row created.

SQL> commit
2 ;

Commit complete.

In ODI:

3. Reverse Engineer the above Oracle table clobdatatbl in.

4. Duplicate clobdatatbl Datastore and drag the Duplicate under a File Model

5. Edit the Duplicate, under Files tab, File Format: Fixed, Heading: 1,
Record Separator MS-DOS
Columns tab, the column automatically become String with Physical and Logical
length both 4000

Note:

The physical and logical length of this column better be defiend according to the length of the source column data, for example it can be 35967.

Otherwise, java.lang.IllegalArgumentException: ODI-40406: Bytes are too big for array error occurs when the File Datastore column size is not defined long enough.

Please see <Note 554792.1> for details.

6. Import in IKM SQL to File Append

7. Create an Interface
Source: clobdatatbl from the Oracle Model
Target: duplicate of clobdatatbl from the File Model
Staging area set on Source Oracle
Mapping, CLOBCOL1 to CLOBCOL1 executed on source
Flow tab: no LKM needed only IKM SQL to File Append
set all 3 options to Yes Insert, Truncate, Generate_Header

8. Run the Interface, only 3 steps Truncate Target file, insert
column/header, Insert new rows will appear in Operator.

Insert new rows fails with:

7000 : null : com.sunopsis.jdbc.driver.file.c.i
com.sunopsis.jdbc.driver.file.c.i
at com.sunopsis.jdbc.driver.file.c.d.setCharacterStream(d.java)
at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
at com.sunopsis.sql.SnpsQuery.addBatch(SnpsQuery.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrders(SnpSessTaskSql.java

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.