Support Article
ORA- value too large exception even after changing semantics
SA-33104
Summary
Getting ORA-12899 errors in the logs even though the length semantics of the database have been altered from bytes to chars.
SQL> show parameter nls_length_semantics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string CHAR
Error Messages
ORA-12899: value too large for column "PEGADATA"."PC_HISTORY_MYWORK"."PYMEMO" (actual: 282, maximum: 280)
Steps to Reproduce
Try to insert 280 characters, including at least one multi-byte character, into the table.
Root Cause
A defect or configuration issue in the operating environment. When the pyMemo column was created the NLS_LENGTH_SEMANTICS parameter was still set to BYTE by default. Thus the column was created using byte length semantics. Changing the NLS_LENGTH_SEMANTICS to CHAR will only affect newly created columns.
- VARCHAR2(20) : Uses the default length semantics defined by the NLS_LENGTH_SEMANTICS parameter which defaults to BYTE.
- VARCHAR2(20 BYTE) : Allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents.
- VARCHAR2(20 CHAR) : Allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.
Resolution
Make the following change to the operating environment:
Explicitly alter the column to use char length semantics.
ALTER TABLE PEGADATA.PC_HISTORY_MYWORK MODIFY (PYMEMO varchar2(280 CHAR));
Published February 4, 2017 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.