Transfering CLOB Data to BLOB using Informatica...Part - II

In last part we saw how we can transfer CLOB data to BLOB data using Informatica. However, the solution which we saw will not always holds true.

As seens earlier, when you import CLOB datatype to Informatica as a source, it will assign data type as TEXT and precision as 4000 to that port by default. [you can increse the precision later on, informatica allows you that]. But why by default its always 4000 ?

Lets create sample table as below and all 1 record in it

Create Table CLOB_Source
(Idx NUMBER(3),
Text_File CLOB);

Insert into  CLOB_Source values (1, '<b> Adding first record </b>');

Now, try to convert CLOB record to Char using To_Char as below

select idx, to_char(text_file)
from CLOB_Source
Oracle will convert that without any difficult.

Now add one more record, but this time at CLOB field give 4000+ characters. Your insert statement will not work giving below error.

Error report:
SQL Error: ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

Even if you add character set more than 4000 in CLOB field, and if you try to convert that using to_char function, oracle will not allow you giving same 4000 characters limit error.

So, oracle will not allow you to convert CLOB data type to To_Char if its more than 4000 characters.

As seen earlier, informatica by default assigns TEXT 4000 to CLOB data type when we import it as a source. Eventually, even internally informatica is trying to convert CLOB data type to Char but when then size of CLOB characters is more than 4000, your mapping will fail giving Oracle database fetch Error.

Solution  --  After doing google and even trial and error, ultimately we found below workaround.

When CLOB data type has more than 4000 characters, divide such records in set of 4000 characters each.

E.g. First find out maximum length of characters in CLOB data type. lets say if its 7500, then divide your column in 2 sets 1-4000 and 4001 to 8000

select idx, substr(text_file, 4000),
substr(text_file,4001,8000)
from CLOB_Source

Add above query to your informatica source qualifier, so in this case you have one extra port for same column. Concatenate these 2 port in a expression tranformation immediately after Source Qualifier.

As we have added SubStr limit to 4000, informatica can easily convert CLOB data to Text and your mapping will not fail.