At my project, we had to migrate CLOB data including Arabic data and we've successed. I thought of sharing my experience, it could be useful for anybody.
We've followed these steps to load CLOB:
1. Source stage is Oracle connector or DRS connector stage.
If Oracle connector stage is used, don't enable LOB references.
2. Define CLOB column at source and destination stages as LongVarChar without specifying length, also don't define it as 'Unicode'.
3. For English data, I didn't find a problem loading it using default project NLS which is 'ISO-8859-1'.
To migrate Arabic data also successfully, we had to try different NLS and it has worked with 'ISO8859-6' and 'UTF8'.
4. Array Size and Transaction Size have to set as 1 and bulk load will not work of course with CLOB or LONG data type.
Source and destination database is Oracle and NLS_CHARACTERSET for both is AL32UTF8.
I didn't try this solution with LONG datatype, but it could work.
It has worked with the same configuration except that source stage has to be Oracle connector stage because it's a MUST to 'Enable LOB references' to make it works.
Also it has loaded Arabic data with NLS of 'ISO8859-6'
Suppose If in a single Job we are using both english and Arabic data ,which NLS Settings we have to use and also could you please explain why you selected the following NLS(with 'ISO8859-6' and 'UTF8').
