1 NAAM Oracle Character sets Aino Andriessen



NAAM
Oracle Character sets
Aino Andriessen

1
Demo1



2
nls_length_semantics


 Intializatie parameter
 CHAR of BYTE (default)
 Van toepassing op multi byte character sets
 Definieert het type voor de lengte van character
kolommen en variabelen
 alter session set nls_length_semantics=CHAR;
 niet met terugwerkende kracht
 ev pl/sql recompile
 alter system

4
nls_length_semantics 2


 lengte van karakter kolommen en variabelen
expliciet opgeven
 create table demo (naam varchar2(4 char))
 create table demo (naam varchar2(4 byte))
 t_naam varchar2(4 char);
 t_naam demo2.naam%TYPE

5
Demo2



6
Character encoding



8
Character set


 Character set definieert de 'mapping' tussen
binary/headecimale code en het character







UTF8
WE8MSWIN1252
WE8ISO8859P1
JA16EUC
US7ASCII
WE8DEC
...
 Code pages
 IBM / windows terminologie
 ~ analoog met character set
 code page per language

9
Character sets 2


 ASCII
 1 byte
 128 karakters
 standaard letters uit het engels zonder accenten
 ISO 8859 en latin-1
 1 byte (8 bit)
 256 karakters
 CP-1252
 Windows variant op latin 1
 UTF8
 variabel, multibyte
 max 4 bytes
 ~100000 karakters
•
~1 miljoen beschikbaar
 meertalig
 ascii codes zijn gelijk

10
Voorbeelden


Character Set
Hexadecimale code - Euro
AL32UTF8
E282AC
WE8MSWIN1252
80
ASCII
-
WE8ISO8859P1
-
WE8ISO8859P15
164
Character Set
Hexadecimale code - é
AL32UTF8
C3A9 (50089)
WE8MSWIN1252
E9 (233)
ASCII
-
WE8ISO8859P1
E9
WE8ISO8859P15
E9

11
Unicode / UTF 8 example


The image shows the number of bytes needed to store different kinds of characters in the
UTF-8 character set. The ASCII characters (C, t, and d) require one byte. The Latin and
Greek characters (á, ö, and Ø) require 2 bytes. The Asian character requires 3 bytes.
The supplementary character (treble clef sign) requires 4 bytes of storage.

12
Diakrieten en speciale tekens


 Diakrieten zijn accenten die bij (boven, onder of
zelfs door) een letter gezet worden om de uitspraak
van een letter te veranderen en daarmee taaleigen
klanken van een (gewijzigde) letter te voorzien.
 àÿęňĜş etc.
 Speciale tekens
 ßæ¿

13
Diakrieten en speciale tekens


 Single byte character sets
 1 byte voor samengesteld karakter
 Niet alle combinaties mogelijk
 code pages
 UTF-8
 diakriet heeft eigen codering
 samengesteld karakter heeft eigen codering
•
meestal (altijd) samenstelling van oorspronkelijke karakter +
diakriet

14
Database functies


 Character functies



substr - substrb - substrc - substr2
instr - ...
length - lengthb
 chr (n)


Returns a character corresponding to the number passed in as the argument in the
database character set
select chr (50089) from dual;
 dump


Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal
representation of expr. The returned result is always in the database character set.
select dump (naam, 1017) from demo2;
 convert

Converts a character string from one character set to another
 utl_raw

select utl_raw.cast_to_raw(naam) from demo2;
 unistr()


Converts the characters in x to the national language character set
select (unistr('Ren\00e9')) from dual;

15
Demo3



16
nls_lang


 Client character set
 When the client NLS_LANG character set is set to
the same value as the database character set,
Oracle assumes that the data being sent or
received are of the same (correct) encoding, so no
conversions or validations may occur for
performance reasons. The data is just stored as
delivered by the client, bit by bit.

18
nls lang 2


 language_country.character set
 american_america.UTF8
 dutch_the netherlands.WE8MSWIN1252
 american_THE NETHERLANDS.WE8MSWIN1252
Environment variable, nls_lang
 Verschil in Windows GUI (WE8MSWIN1252) en
command line (WE8PC850)
 Wordt niet door Java clients gebruikt

19
Demo4



20
National character set


 Support for another character set next to the
database character set
 e.g to allow japanese in a MSWIN1252 or ISO8859
character set
 Less necessary in a UTF8 database
 Multibyte
 nvarchar, nclob etc.

22
Case


 TELETEX karakterset
 bestaat niet meer in Oracle
 select convert(naam,’TELETEX’,’UTF8’) from
tabel;
 Locale builder

23


sql> select name from emp
sql> select name from emp@db
sql> select utl_raw.cast_to_varchar (utl_raw.cast_to_raw
(name)) from emp@db
sql> select utl_raw.cast_to_varchar (utl_raw.cast_to_raw@db
(name)) from emp@db

25
Vraag


 Diacrietloos zoeken
 Case insensitive zoeken

26
Summary


 nls_lenght_semantics
 Always explicitly define a character column with its
type (CHAR or BYTE)
 Oracle performs automatic character set
conversion
 wysinawyg
 Use a Java client
 Working with character sets can be confusing
 UTF8 is often the preferred character set

27
Referenties


 Unicode en Ultraedit
 http://www.ultraedit.com/support/tutorials_power_tips/ultr
aedit/unicode.html
 nls_lang
 http://www.oracle.com/technology/tech/globalization/htdo
cs/nls_lang%20faq.htm
 Oracle globalization support
 http://download.oracle.com/docs/cd/B28359_01/server.1
11/b28298/toc.htm
 Wikipedia

28