ORA-24333 Zero iteration count error with CLOBs

dataservices job design
1

Hi,

I am getting an error when loading data into Oracle. I am using two environments with different setups.

The first environment uses Business Objects Data Services XI 3.1. The job server operating system is Windows Server 2003. An Oracle 11g database sits on the job server.

The second environment uses Business Objects Data Services XI 3.2. The job server operating system is HP Unix, version B.11.31. An Oracle 11g database sits on the job server.

I have a simple job that loads data from a csv file into Oracle using a simple query transform. The flat file definition is:

Field_01 – long
Field_02 – int
Field_03 –- varchar 2
Field_04 –- varchar 35
Field_05 –- varchar 5
Field_06 – int
Field_07 – int

The Oracle table is defined as

Col_01 – Number(10,0), Primary Key
Col_02 – Number(5,0), Not Null
Col_03 – Date, Not Null
Col_04 – Clob
Col_05 – Varchar2(1)
Col_06 – Varchar2(2)
Col_07 – Number(5,0)
Col_08 – Varchar2(9)
Col_09 – Varchar2(35)
Col_10 – Varchar2(5)
Col_11 – Number(10,0)

The mappings are:

Col_01 <-- Null (populated by a trigger in Oracle)
Col_02 <-- Null (populated by a trigger in Oracle)
Col_03 <-- Null (populated by a trigger in Oracle)
Col_04 <-- Field_01
Col_05 <-- Null
Col_06 <-- Field_03
Col_07 <-- Field_02
Col_08 <-- Null
Col_09 <-- Field_04
Col_10 <-- Field_05
Col_11 <-- Field_06

The job runs successfully in the first environment but fails in the second environment, even with a csv file with 1 row of data. Unfortunately the second environment is the preferred one.

The error reported is “error message for operation : <ORA-24333: zero iteration count>”.

I think the error is related to the CLOB column on the oracle table. I have managed to load 16000 rows into an Oracle table without a CLOB column, but have been unsuccessful with any table containing a CLOB column.

Can anybody tell me why this error is being generated? Also, is there a parameter setting I can use to avoid the error?

Thanks
Adrian

adrian8765 :uk: (BOB member since 2010-08-04)

2

I have the same problem when use the function varchar_to_long in order to insert the data in Oracle table with data type long or clob, somebody known solve this error?

armandostk (BOB member since 2010-10-11)

3

I have the same the problem too with Long data type.Is there any resolution for this.

Any help/suggestions is greatly appreciated.

Thanks,
-Sri.

sriprameela :india: (BOB member since 2009-10-22)

4

I tried to reproduce the issue but its not happening in my env
can you provide the complete versions of following
jobserver OS
DataServices
Oracle Client on the jobServer machine and Oracle Database
Source type (file/xml/database) if database, type and version ?

manoj_d (BOB member since 2009-01-02)

5

Jobserver OS: Windows 7
DataServices 12.2
Oracle Client on the jobServer machine and Oracle Database: 11G
Source type (file/xml/database) if database, type and version:MySQl database

Thank you for looking into this.
-Sri.

sriprameela :india: (BOB member since 2009-10-22)

6

Have the exact same error.

This issue (in our env) appears to only happen with Oracle 11g R2 (11.2). Both DS 3.1 and DS 3.2 with Oracle 11g R2 (11.2) error. Both versions function correctly with Oracle 11g (11.1).

The error has been reproduced on both Windows and HP-UX.

Any help is appreciated.

Sri - Are you running 11.2 Oracle as well?

PS. Only way I got around this is to force the target table to use bulk loader type ‘FILE’ to force it to use SQL LOADER. This has another issue see https://bobj-board.org/t/165950/1

carlgb (BOB member since 2010-11-02)

7

I’m getting the same error, using a varchar_to_long writing into a CLOB.

Oracle 11.2.0.1 64 bit.

We’ve just upgraded and this was not happening with Oracle 10g.

Strange thing is that I got this run-time error for the same DF:

Data type mismatch: In query <QRY_Merge_Long_Desc>, an expression of type <LONG> is mapped to column <LONG_DESC_EXT> of type <LONG>.
Please specify an expression of compatible type or change the type of the output column.

which makes no sense! Adding a simple query before the target table fixed that issue.

George :cyprus: (BOB member since 2003-06-27)

8

Apparently, this version of DS (12.2) is not compatible with Oracle 11.2.

We will install 11.1 and see how it goes.

George :cyprus: (BOB member since 2003-06-27)

9

This problem is reproduced only with oracle client 11.2.0.1.

As a workaround use lower version of oracle.
or Wait for DataServices 12.2.3.0 which is expected to be avilable in Jan 2011.

-Srini

I818328 (BOB member since 2010-12-01)