Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Storing Chinese in Oracle Database

user513543Dec 8 2006 — edited Dec 9 2006
Hi all,

I have a doubt in storing Chinese fonts in my oracle database.

The server setup is as follows.

OS- windows 2000 server
RDBMS - ORACLE 8I (8.1.7)

nls_parameters -

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET WE8ISO8859P1


The CLIENT setup is as follows:

OS - windows XP/2000 professional (English version)


The main requirement is,only two fields or some specific fields of a table should allow chinese fonts to get stored in the oracle database.

i need to know what are the changes i have to do on server side such as changing the CHARACTERSET , NLS_NCHAR_CHARACTERSET parameters and on the client side such as setting nls_char etc...



Thanks & regards,

Ashok Kumar.G

Comments

343896
1)Apply Oracle Database Lite 10gr2(10.2.0.1.0) one-off patch for Bug 4636973 uploaded to metalink. You can download this patch using bug no: 4860790.


You must also make sure you complete these steps in order for UTF8 to work

2) in polite.ini:
DB_CHAR_ENCODING=UTF8
DB_ENCODING=UTF8

3) in webtogo.ora
JAVA_OPTION=-Djava.compiler=NONE -Dfile.encoding=UTF8

NOTE: If you do not add this to java_options, some default system char encoding such as CP1252 might corrupt data.

4) Make sure olilutf8.dll file is copied onto client - otherwise webtogo won't start with UTF8 specified as char encodings in polite.ini.
user513543
hi,

thanks for your immediate response..

but i want to know is there is any other way to store chinese character in oracle 8i (8.1.7) data base with oracle 9i lite..( since we do not want to upgrade our database to 10g R2 right now)....

Regards ,

Ashok Kumar.G
343896
Hi Ashok,

You are working with a couple of de-supported products so an upgrade may be required sooner rather then later.

I haven't worked with multilingual character sets in earlier versions of 9i Lite or 8i so I can't guarantee any of it will work.

But in the meantime, look at this document for setting up Oracle 8.1.7

3. CHANGING THE DATABASE OR THE NATIONAL CHARACTER SET
======================================================

Oracle8(i) introduces a new documented method of changing the database
and national character sets. The method uses two SQL statements, which
are described in the Oracle8i National Language Support Guide:

ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>
ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>

The database name is optional. The character set name should be specified
without quotes, for example:

ALTER DATABASE CHARACTER SET WE8ISO8859P1

To change the database character set perform the following steps.
Note that some of them have been erroneously omitted from the Oracle8i
documentation:

1. Use the Character Set Scanner utility to verify that your database
contains only valid character codes -- see "2. USING THE CHARACTER SET
SCANNER" above.

2. If you go from a 7/8bit characterset to varying width character set
(like UTF8, AL32UTF8, JA16EUC, JA16SJIS, ZHT16BIG5, ZHS16GBK, KO16KSC5601...)
then prepare the CLOB columns for the character set change
-- see "4. HANDLING CLOB AND NCLOB COLUMNS" below.
Omitting this step can lead to corrupted CLOB/NCLOB values
in the database.

If SYS.METASTYLESHEET (STYLESHEET) is populated (9i and up only)
then see Note 213015.1 "SYS.METASTYLESHEET marked as having convertible
data (ORA-12716 when trying to convert character set)" for the actions
that need to be taken.

3. Make sure the parallel_server parameter in INIT.ORA is set to false
or it is not set at all.

If you are using RAC see
Note 221646.1 Changing the Character Set for a RAC Database Fails with an ORA-12720 Error

4. For Oracle 9 and up, make sure you are connected "AS SYSDBA" in sqlplus.
For Oracle 8/8i, make sure you are connected as INTERNAL in svrmgrl.
Then follow these steps:

SHUTDOWN IMMEDIATE;
-- make sure there is a database backup you can rely on, or create one
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET <new_character_set>;
-- a alter database takes typically only a few minutes or less,
-- it depends on the number of columns in the database, not the
-- amount of data.
SHUTDOWN;
-- If you use Oracle8 then also do:
STARTUP RESTRICT;
SHUTDOWN;
The extra restart/shutdown is necessary in Oracle8(i) because of a SGA
initialization bug which is fixed in Oracle9i.
5. Restore the parallel_server parameter in INIT.ORA, if necessary.

6. Restart the database:

STARTUP;

7. If necessary, restore CLOB columns -- see "4. HANDLING CLOB AND NCLOB
COLUMNS" below.

To change the national character set replace the ALTER DATABASE CHARACTER SET
statement with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both
statements together if you wish.


Error Conditions
----------------

A number of error conditions may be reported when trying to change the database
or national character set.

In Oracle8(i) the ALTER DATABASE [NATIONAL] CHARACTER SET statement will return:

ORA-01679: database must be mounted EXCLUSIVE and not open to activate

- if you do not enable restricted session
- if you startup the instance in PARALLEL/SHARED mode
- if you do not set the number of queue processes to 0
- if you do not set the number of AQ time manager processes to 0
- if anybody is logged in apart from you.

This error message is misleading. The command requires the database to be
open but only one session, the one executing the command, is allowed.
For the above error conditions Oracle9i will report one of the errors:

ORA-12719: operation requires database is in RESTRICTED mode
ORA-12720: operation requires database is in EXCLUSIVE mode
ORA-12721: operation cannot execute when other sessions are active

Oracle9i can also report:

ORA-12718: operation requires connection as SYS

if you are not connect as SYS (INTERNAL, "/ AS SYSDBA").

If the specified new character set name is not recognized, Oracle will report
one of the errors:

ORA-24329: invalid character set identifier
ORA-12714: invalid national character set specified
ORA-12715: invalid character set specified

The ALTER DATABASE [NATIONAL] CHARACTER SET command will only work if
the old character set is considered a binary subset of the new character set.
Oracle Server 8.0.3 to 8.1.5 recognizes US7ASCII as the binary subset of
all ASCII-based character sets. It also treats each character set as
a binary subset of itself. No other combinations are recognized.
Newer Oracle Server versions recognize additional subset/superset
combinations, which are listed in Note 119164.1.

If the old character set is not recognized as a binary subset of
the new character set, the ALTER DATABASE [NATIONAL] CHARACTER SET
statement will return:

- in Oracle 8.1.5 and above:

ORA-12712: new character set must be a superset of old character set

- in Oracle 8.0.5 and 8.0.6:

ORA-12710: new character set must be a superset of old character set

- in Oracle 8.0.3 and 8.0.4:

ORA-24329: invalid character set identifier

You will also get these errors if you try to change the characterset of a
US7ASCII database that was started without a (correct) ORA_NLSxx parameter.
See Note 77442.1

It may be necessary to switch off the superset check to allow changes
between formally incompatible character sets to solve certain character set
problems or to speed up migration of huge databases. Oracle Support Services
may pass the necessary information to customers after verifying the safety
of the change for the customers' environments.

If in Oracle9i an ALTER DATABASE NATIONAL CHARACTER SET is issued and
there are N-type colums who contain data then this error is returned:
ORA-12717:Cannot ALTER DATABASE NATIONAL CHARACTER SET when NCLOB data exists
The error only speaks about Nclob but Nchar and Nvarchar2 are also checked.
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 6 2007
Added on Dec 8 2006
#database-key-features, #database-mobile-server-inc-legacy-database-lite
3 comments
4,897 views