How to check byte length of data?

chulett · Post by **chulett** » Sun Nov 08, 2009 8:27 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sun Nov 08, 2009 10:04 pm

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

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

ArndW · Post by **ArndW** » Mon Nov 09, 2009 2:43 am

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.

chulett · Post by **chulett** » Mon Nov 09, 2009 6:24 am

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

chulett · Post by **chulett** » Mon Nov 09, 2009 2:44 pm

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".

aaryabhatta · Post by **aaryabhatta** » Thu Feb 25, 2010 9:31 am

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.

chulett · Post by **chulett** » Thu Feb 25, 2010 10:26 am

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.

aaryabhatta · Post by **aaryabhatta** » Thu Feb 25, 2010 10:48 am

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?

ray.wurlod · Post by **ray.wurlod** » Thu Feb 25, 2010 3:57 pm

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

aaryabhatta · Post by **aaryabhatta** » Fri Feb 26, 2010 3:30 am

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

dsedi · Post by **dsedi** » Fri Feb 26, 2010 11:22 am

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