I have to change a column VARCHAR2 in NVARCHAR2 in my Oracle database but I have a problem. The maximum length for a VARCHAR2 is 4000 but for a NVARCHAR2 is 2000 and when I'm trying to change that, I have an error about the length due to the value already existing.
ALTER TABLE myTable MODIFY (myColumn NVARCHAR2(2000));
=> "ORA-01441: cannot decrease column length because some value is too big"
Is there a solution to have the same length than the VARCHAR2 ?
Thanks in advance,
Joined: Oct 27, 2008
I see a solution, I have to change the national charactset from AL16UTF16 to UTF8 but I have not the privilege to do that now...
I'm waiting :-)
The maximum size of any column (except LOBS) in Oracle is 4000 bytes. Since the VARCHAR2 and NVARCHAR2 datatypes use different database encodings, it might mean that their maximum length limits differ, which seems to be your case. It looks like your table contains data in the affected column which are longer than 2000 characters, so you cannot store it in NVARCHAR2 (with your current national character DB encoding, in any case).
I'm not aware of any workaround. What you could do is to use NCLOB instead with the inline option - this way you'd have the shorter (smaller than 4000 bytes) fields stored inline, and only have the penalty of storing the data out of line (apart from the rest of the table data) for larger fields. In other words: you'd still have to handle NCLOBS correctly (using the LOB functionality), but the performance for values smaller than 4000 bytes (2000 characters in your case) would be the same as NVARCHAR2's.
You cannot convert VARCHAR2 to CLOB/NCLOB directly, however. You'll have to create a "new" table, insert (with the /*+append*/ hint) old data into it, drop the "old" table and rename "new" to "old". This can be even made online if needed (using Oracle's online redefinition process), but that's already beyond my experience.
I've seen your update only after finishing my earlier reply.
Changing the database encoding actually means physically rewriting all character data, I'd say it is non-trivial operation that needs to be thoroughly understood. Privileges won't be the only problem here.
The difference between VARCHAR2 and NVARCHAR2 is the number of bytes used to store a character. Since, as was mentioned, the physical size in bytes of the area in which these 2 field types get stored in in the same, capacity is halved for NVARCHAR2.
Although you can swap out tables to get around this, you should also be able to play games with just the original table. Just make sure that you have a readily-accessible backup!
Basically, what you'd do is alter the table to add a column with the new field type defined, then apply a SQL update to copy the original column's value into the new column, applying any code-page mods or the like as you do. Then alter the table to remove the original column and rename the new column to the original column's name. Reorg the table as needed.
If the original column does, in fact contain items whose length is greater than 2000 characters, then your new column would need to be a CLOB, not NVARCHAR2. Because no matter how you shuttle the data around, you can't put 2 tons of fertilizer in a 1-ton truck.
An IDE is no substitute for an Intelligent Developer.
Tim Holloway wrote:Basically, what you'd do is alter the table to add a column with the new field type defined, then apply a SQL update to copy the original column's value into the new column, applying any code-page mods or the like as you do. Then alter the table to remove the original column and rename the new column to the original column's name. Reorg the table as needed.
This is possible, although creating and populating a new table may be several times faster for large tables (say, millions of rows or more).
If the original column does, in fact contain items whose length is greater than 2000 characters, then your new column would need to be a CLOB, not NVARCHAR2.
Just a small note: the new column should be NCLOB, not CLOB, for it to use the national character set.