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 January 31, 2017 - Updated February 4, 2017


100% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.