6

I am using Oracle 11g Express Edition. Currently when I check NLS character set parameter using SELECT * FROM nls_database_parameters; it gives the default values:

NLS_CHARACTERSET: AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16

I want to set both these parameters to UTF8. How can I do so? I have just installed Oracle 11g XE, so there is not data except those required by Oracle itself.

Géry Ogam
  • 6,336
  • 4
  • 38
  • 67
Parth Bhagat
  • 509
  • 1
  • 11
  • 24

4 Answers4

8

This worked for me where an application was checking for UTF8 rather than AL32UTF8 .

SQL> shutdown immediate;

SQL> startup restrict

SQL> select name from v$database;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8 ;

SQL> select value from NLS_DATABASE_PARAMETERS 
     where parameter=’NLS_CHARACTERSET’;

SQL> shutdown immediate;

SQL> startup
Dharmesh Porwal
  • 1,406
  • 2
  • 12
  • 21
Gary
  • 99
  • 1
  • 1
  • EM login page - corrupted – Velmurugan Mar 02 '18 at 17:00
  • Your answer was very helpful for me. I was trying to change default charset in dockerized Oracle XE. It is possible if sequence of commands `SHUTDOWN IMMEDIATE; STARTUP RESTRICT; ALTER DATABASE CHARACTER SET INTERNAL_USE EE8ISO8859P2; SHUTDOWN IMMEDIATE; STARTUP;` is inserted before this line: https://github.com/oracle/docker-images/blob/25dae6127a6cd1aa233e2ee98db5f4da914f6bf8/OracleDatabase/SingleInstance/dockerfiles/11.2.0.2/runOracle.sh#L142 – Tomáš Záluský Mar 05 '21 at 15:28
3

Maybe this works: alter database character set UTF8; --> Not supported anymore on Oracle 10.1 or later.

However, are you really sure to change it? AL32UTF8 is UTF-8, actually.

On Oracle you have also a character set called UTF8, this is a kind of misnomer and means Character set CESU-8. As far as I know this is like "UTF-8 as of Unicode version 3 from 1999". As long as you use Unicode characters less than 65535 (Basic Multilingual Plane) AL32UTF8 and UTF8 are identical.

Note, this command works only if the new character set is a strict superset of old character set. I.e. you can change from US7ASCII to AL32UTF8 or WE8ISO8859P1 but you cannot change from WE8ISO8859P1 to AL32UTF8.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • 1
    I've tried applying mentioned command, it gives following error. `SQL Error: ORA-12712: new character set must be a superset of old character set` I am agree that Characterset should not be changed. But the application I am installing require `NLS_CHARACTERSET` and `NLS_NCHAR_CHARACTERSET` set to `UTF8`. – Parth Bhagat May 21 '14 at 18:35
  • I doupt that any application may have such a requirement - unless it uses some **very** special characters. – Wernfried Domscheit May 21 '14 at 19:52
  • I can understand that. Actually I am not much aware about Oracle database. If you say that if `AL32UTF8` is `UTF8` that is clear as the name suggest, but for `NLS_NCHAR_CHARACTERSET` it is having value as `AL16UTF16`, so it does means that it is `UTF16` Characterset. How to change that parameter to UTF8? – Parth Bhagat May 22 '14 at 17:04
  • 2
    Yes, it means UTF-16. `NLS_NCHAR_CHARACTERSET` is used for datatype `NVARCHAR2`, resp `NCHAR`. It was useful in earlier times before Unicode was invented. Today there is actually no reason anymore to use one of these datatypes. – Wernfried Domscheit May 22 '14 at 17:32
  • Having NLS_CHARACTERSET set to AL32UTF8 is not the same as UTF8. For example: you cannot connect into AL32UTF8 database with old client (like Oracle Forms 6 runtime), while you can connect to UTF8 database. . – LiborStefek Aug 16 '16 at 07:23
0

You can't change the character set once you set installation. If you want to change then there is long procedure for which you need to raise SR with Oracle.

Rosel
  • 11
  • 1
-3
SQL> shutdown immediate;

SQL> startup restrict

SQL> select name from v$database;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252 ;

SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

SQL> shutdown immediate;

SQL> startup

SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;
Kampai
  • 22,848
  • 21
  • 95
  • 95
user4273977
  • 1