my dog learned polymorphism*
The moose likes Oracle/OAS and the fly likes VARCHAR2 to NVARCHAR2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "VARCHAR2 to NVARCHAR2" Watch "VARCHAR2 to NVARCHAR2" New topic
Author

VARCHAR2 to NVARCHAR2

Fabian Angy
Ranch Hand

Joined: Oct 27, 2008
Posts: 90
Hi !

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,

Angy.


SCJP 5
Fabian Angy
Ranch Hand

Joined: Oct 27, 2008
Posts: 90
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 :-)

If someone has another solution.. :p
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15960
    
  19

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.


Customer surveys are for companies who didn't pay proper attention to begin with.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: VARCHAR2 to NVARCHAR2
 
Similar Threads
Hibernate String Mapping
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Multiple arrays to SP
Unable to cast preparestatement of weblogic6.1 to oracleprpepared statement
Authenticate Hashed password from My Java Swing Application