Oracle 8i UNICODE support

I need to setup a DB which supports multilingual data entry and viewing.
I have installed oracle 8i on sun solaris 2.6 o/s. During installation, I selected UTF8 as the database character set and the national character set. The installation went fine.
After installation, I created a table called "test" with two columns in it, "code" and "description". I selected the datatype for both the columns as NVARCHAR2.
After that, I created a DB connection to the new DB using Microsoft Query and opened the "test" table.
I tried inserting english characters in it and I got a character type mismatch error. This error message also appears when I used sqlplus to input data to the "test" table. I am using windows NT4.0 as the client.
Do I have to change the character set of my o/s so that I can insert data to the "test" table I have created?

Are there any step by step solutions available to assist me in this matter? My objective here is to be able to insert a mixture of chinese and english characters to the "description" column of the "test" table. In future, I would also like to insert other national characters such as spanish and german. Thanks.
kitsoonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

mnicorasCommented:
Hi,

usually when U try to connect to database the national language it's taken from the client installation. Look in the registry for NLS_LANG in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx where HOMEx is Ur currently oracle_home(usually HOME0). Need to have a value like 'AMERICAN_AMERICA.WE8ISO8859P15' or 'AMERICAN_AMERICA.UTF8'. Changing this to what U really need will set the 'environment' for Ur all sessions opened from the client side.

But to be sure that Ur actions are using correct character set use the command alter session set nls_laguage = 'SPANISH' everytime U want to change it in Ur application according to Ur specifications.

best regards,
nicky

ps: more help?
0

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
stmontgoCommented:
pleas select * from nls_session_parameters and paste here. Are you running this on client or server?
0
kitsoonAuthor Commented:
Hi Nicky, I looked into my registry and checked the value of NLS_LANG. It was set to AMERICAN_AMERICA.WE8ISO8859P1. Should I change it to AMERICAN_AMERICA.UTF8?
Each time I try inserting values to the "test" table using SQLPlus, my SQLPlus application will crash.The Dr Watson application comes out and says that an application error has occured. Any idea what causes this to happen?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

kitsoonAuthor Commented:
Hi stmontgo

I did the select query. Here is the result:
SQL> select * from nls_session_parameters;

PARAMETER
-----------------------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$

PARAMETER
-----------------------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN


PARAMETER
-----------------------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-YY

NLS_DATE_LANGUAGE
AMERICAN

NLS_SORT
BINARY


PARAMETER
-----------------------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-YY HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZH:TZM


PARAMETER
-----------------------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-YY HH.MI.SSXFF AM TZH:T

NLS_DUAL_CURRENCY
$

NLS_COMP

15 rows selected.
0
kitsoonAuthor Commented:
hi stmongo I ran the command from client
0
mnicorasCommented:
Hi,

changing in the registry affects all sessions that will be started from that client machine. basicly change it to AMERICAN_AMERICA.UTF8 and in Ur applications try every time to change the language according to Ur wishes. e.g. alter session set nls_language = 'SPANISH' or whatever.

best regards,

nicky
0
graf27Commented:
please verify any settings of your NLS_LANG environment variable (System,User,Registry,Command-Shell).
You can also use the "CONVERT"-Function (I like).

SELECT CONVERT('Gro?', 'US7ASCII', 'WE8HP')
   "Conversion" FROM DUAL;
0
ksevastiadisCommented:
I worked with UTF8 encoding with Oracle 8.0.5 and Forms6i.
I exported a plain text file with FILE_IO package.
I managed to get UTF8 encode files when I had the entry NLS_LANG=AMERICA_AMERICAN.UTF8 in all forms6i registry entries.
I think that the client must have default encoding UTF8 in its' settings.
0
ksevastiadisCommented:
I worked with UTF8 encoding with Oracle 8.0.5 and Forms6i.
I exported a plain text file with FILE_IO package.
I managed to get UTF8 encode files when I had the entry NLS_LANG=AMERICA_AMERICAN.UTF8 in all forms6i registry entries.
I think that the client must have default encoding UTF8 in its' settings.
0
MoondancerCommented:
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks to all,
Moondancer - EE Moderator

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
MindphaserCommented:
Force accepted

** Mindphaser - Community Support Moderator **
0
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.