Handling CLOB datatype in datastage

[bikan]

Hi,

I have a requirement where I have to fectch column from oracle database having CLOB datatype... I wanted to know that can i directly read the column in datastage ...or I have to use some other method..Please advise...

[ray.wurlod]

Resist the requirement. There's nothing sensible that an ETL tool can do with a CLOB.

[ArndW]

Have your SQL split the CLOB into n columns of VarChar(2000) each and then use either a transform stage or Column Import stage to concatenate the list of VarChar columns into one large one.
This method is being used on my current project because we have CLOBs and BLOBs of up to 60Kb (very wordy XML) and have no choice but to store it in Oracle. This is very unwieldy and, if there is any way for you to avoid it, you should use supported datatypes.

[Jasti]

Hi..
Try using the LongVarChar datatype in the datastage job..We are using LongVarChar[4000] to read some CLOB type data in our project..

[ArndW]

Note that 4000 is bytes. If you are using NLS then the number of characters displayable in 4000 bytes is less and will cause the job to abort.

[chulett]

Do you have any idea what the max size of the CLOB data is? I've had good luck using the built-in DBMS_LOB package to process ours but they're not mongo huge like Arnd's were.

[nasimul]

I have the similar kind of requirement. I need to load a field in Oracle having CLOB datatype. While reading from source, I am using LongVarchar(2147483647). because the length can be 2147483647 bytes.

But while loading into table using upsert in Oracle Enterprise stage, getting error like SQLCODE - 1480.

While trying to load using LOAD method, giving error saon CLOB datatype not supported...like "main_program: Unsupported SQL type CLOB or NCLOB for column MEMO1."

Can you please suggest?

Thanks,
Nasimul

[rameshrr3]

Im loading a CLOB column as LongVarchar(100000) . Source is an XML Chunk from MQ Series ..