Six Stars

java.sql.SQLException: ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

Hello,

I have a column with a size over 1000, i want to limit the number of characters to 255 so i used this instruction :

StringHandling.LEFT(row1.description,255)

but when i run the job, i got this problem :

tOracleOutput_15 ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

I can not understand the 20 characters between 275 and 255.

I try to delete all the characters no-ascii but i got the same result.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Thirteen Stars TRF
Thirteen Stars

Re: java.sql.SQLException: ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

Hi,

By default on Oracle side, varchar2 length is measured in bytes (in your case, 255) not in characters.

If there is some characters in the input flow which are encoding using 2 bytes, then the total length in bytes may exceed the max length for the Oracle column.


TRF
Six Stars

Re: java.sql.SQLException: ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

hello TRF,

 

Thanks for your reply.

I added code java to delete the characters no ascii.

description.replaceAll("[^\\x00-\\x7F]", "");

 

Ridha

3 REPLIES
Thirteen Stars TRF
Thirteen Stars

Re: java.sql.SQLException: ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

Hi,

By default on Oracle side, varchar2 length is measured in bytes (in your case, 255) not in characters.

If there is some characters in the input flow which are encoding using 2 bytes, then the total length in bytes may exceed the max length for the Oracle column.


TRF
Six Stars

Re: java.sql.SQLException: ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

hello TRF,

 

Thanks for your reply.

I added code java to delete the characters no ascii.

description.replaceAll("[^\\x00-\\x7F]", "");

 

Ridha

Thirteen Stars TRF
Thirteen Stars

Re: java.sql.SQLException: ORA-12899: valeur trop grande pour la colonne "TALEND_REC6"."NOT_IMD"."DESCRIPTION" (réelle : 275, maximum : 255)

Great!

Remind this is not a good practice to accept your own solution when other people give you the direction to follow to solve your case.

 

Edit: thank's Smiley Wink


TRF