Help - Search - Members - Calendar
Full Version: error message“value too large for column "
Oracle DBA Forums > Oracle > Oracle Forum

The application doesn’t allow user to enter more than 1023 chars there is no issue there. But while this being saved to the database we get an error message saying “value too large for column CO_EXCEPT_TEXT_LG.CONCLUSION actual 2041 max 1023”.

Following were my DB settings:

In my web server, app server and DB server the oracle variable NLS_LANG is HEBREW_ISRAEL.IW8ISO8859P8 (registry setting)

I created a DB with the following settings:





When I alter the column to be of type Nvarchar2 (1023) or varchar2 (1023 char) the issue is resolved.

alter table




CONCLUSION varchar2 (1023 char)


alter table




CONCLUSION Nvarchar2 (1023)


There is a parameter called nls_length_semantics whose default value is byte, we can set this at system level to char, doing this also can fix the issue (not tested). But we need to do this before creating the schema (before schema creation and after database creation).

All these options seem to be a lot of change to schema. There could be simpler setting changes on the oracle side which could resolve this issue, I am not able to find them, please suggest.


Why can't the column be defined as VARCHAR2 from the start?
QUOTE (minal @ Mar 5 2010, 08:21 AM) *
I created a DB with the following settings:


Try creating the database with a 16bit character set. The database is using 16bit characters and that is why you are running out ot space.
When you recreate the varchar is is using 16 bit characters in the definition and of course nvarchars are 16 bit characters.

we shall be changing
nls_length_semantics= at system level
and we shall be performming following steps for existing database object

1. Full backup of database with export.

2. Change nls_length_semantics to char bounce database back

3. Import with index file option

4. Edit file to recreate tables

5. Import with ignore=y
Will it cause any performance problem in future?
Following lines from oracle documentation.

"For best performance, choose a character set that avoids character set conversion and uses the most efficient encoding for the languages desired. Single-byte character sets result in better performance than multibyte character sets, and they also are the most efficient in terms of space requirements. However, single-byte character sets limit how many languages you can support."

Please provide your suggestion.



>> we shall be performming following steps for existing database object


>> Please provide your suggestion.

About what?

The Oracle documentation you cited sounds fine to me . . .

READ THE DOCS. It suggests that a migration is not necessary:

NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics.

Existing columns are not affected.
Back to your original issue, it would have been nice if you had told us your error, a ORA-01401

See here:

Check MOSC, there are numerous bugs on this ORA-01401 issue:
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2015 Invision Power Services, Inc.