58 - How will you transfer CLOB Data to BLOB Data using informatica

Requirement -- You have a oracle source column which holds CLOB data and you have to insert same data into a target column which is of type BLOB. So, you need to convert CLOB data into BLOB via Infrmatica Mapping.

1. Lets create a source and target first;

Source Table Create Table CLOB_Source
(Idx      NUMBER(3),
Text_File      CLOB);
Target Table
Create Table BLOB_Source
(Idx      NUMBER(3),
Text_File      BLOB);

2. Insert some dummy data into newly created source

insert into Clob_Source values (1, 'This is a Clob Source Text');


3. Create a simple mapping to pass data from Source to Target

As you can see, if you simply try to connect your source qualifier port "MyText" which is automatically got converted to text datatype to target MyText which is of BLOB, informatica will not allow you and pops up an error as "Data types text and blob are incompatible"

How will you overcome this error ?

 You can change the Source Qualifier data type to Binary and then informatica will allow you to link the port, however, when you will validate the mapping, you will get the error again as mentioned in above image.

But, if you add a Java Transformation in between Source and Target, you can achieve it.

- Add (Passive / Active) Java Transformation, and add extra out port BLOB_MyText into it along with source ports id and MyText.
- Write a simple java code which will actually convert your Clob to Blob as below
         Blob_MYTEXT = MYTEXT.getBytes();
- Connect the ports and validate the mapping

As you can see, I have created passing Java Transformation, and added corresponding code at "On Input Row" table, which will actually convert CLOB data into BLOB.

Note -- If you are doing Bulk Load, your mapping gets fail giving below error.

Database errors occurred:
ORA-39778: the parallel load option is not allowed when loading lob columns


However, I have observed that its specific to your oracle version.