Handling CLOBs and CRLF.

[cdrk823]

Hi Everyone,

I've read through a couple of threads and found no concrete solution in handling LongVarChar fields in tables.

We have a problem with one of these fields. I've tried setting the length into monstrous proportions, but still to no avail. OCI_INVALID_HANDLE.

That's not really our problem, but this: does anyone have a suggestion in handling CRLF from within these fields? I've tried setting this to Char type with a 2k length, everything was fine, but since my target is a sequential file, it produces CRLFs in the wrong places (i.e. Now there are extra lines in my file because some records have new line characters from within their text).

Would changing this field back to LongVarChar help at all? Does anyone have any suggestions on how to handle these? Take note that modifying the program that reads this file IS NOT AN OPTION. :D

Thanks as always.

[ray.wurlod]

Try a CAST in the Derivation of the column. For example

Code: Select all

CAST(InLink.TheCLOB AS VARCHAR(2000))

For the line terminations, get to the Columns grid in your Sequential File stage, and scroll to the right till you find "contains line terminators". Set this to "Yes".