Re: National Lang Support

From: Doug Harris <ah513_at_FreeNet.Carleton.CA>
Date: Fri, 25 Feb 1994 02:09:13 GMT
Message-ID: <CLrCnD.2J1_at_freenet.carleton.ca>


In a previous article, jdennis_at_netcom.com (John Dennis) says:

> Trying to get sqlldr to load data with a ‰ (e accent)
> into the database such that I can then go into sqlplus
> and select the data and see the e accent.

   Having gone through a lot of pain with the same problem I think I should post a warning to the entire group:

   If you will be storing 8 bit characters in an Oracle7 Database then you must use the new "CHARACTER SET" clause of the CREATE DATABASE statement. Once a database has been created the character set CAN NOT be changed.

   The default DBMS character set us US7ASCII which does not provide for accented characters. The disturbing part is that it is possible to store 8 bit characters in a 7 bit database and never know there is a problem until it is too late (guess what happened to me?). Do not confuse this with the NLS_LANG environment variable which sets the character set for the client tool (e.g. Forms, Sql*Plus, etc). You have to set them BOTH!

   I currently have a database full of french accented text and the character set is US7ASCII. Everything worked fine until I tried accessing the text from PC's (The default character set for CDE is WE8ISO8859P1) and couldn't get any accents in or out of the database. After exploring I discovered import/export was not preserving accents, and from what John says it looks like SQL*Loader would cause problems to.

   I have resorted to exporting the french data using the Oracle 6.0 export utility (must run the catexp6.sql script), then doing a full DB export with the Oracle7 export, creating a new database (with "CHARACTER SET WE8ISO8859P1"), doing a full import from the 7 export, deleting the contents of the french tables, then reloading them from the 6.0 export file.

   This is definitely going to be a two pizza night when I do this.

   The good news is that once you get everything set up right the new NLS stuff really works well. I have been able to move french data from PC to UNIX to MVS (EBCDIC blech) and the accents and character sets are translated flawlessly (and transparently). I just wish:

  1. The note on this was not relegated to a tiny appendix at the back of the IUG.
  2. The default character set was an 8 bit one (ISO8859 is a superset of 7 bit ascii, there is no excuse for not making it the default).
    • Doug Harris Database Administrator, System Development Division, Statistics Canada ## Standard Disclaimer Applies ## --
Received on Fri Feb 25 1994 - 03:09:13 CET

Original text of this message