Solved

Oracle 8i UNICODE support

Posted on 2001-06-18
11
882 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:kitsoon
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 3

Accepted Solution

by:
mnicoras earned 300 total points
ID: 6201241
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
 
LVL 2

Expert Comment

by:stmontgo
ID: 6202293
pleas select * from nls_session_parameters and paste here. Are you running this on client or server?
0
 

Author Comment

by:kitsoon
ID: 6204696
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
 

Author Comment

by:kitsoon
ID: 6204700
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
 

Author Comment

by:kitsoon
ID: 6205102
hi stmongo I ran the command from client
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:mnicoras
ID: 6205662
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
 
LVL 3

Expert Comment

by:graf27
ID: 6206251
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
 

Expert Comment

by:ksevastiadis
ID: 6452542
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
 

Expert Comment

by:ksevastiadis
ID: 6452543
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
 
LVL 1

Expert Comment

by:Moondancer
ID: 7033784
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
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7046041
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now