ORA-12899: value too large for column error

[former_member241220]

Hi All,

I have a job with pulling data from flat file and load into Oracle(staging) and finally load data into BW(Target). As a daily schedule monitoring I found a issue regarding "ORA-12899: value too large for column" error. I went through flat file and verified all records having 25 char length, and source having 684 records and this 684 records loaded by looking monitor log and job failed with above error. If all records having 25 char length, why I am still getting "ORA-12899: value too large for column" error.

Here is monitor log

Path name                                                                                                                                                                                              State Row Count Elapsed time (secs) Absolute time (secs

+DF_HEALTH_ELEMENTS_FILE_LOAD/F_HEALTH_ELEMENTS 1	PROCEED	0	0.016 11.455
/DF_ HEALTH_ELEMENTS_FILE_LOAD /Q_ HEALTH_ELEMENTS_TEMP_LOAD	PROCEED	684	0.000 11.455
- DF_ HEALTH_ELEMENTS_FILE_LOAD/ Q_ HEALTH_ELEMENTS_TEMP_LOAD _HEALTH_ELEMENTS_TEMP_LOAD	PROCEED	684	0.000 11.455

Note: I am getting 0 records form source(F_HEALTH_ELEMENTS 1) file where I marked green

Here is error log

(14.2) 10-04-16 08:18:28 (E) (10148: 6532) DBS-070301: | Data flow DF_HEALTH_ELEMENTS_FILE_LOAD | Loader Q_HEALTH_ELEMENTS_TEMP_LOAD_HEALTH_ELEMENTS_TEMP_LOAD

Oracle <(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = ZEBROC.linux.UK) (PORT = 6780))) (CONNECT_DATA = (SID = UNIVERSALBI)))> error message for operation <OCIStmtExecute>: <ORA-12899: value too large for column

"STAGING". "HEALTH_ELEMENTS_TEMP_LOAD". "LONGDISCRIPTION" (actually: 26, maximum: 25)

Please help to understand the actual issue

Thanks

Murali

[vnovozhilov]

Please re-run the job with the tracing enabled to catch the actual record which causes the issue. In case it has some multibyte/unicode characters you may wish to check if NLS_LENGTH_SEMANTICS set to BYTE or CHAR and NLS_LANG settings.

Thank you,

Viacheslav.

[former_member241220]

Hi Viacheslav,

Thanks for your swift reply,

where can i check NLS_LENGTH_SEMANTICS set to BYTE or CHAR and NLS_LANG settings.

Thanks

Murali

[vnovozhilov]

Connect to Oracle db and issue the following command:

select * from v$nls_parameters where Parameter = 'NLS_LENGTH_SEMANTICS';

Viacheslav.

[former_member198401]

You source column might contain spaces or blanks due to which the length of column gets increased.

Try increasing the length of affected column (E.g. Varchar(30)) and try loading the table

Regards

Arun Sasi

[former_member241220]

Hi Arun,

I tried with increasing affected column upto Varchar(30), but still I am getting same error. Even I try with ltrim and rtirm with affected column. but no luck

Can you suggest me, how to resolve this issue?

Thanks

Murali

[former_member198401]

Murali,

Can you follow below SAP Note

1410961 - ORA-12899: value too large for column - Data Services

Regards

Arun Sasi

[former_member198401]

Is the issue resolved or are you still facing the issue?