Issue with reading a oracle CLOB column in Informatica and Inserting to Netezza using Netezza PowerCenter Driver:

Issue with reading a oracle CLOB column in Informatica and Inserting to Netezza using Netezza PowerCenter Driver:

Issue: Informatica session failed with the below message when reading a table with a column of CLOB data type.

Severity    Timestamp    Node    Thread    Message Code    Message
ERROR    12/16/2014 11:40:43 AM        NZ_WRT_1_*_1    Net_1762    [ERROR] Failed to execute query : [INSERT INTO "MT TABLE"("MY_KEY", "KEY_DESC", "BATCH_KEY") ...........

The .nzbad showed that all the columns after clob column was going to next line. i.e netezza driver was not able to read properly the | delimited file that it creates while inserting data to the target.

Resolution:
There can be two reasons for this issue, either your clob column is null or you have some new line characters. First I fixed the null issue but the problem still persisted which made me later think that the clob column has some newline or carriage return character. Fixed the issue by using the replacestr function to replace all the new line and carriage return characters.

The expression is below:

IIF(isnull(CLOB_COLUMN_STRING),'  ', REPLACESTR (1,CLOB_COLUMN_STRING,CHR(10),CHR(13), ''))


char(10), char(13) in the expression above stands for newline and carriage return characters.



In the above example CLOB_COLUMN_STRING is the name of the port that belongs to the CLOB table column. In Informatica, clob columns are read as Text data type.

Note: Also, make sure the length of the text port in informatica for the clob column is same as the length of clob column defined in the oracle table.