Multilingual Option in Oracle (NLS)

Here are some often asked questions about Oracle NLS.

Show characterset the database was initially built ?

column name format a25
column VALUE$ format a35

SELECT name,value$ FROM sys.props$;

NAME                      VALUE$
------------------------- -----------------------------------
DICT.BASE                 2
NLS_LANGUAGE              AMERICAN
NLS_TERRITORY             AMERICA
NLS_CURRENCY              $
NLS_ISO_CURRENCY          AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CHARACTERSET          UTF8
NLS_CALENDAR              GREGORIAN
NLS_DATE_FORMAT           DD-MON-YY
NLS_DATE_LANGUAGE         AMERICAN
NLS_SORT                  BINARY
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
NLS_TIMESTAMP_TZ_FORMAT   DD-MON-YY HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY         $
NLS_COMP
NLS_NCHAR_CHARACTERSET    UTF8
NLS_RDBMS_VERSION         8.1.5.0.0
GLOBAL_DB_NAME            ARK1.WORLD
EXPORT_VIEWS_VERSION      7

Show valid NLS parameters (TERRITORY, CHARACTERSET)

COLUMN parameter format A15
COLUMN value format A15

SELECT parameter,value
  FROM v$nls_valid_values
 WHERE parameter = 'CHARACTERSET'
ORDER BY value;

Uses for the National Character Set

When you create the database, you the possibility to create an alternate character set, the so called national character set. In the following example, the default character set is UTF8 and the national character set is WE8ISO8859P1. The national character set is for example used in Eastern Europe countries, where we want to support the national language besides the "official" language.

CREATE DATABASE ARK1
  CONTROLFILE     REUSE
  MAXDATAFILES    256
  MAXINSTANCES    4
  MAXLOGFILES     62
  MAXLOGMEMBERS   5
  MAXLOGHISTORY   1600
  CHARACTER SET   "UTF8"
  NATIONAL CHARACTER SET   "WE8ISO8859P1"
  DATAFILE 'E:\Oradata\ARK1\sys\ARK1_sys1.dbf' SIZE 100M REUSE
  AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
  LOGFILE
   GROUP 1
    ('E:\Oradata\ARK1\rdo\ARK1_log1A.rdo',
     'E:\Oradata\ARK1\rdo\ARK1_log1B.rdo') SIZE 15M REUSE,
   GROUP 2
    ('E:\Oradata\ARK1\rdo\ARK1_log2A.rdo',
     'E:\Oradata\ARK1\rdo\ARK1_log2B.rdo') SIZE 15M REUSE;

Oracle uses the default database character set for these items:

• Identifiers such as table names, column names

• Data stored in CHAR, VARCHAR2, CLOB and LONG columns

• Entering and storing SQL and PL/SQL program source

Oracle uses the national character set for these items

• Data stored in NCHAR, NVARCHAR2 and NCLOB columns

How to change the character set in Oracle 8i ?

Under certain circumstances you have the possibility to change the database or national character set in Oracle 8i. The current character set must be a strict subset of the character set to which you change. That is, each character represented by a codepoint value in the source character set must be represented by the same codepoint value in the target character set. The following example changes to character set from WE8ISO8859P1 to UTF8.

SVRMGR> connect sys as sysdba;
SVRMGR> shutdown immediate
SVRMGR> startup mount
SVRMGR> alter system enable restricted session;
SVRMGR> alter system set job_queue_processes = 0;
SVRMGR> alter database open;
SVRMGR> alter database character set utf8;
SVRMGR> alter system disable restricted session;