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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
sam15Author Commented:
are you sure this applies for National character set.

slightwv (䄆 Netminder) 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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
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)
The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g (Doc ID 276914.1)

sam15Author Commented:
I think you can. What about this link?
From Oracle Documentation (9i):
To change the national character set, use the ALTER DATABASE NATIONAL CHARACTER SET statement. The syntax of the statement is as follows:


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.
slightwv (䄆 Netminder) Commented:
>>What about this link?


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


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.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.