Hi,
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:

NLS_CHARACTERSET = AL32UTF8

NLS_NCHAR_CHARACTERSET = AL16UTF16

NLS_LANGUAGE=HEBREW

NLS_TERRITORY=ISRAEL



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



alter table

co_except_text_lg

modify

(

CONCLUSION varchar2 (1023 char)

);



alter table

co_except_text_lg

modify

(

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.



Regards,

Minal

Why can't the column be defined as VARCHAR2 from the start?

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.

Hi,

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.


Thanks

Hi,

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

Why?

******************************************************
>> 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:

Back to your original issue, it would have been nice if you had told us your error, a ORA-01401

See here:

http://www.dba-oracle.com/sf_ora_01401_ins..._for_column.htm

Check MOSC, there are numerous bugs on this ORA-01401 issue:

http://metalink.oracle.com