How to Truncate data in Oracle

ca.parker · Post by **ca.parker** » Tue Jun 09, 2009 8:03 am

We have recently changed our database to 10g and now I am getting an error message:

extServiceRequest..Transformer_1: ORA-12899: value too large for column "SBLMART_DATA"."EXT_S_SRV_REQ"."DESC_TEXT" (actual: 302, maximum: 255)

Is there a function to trucate this data to a maximum length of 255? This is just a description field so we do not want this to be and longer than 255 and if it is then we just want to truncate the remaining data.

ArndW · Post by **ArndW** » Tue Jun 09, 2009 8:07 am

If you already have a transform stage, just use "In.DESC_TEXT[1,255]" or "LEFT(In.DESC_TEXT,255)". If you have a modify stage you'll need different syntax, which I can't recall off the top of my head.

Or use the ORACLE "substr" DML command.

ca.parker · Post by **ca.parker** » Tue Jun 09, 2009 8:12 am

Here is what I currently have in the transformer:

OutputSRLink.DESC_TEXT[1,255]

I also tried left(OutputSRLink.DESC_TEXT,255) and still get the error message.

priyadarshikunal · Post by **priyadarshikunal** » Tue Jun 09, 2009 9:06 am

first try to load only 200 characters and see what happens.

You might be getting double byte characters in source.

ArndW · Post by **ArndW** » Tue Jun 09, 2009 9:17 am

Do you have an NLS installation and how are your Oracle columns defined - by character or by byte?

ca.parker · Post by **ca.parker** » Tue Jun 09, 2009 10:38 am

Yes I am using the NLS map MS1252. I noticed the columns are defined as bytes also. Is there a way to define the columns as character instead?

chulett · Post by **chulett** » Tue Jun 09, 2009 10:59 am

Sure, it's an option when creating the column:

FRED1 VARCHAR2(20 CHAR)
FRED2 VARCHAR2(20 BYTE)

ca.parker · Post by **ca.parker** » Tue Jun 09, 2009 11:06 am

Is this an option in Oracle or DataStage itself?

chulett · Post by **chulett** » Tue Jun 09, 2009 11:11 am

Oracle.

ca.parker · Post by **ca.parker** » Tue Jun 09, 2009 11:14 am

Ok. So there is not any way I can just simple tell DataStage to truncate the data in the column is it exceeds 255?

chulett · Post by **chulett** » Tue Jun 09, 2009 11:20 am

You're already doing that with your substring notation of [1,255] - that limits it to 255 characters. Your problem is those 255 characters need more than 255 bytes to hold them. Any chance of getting the target table altered? All it takes is a simple MODIFY statement for the column in question and you can do it with the existing data there, i.e. the table does not have to be empty for this.

ca.parker · Post by **ca.parker** » Tue Jun 09, 2009 11:25 am

Is there an option in DataStage for when the DDL is created to create that in character instead of byte?

chulett · Post by **chulett** » Tue Jun 09, 2009 11:42 am

You'd have to switch to 'user-defined' DDL for that, I assume, and add the CHAR part yourself. Pretty sure the default when you don't specify either is BYTE.