DSXchange

Got the dreaded 'ORA-12899 value too large for column' error because somehow a two character field got some multi-byte crappola in it and it won't fit in an Oracle VARCHAR2 field defined as 2 bytes rather than characters.

Is there an expedient way to check the byte length of a string? May need to end up making this check in multiple fields in 20+ million records, hence the need for speed.

How about Len(StringToRaw(InLink.TheString)) ?

Or ByteLen(InLink.TheString) in a BASIC Transformer stage ?

Craig - we had to put in a BASIC transform stage in order to figure out byte lengths, basically we had to parse a very long string into multiple Oracle strings and they could contain multibyte data and the BASIC ByteLen() was our first choice. I think we've now replaced that with a BuildOp because the BASIC transform stage caused sporadic production hangups.

Thanks guys. I'll check these options out today, hopefully.

ray.wurlod wrote:How about Len(StringToRaw(InLink.TheString)) ?

This I can't even get to compile. Depending on the output data type it either complains about an "Invalid conversion requested from a string to a raw" or "from an int32 to a raw".

chulett wrote:

ray.wurlod wrote:How about Len(StringToRaw(InLink.TheString)) ?

This I can't even get to compile. Depending on the output data type it either complains about an "Invalid conversion requested from a string to a raw" or "from an int32 to a raw".

Hi Craig,

Have you managed to find the solution for this please?
My requirement is to supress multi byte characters from loading to Oracle.

Thanks.

Heck, I'm not working for that client any longer and I forget what we ended up actually doing for this.

Tell us what you mean by needing to "suppress" multi-byte characters.

chulett wrote:
Tell us what you mean by needing to "suppress" multi-byte characters.

I am trying to load around 200 million records to Oracle db, but some records seem to contain multi byte characters due to which Oracle load is failing.

I don't want the job to abend, instead I want to capture all those such records and continue with the rest of the records.

So I am trying to identify these records before the Oracle stage, for example in a transformer but so far failed to do so.

Any ideas please?

Add a reject link to your Oracle Enterprise stage or Oracle Connector.

Those records are not getting rejected through Oracle Enterprise stage but causing the job to fail.

The specific error that I am getting is:

ORA-12899: value too large for column

If you can not think ALTER DATABASE CHARACTER SET then this link may educate/help you to write a routine to handle Multibyte Characters.
would be happy to see any working solution here.. :D