DSXchange

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.

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.

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.

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

You might be getting double byte characters in source.

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

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?

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

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

Is this an option in Oracle or DataStage itself?

Oracle.

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

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.

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

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.