Transfering CLOB Data to BLOB using Informatica...

Few days before one of my colleague had a requirement of transferring data from one source table to another target table as is... Requirement was quite simple as no logic was involved in between, however there was a difference in data type of both the tables.

Below is a sample structure of  Source and Target table respectively.

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

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

Though the requirement was pretty clear and simple, challenging part was to convert CLOB data type to BLOB. We tried number of things as below

When you import CLOB data type as a source, Source definition will have CLOB data type by default as of source and Source Qualifier will have TEXT data type with precision as 4000.

If you try to transfer this text data [from source] directly to Target BLOB without converting it, it won't allow you and will give error "Data Types text and BLOB are incompatible"

If you convert data type of expression from text to binary, it will allow you to connect; however mapping will be still invalid giving below error, because Source Qualifier has data type as text.

------------------------------------------------------------------------------
9/1/2013 2:29:04 PM ** Validating mapping m_ConvertBlobToClob
------------------------------------------------------------------------------
Validating transformations of mapping m_ConvertBlobToClob...
...transformation validation completed with no errors.
Validating data flow of mapping m_ConvertBlobToClob...
...link has incompatible data types: from  SQ_CLOB_SOURCE: TEXT_FILE  to  exp_ClobToBlob: TEXT_FILE.
...there are data flow validation errors.
Parsing mapping m_ConvertBlobToClob...
...parsing completed with no errors.

******** Mapping m_ConvertBlobToClob is INVALID ********


So, we have to find some way, by which we can convert incoming text data to binary.

After doing lots of Hit and Trials, we finally got a way to convert Characters to Binary using Java code.

Java function <String>.getBytes() will return byte array which is a binary value of a particular string and same we will pass ahead to Target column.

So our mapping will be as below

Here, we will add Java Transformation in between source and target which will actually convert Char to Binary.

Code written in Java transformation is as below

We just called getBytes() method of Java which will return binary array values and same we will pass to the target.

Validate the mapping and create the workflow.

If you run the workflow with Bulk Load it will fail giving below error.


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

Database driver error...
Function Name : Prepare DPL
SQL Stmt : INSERT INTO BLOB_SOURCE(IDX,TEXT_FILE)  VALUES ( ?, ?)
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO BLOB_SOURCE(IDX,TEXT_FILE)  VALUES ( ?, ?)  



Change the load from Bulk to Normal and your workflow will get succeed. Also, keep in mind that, if you are creating Passive Java transformation [in this case it will be passive, as we are not changing number of incoming rows] you do not need to call generateRow() API in java transformation, rather java transformation will not allow you to call it inside the code when its a Passive transformation.