I have an issue using BODI to load in to CLOB type columns. The error is ‘ZERO INTERATION COUNT’ see https://bobj-board.org/t/163112
In order to get around this problem I set the bulk load options for the target table to FILE to make BODI use SQL Loader.
When BODI generates the SQL Loader files, if the target table contains a long (CLOB) the control file generated defines these columns incorrectly.
Test Environment
OS: HP-UX
DS: 3.2
ORA: 11.2
TABLE
NAME Varchar2(4000),
DESCRIPTION clob
SOURCE DATE (CSV File)
Rod,This is test data This is test data This is test data This is test data
Jane,This is test data This is test data
Freddy,This is test data This is test data This is test data This is test data This is test data This is test data This is test data This is test data This is test data This is test data This is test data This is test data This is test data
When BODI generates the control file used by SQL Loader the NAME column (vchar2:4000) is defined as CHAR 8000 but the DESCRIPTION column (clob) is defined as CHAR 30.
SQL Loader then obviously throws out the last row as the DESCRIPTION data is larger than char 30.
I can not understand why the control file is being generated with this kind of constraint. Clearly if I change the control file for the CLOB columns to CHAR (2000000) all the data loads successfully.
Has anyone else come across this problem? Can anyone tell me how BODI comes up with the length of the CLOB column for the control file?
Any help is greatly appreciated.
Thanks in advance.
carlgb (BOB member since 2010-11-02)