Converting LONG to CLOB in INFORMATICA

Question

I am getting below error when i run the informatica mapping, which has a LONG column in source and target column has data type of CLOB. I am trying to use TO_LOB and TO_CLOB, but not helping. In source qualifier in infa mapping there is no LONG or CLOB, so I have used data type in Source qualifier as string, text, nsting and ntext for this column, but still throwing the same error.

ORA-00932: inconsistent datatypes: expected CLOB got LONG

Let me know if you have come across this kind of error, converting from LONG to CLOB.

Thanks Kriti

Answer 1

If you read reference for TO_LOB function http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions205.htm#SQLRF06134

then you see it works only in INSERT ... SELECT ... statements. You can't convert LONG into CLOB on fly. The only way is to replace the table with new one with CLOB used instead of LONG.

However there is workaround from Tom Kyte using PL/SQL function reading by rowid LONG into VARCHAR2(32767). See here https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1037232794454

But it does not work for views where you can't read by rowid. You need to write another more complex function to accept PK columns to read row inside function.