ascii to unicode Database changes

1
votes

We are migrating legacy application ascii to unicode, as part of this activity we need to change datatypes from varchar2 to nvarchar2 so that it can accept different character. I was trying testing this activity for one the table and one column (using alter table script) and it took more than 15 mins as table is holding data (13,81,67,254 rows). There are around 200 tables(each table having around 25 columns having varchar2 datatype) present and to complete this activity it will take lot of time and we cant afford so much down time. Is there any way to perform this quicker? DB - Oracle 10 g

Editing Question - Is there any way we can avoid conversion of datatypes from varchar2 to nvarchar2? Curretnly varchar2 range defined in BYTES e.g. - varchar2(3 BYTE).

Database Details -

  • Oracle 10g
  • NLS_CHARACTERSET - UTF8
  • NLS_NCHAR_CHARACTERSET - AL16UTF16
2
sqloracle
Why do you want to change from VARCHAR to NVARCHAR? Change your DB to characterset AL32UTF8, then VARCHAR works. Nowadays with Unicode there is no reason anymore to use NCHAR or NVARCHAR. dataytype at all. – Wernfried Domscheit
@Wernfried - Thats great, I m going to goggle about characterse AL32UTF8. Can you can shed some light how to go about it? I mean where and how to set this. – Pravin Satav
Have a look at this document: Character Set Migration – Wernfried Domscheit
will get back to you on this after going through the document – Pravin Satav
@Wernfried - I went through the document but have a dount, if I m trying to store data for varchar2(3) column and lets say I have '3 asian characters' and my current character set is "UTF8" which is not allowing me as it needs more than 3 bytes. But if I change my character set to "AL32UTF8" then will it allow me even though its consuming more than 3 bytes? Also DB characterset you meant in your comment is NLS_NCHAR_CHARACTERSET or NLS_CHARACTERSET? – Pravin Satav

2 Answers

1
votes

Try

alter table modify <column_name> varchar2(4 char)

This should be doable on a live system without anyone noticing. 4 char should be enough to store Unicode asian characters.

1
votes

I recommend using the latest Oracle Data Migration Assistant for Unicode (DMU). Here is a link:

http://www.oracle.com/technetwork/database/database-technologies/globalization/dmu/overview/index.html

But also it's good to have an understanding of Unicode (which isn't that complicated). Take a look at Wiki for a better understanding of Unicode.

I agree with those who have commented that it makes the most sense to use a default unicode based character set so that you can use varchar and you won't need nvarchar.

One issue with conversion is size. Suppose you have a declaration of varchar2(3). This declaration means you can store up to 3 bytes which for non-unicode equates to 3 characters because each character only takes up one byte. But in unicode a character can take up more then one byte. You could change the declaration to varchar2(3 CHAR) and then it would work for unicode. You could also change the default for the database for varchar2 to be CHAR rather then BYTE. In either case you still have the maximum bytes issue to contend with. The maximum number of bytes for varchar2 is 4000 (unless you are upgrade to Oracle 12c in which case you can change the maximum amount to 32767 bytes). In any case if you have a declaration of varchar2(4000 char) you may or may not be able to insert 4000 unicode characters. You can only insert that many if all the characters are represented with one byte in you unicode encoding (for example if they are ascii characters). In AL32UTF all characters take up 1-4 bytes. So that means if you use a declaration of varchar2(1000 CHAR) you are guaranteed to be able to have up to 1000 characters, even if all the characters have 4 byte encodings.

I also recommend changing the default nls_length_semantics to CHAR. That way varchar2(n) will mean up to n characters can be stored, irregardless of the number of bytes that the n characters require (as long as it is less then 4000 bytes).