Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Charater set Problem

Posted on 2001-06-07
Medium Priority
Last Modified: 2010-05-18
We develop client server application using VB & Oracle. We are using Novell oracle 8i at Our office which we have bought with Novell 5 and one of our client has bought Oracle with Novell. We are using ISM GIST from CDAC for developing software in reginol langauge. Where some extended ASCII charcters are used. The problem at my client site is that some character are changing when saved to database and retreived.
Please suggest Solution.
Question by:Indraneel
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Expert Comment

ID: 6166577
I think you need to set the proper NLS character set in your DB. The problem is that this set is set only once by creating of the DB. Altering after that is a risky action. I took for you the answers from similar question on the Oracle thread and hope this will make you oriented in the problem.

The following is from documentation.  You have to
set proper character set while creating database.


Creating the Database with a Character Set:

The database character encoding scheme is specified by the CHARACTER SET clause of the CREATE DATABASE
statement. The client character encoding scheme is specified by the NLS_LANG parameter. Each user session
can, if required, specify a different encoding scheme. When a client?s character set is different from
the database character set, data conversion is done automatically, and it is transparent to the client

The specification of CHARACTER SET in CREATE DATABASE is an important consideration when creating a
database, since a database character set cannot be changed without re-creating the database. If a number
of clients are to access a database using different character sets, the database character set chosen
should be equivalent to or a superset of all possible client character sets.

Character Conversion

Character set conversion is often necessary in a client/server computing environment where a client
application may be running in a different character set than that of the server. In such cases, client/server
character conversion occurs automatically and transparent to the application.

Conversion is possible between any two character sets and can also be invoked explicitly. In Oracle8,
conversion between different character sets is implemented using Unicode codepoints as an intermediate
form. The source character is first converted to a Unicode codepoint. The Unicode codepoint is converted
to the character in the destination character set. In cases where a target character set does not contain
all characters in the source data, replacement characters are used.

Unicode a Universal Character Set

A character set may support a specific language, a group of related languages, or attempt to encompass
all known languages. The Unicode Standard defines a character repertoire that encompasses all major
scripts of the world, as well as technical symbols in common use. Unicode Version 2.0 contains 38,885
characters from the world?s scripts.

The Unicode character repertoire can be represented in a number of different encoding formats. UCS-2
is a two-byte fixed-width format, UTF-8 is a multi-byte format with variable width. Oracle8 supports
the UTF-8 format only. UTF-8 is an ASCII-compatible encoding scheme. The Oracle character set name for
Unicode 2.0 is "UTF8". Unicode 1.1 has been supported with the Oracle character set name of "AL24UTFFSS"
since Oracle7?.

A typical scenario in which the Unicode character set would be useful is a multinational company with
local databases throughout the world that may want to consolidate data into one central database. In
this case, UTF8 would be an ideal character set to use as a server character set since it is a superset
of all other character sets.

By default it is not UTF8, Western European ISO ...

There are 2 different sets of languages - normal english, French and other characters are single bytes.
Other language characters (chenese etc.) are multibytes.

If you want to store those multibyte characters in your database, you have to define character set as
UTF8 or UTF16 while creating database. Otherwise it can not convert/ store exeisting single byte data
into multibyte.

There is another parameter (system and session specific)NLS_LANG is for how to display which you need
to maintain in your java code. You can set it on browser and display in different format no matter how
it is stored in the database. If you store in UTF8 or UTF16, you can view in any language.

Character set conversion is often necessary in a client/server computing environment where a client
application may reside on a different computer platform from that of the server, and both platforms
may not use the same character encoding schemes. Character data passed between client and server must
be converted between the two encoding schemes. Character conversion occurs automatically and transparently
via Net8.
In order to change unpropriate database character set if it is not 7ASCII you have to recreate database
as follow( or simular)
   LOGFILE 'D:\ora8v2\oradata\ques\redo01.log' SIZE 1024K,
           'D:\ora8v2\oradata\ques\redo02.log' SIZE 1024K,
           'D:\ora8v2\oradata\ques\redo03.log' SIZE 1024K
     DATAFILE 'D:\ora8v2\oradata\ques\system01.dbf' SIZE 98M  REUSE AUTOEXTEND ON NEXT 640K
If initial character set is 7ASCII
you can use
Alter database set character set UTF8 ...
because UTF8 is superset of 7ASCII.

There is a way to change the character set of a database, but it's a somewhat high risk operation.  
Shut the database down.  Then do a startup mount exclusive restrict.  From there, you can issue an alter
database character internal_use UTF8;  This should work.  But (and I cannot emphasize this enough),
you *must* backup your database prior to doing this -- and a cold backup at that.  

Oracle support can probably give you more information about this particular to your situation.  

If you have any possibility to recreate the DB than I would recreate it with utf-8 character set.
I think that jdbc anyway handles the characters in the utf-8 format so you don't need any further conversion.

Expert Comment

ID: 6194763
Hello Indraneel,
I have tried the second methord you mentioned (The high risky option). and got the following errors.

SVRMGR> alter database character SET internal_use UTF8;
alter database character SET internal_use UTF8
ORA-12710: new character set must be a superset of old character set

Does this means I have not selected ASCII? if so how to find out what char set I used at the time of installation


Expert Comment

ID: 6194770
Sorry the reply was for "schwertner"
LVL 54

Expert Comment

ID: 7257252

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.



Accepted Solution

Jgould earned 0 total points
ID: 7282206
Question has been closed as per recommendation

JGould-EE Moderator

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

596 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