National_character_set

I would like to change the national character set for 9i databse from ALT16UTF16 to AL8UTF8 so i can store a file in UTF format in NCLOB column.

The database character set is ISO88591.

Would this impact anything else? Is it safe to do. is it simple command and reboot of server.

I do not have any columns NVARCHAR2 or NCLOB too.
sam15Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I'm far from an expert on all the NLS stuff going on with Oracle but I know a little so I'll try to help.

I think the confusion here is the NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET.

I believe you are wanting to change the NLS_NCHAR_CHARACTERSET.  This dictates what goes in to N* columns.

Do you currently have an data stored in an N* column?  If not, You should be able to go ahead and make the change (to NLS_NCHAR_CHARACTERSET).

From my limited experience with charactersets I believe you shouldn't have to change anything.

Granted under the scenes UTH16 and UTF8 are a little different but you should be able to store anything in the UTF16 NCLOB that you can store in the UTF8.

Can you elaborate a little on why you think you need to do this?
0
 
Sanjeev LabhDatabase ConsultantCommented:
It is not extremely simple but still relatively easier to change while doing an export import. For changing the character set you would first have to export the whole database in this new character set with the use of argument specified at during export, and then import it back. This would change the characterset. This might not necessarily impact the database.

But, this is relatively quite easy to specify during creation of new database.
0
 
sam15Author Commented:
are you sure this applies for National character set.

according to this it is ALTER DATABASE NATIONAL CHARACTER SET SET UTF-8

http://repettas.wordpress.com/2008/05/16/national-character-set-in-oracle-9i-and-10g/
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sam15Author Commented:
Yes there are no columns with NCHAR or NVARCHAR2 or NCLOB type now.
The reason i want to changeUTF16 to UTF8 is i have a requirement to generate an XML file in UTF8 format for other external vendor to process. They do not want UTF16.
Now i use ISO88591.

so i want to create NCLOB column type and generate the file and store it there. Then When vendor selects it via a URL it goes through mod_plsql as a file without any character set conversion and they wil ahve it as UTF8.
0
 
slightwv (䄆 Netminder) Commented:
Again, I'm not an UTF expert but I believe that how the document is created and how it is stored are two different things. If the XML is UTF8 encoded it can be stored in a normal varchar2 field.

After a little looking it doesn't appear you can set NLS_NCHAR_CHARACTERSET once you've created the DB. At least I couldn't find a link on how to do it.

All that said, I suggest a couple of documents from Oracle:

Unicode Character Sets In The Oracle Database (Doc ID 260893.1)
and
The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g (Doc ID 276914.1)





 
0
 
sam15Author Commented:
I think you can. What about this link?
From Oracle Documentation (9i):
http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch10.htm#1010397
<Quote>
To change the national character set, use the ALTER DATABASE NATIONAL CHARACTER SET statement. The syntax of the statement is as follows:
ALTER DATABASE [db_name] NATIONAL CHARACTER SET new_NCHAR_character_set;

</Quote>

You cant store a UTF8 document in ISO88591 database. You might lose data. Character sets are like human languages. If databse talks X and document is in Y language it converts it to X.
0
 
slightwv (䄆 Netminder) Commented:
>>What about this link?

That is NLS_CHARACTERSET not NLS_NCHAR_CHARACTERSET.

>>You cant store a UTF8 document in ISO88591 database

I agree if you take a UTF8 'string' and stick it into a regular VARCHAR2 field.

An XML document isn't RAW UTF data.  Once you generate/encode an XML document UTF8, it pretty much becomes a regular text document.

Can't you open/read it with Notepad?

Now back to point:  I bet you can take a raw UTF8 string and safely put it into a UTF16 NVARCHAR2 fiend and extract it back out w/o losing data.

Try setting up a test table with your system as-is with a NCLOB and try it.
0
 
slightwv (䄆 Netminder) Commented:
I have to apologize for one of my comments:
" Once you generate/encode an XML document UTF8, it pretty much becomes a regular text document."

I checked with our sys-admin who knows more than I do about character encoding.  He says the multi-byte character 'might' remain unencoded in the XML doc.

Sorry for any confusion I caused.

I still think you'll be OK storing a UTF8 XML doc in a ALT16UTF16  NCLOB column.
0
 
sam15Author Commented:
if you store a utf8 document in utf16 columns it will be utf16 format. It might be same or it might be different depends on the character but these are two diff unicode set.


I do not understand your comment about NLS NCHAR character set. The article describes how to change both character sets. Scroll down to the end of page.

 

Changing the National Character Set
To change the national character set, use the ALTER DATABASE NATIONAL CHARACTER SET statement. The syntax of the statement is as follows:

ALTER DATABASE [db_name] NATIONAL CHARACTER SET new_NCHAR_character_set;


db_name is optional. The character set name should be specified without quotes.

You can issue the ALTER DATABASE CHARACTER SET and ALTER DATABASE NATIONAL CHARACTER SET statements together if desired.


0
 
slightwv (䄆 Netminder) Commented:
OK.  I can't argue with the statements and unfortunately I can't try them out to verify.

Since you don't have any N* data or multi-byte character data currently, I can't think of a reason to not do it if the database will let you.

I would make sure you have a good backup (I would prefer a cold backup) before you do this.

I'm afraid I have nothing else I can offer on this question.  I suggest you contact Oracle support to confirm all this since it doesn't appear any experts are going to post.

You can always try one last time to get more experts to post by 'Requesting Attention' using the link above.
0
All Courses

From novice to tech pro — start learning today.