Troubleshooting
Problem
ORA-12899 Value of STATE too large for column while creating order, value too large for column
Symptom
When creating an order if one adds special characters to a CHAR
field, the length of which is equal to the column size, they see the following
error. This will be true while inserting special character values into any
column of type CHAR in the database, which is equivalent to the length of the
field.
Error Message
ErrorDescription="ORA-12899: value too large for column "YANTRA_ADM"."YFS_PERSON_INFO"."STATE" (actual: 39, maximum: 35)
"
Resolving The Problem
Check the
NLS_LENGTH_SEMANTICS parameter from the NLS_INSTANCE_PARAMETERS table. Changing
the value to CHAR will resolve the issue.
alter session set
nls_length_semantics = CHAR, prior to running any create table
scripts.
Setting this attribute ensures that the
field sizes are not impacted by the number of bytes a data type can store. For
example, VARCHAR (40) would now be able to store 40 Japanese characters instead
of 40/3 bytes in the UTF-8 character set.
For an existing
table:
(1) Take backup of
the table if need be. (One can use exp utility)
(2) Drop the table
(3) Change the NLS_LENGTH_SEMANTICS
parameter to CHAR
(4)
Recreate the table.
To prove that a change in the length
semantics parameter will help, try the following steps in a local DB
schema:
(1) Make sure that
the NLS_LENGTH_SEMANTICS is set to BYTE.
(2) Create a dummy table with one column as CHAR
(30).
(3) Try inserting
data with special characters which is 30 in length
(4) This insertion should fail
(5) Issue the alter system command to
set the NLS_LENGTH_SEMANTICS parameter to CHAR.
(6) Drop the table and recreate
it.
(7) Try the same
insertion of data as in step 3.
This should be a good enough
proof to show that one can resolve the problem by dropping the table and
recreating it after modifying the parameter value of
NLS_LENGTH_SEMANTICS.
[{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Historical Number
NFX5666
Product Synonym
[<p><b>]Type[</b><p>];NormalFix
Was this topic helpful?
Document Information
Modified date:
11 February 2020
UID
swg21558091