Starge behavior while extract flat file source in sap bods

[former_member241220]

Hi All,

I have a .csv file as source, in that one of the column having character length 20 such as "CLIENTNAME varchar(20)"

my issue is while extracting the 25 length character, it is going to trim automatically and load into target (Oracle)

For Ex: the record "MURALI KRISHNA GARIKAPATI" is going to trim and load into target(oracle) as "MURALI KRISHNA GARIK"

Here my intention is I don't want trim the record, it should be get "ORA-12899: VALUE TOO LARGE FOR COLUMN" ERROR.

Can you please help me in this strange behavior in flat file as source?

Thanks

Murali.

[former_member187605]

Increase the character length to 25. Specify CLIENTNAME varchar(25) in the file format definition.

[former_member241220]

Thanks Dirk for your swift reply,

I would like to know, how flat file source trim the record automatically? actually if we pass the value more then length of our column, we have to get "Value to large error". But here value going to trim.

I am very confuse of this behavior, can you explain what is the problem?

Thanks

Murali

[former_member208402]

Hi Murali,

when we specify a field length to varchar(20) it will automatically take 20 length string only even if the string is of more than 20 length. because at source level only it gets trimmed.

if the source length is varchar(30) and target filed lenght is of varchar(20) and if we are not chaging this datatype to varchar(20) in query transform between source and target and when we get a record that is more than 20 length then it might give you ORA-12899: VALUE TOO LARGE FOR COLUMN" ERROR. because from source we are getting 30 length value but target is not able to store 30 length value.

[former_member208402]

Hi Murali.

i found this.. may be this is the issue.

The usual reason for problems like this are non-ASCII characters that can be represented with one byte in the original database but require two (or more) bytes in the target database (due to different NLS settings).

To ensure your target column is large enough for 15 characters, you can modify it:

  ALTER table_name MODIFY column_name VARCHAR2(15 CHAR)