Oracle8i NLS ガイド, リリース8.1

Transcription

1 Oracle8i NLS J

2 Oracle8i NLS, 8.1 J National Language Support Guide, Release 2 (8.1.6) A Paul Lane Winson Chu Jason Durbin Jessica Fan Yu Gong Josef Hasenberger Claire Ho Peter Linsley Tom Portfolio Den Raphaely Linus Tanaka Makoto Tozawa Gail Yamanaka Michael Yau Hiro Yoshioka Sergiusz Wolicki Simon Wong Valarie Moore Copyright 1996, 1999, Oracle Corporation. All rights reserved. Printed in Japan. * Oracle Corporation Oracle Corporation Restricted Rights Legend Restricted Rights Legend Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication and disclosure of the Programs shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication and disclosure of the Programs shall be subject to the restrictions in FAR , Rights in Data -- General, including Alternate III (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA

3 ... xi... xi... xi... xi... xii... xii Oracle8i NLS... xii... xiii 1 Oracle NLS Oracle Server NLS / SQL i

4 2 NLS NLS NLS_LANG NLS_LANG NLS_LANG NLS NLS NLS OCI NLS_DATE_FORMAT NLS_DATE_LANGUAGE NLS_CALENDAR NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_DUAL_CURRENCY NLS_MONETARY_CHARACTERS NLS_CREDIT NLS_DEBIT NLS_SORT NLS_COMP ii

5 NLS_LIST_SEPARATOR NLS_NCHAR ASCII Oracle Oracle Oracle NCHAR Oracle Unicode 2.1 Private Use Area UDC iii

6 SQL SQL CONVERT SQL NLSSORT / / OCI OCI NLS NLS OCINlsGetInfo OCI_Nls_MaxBufSz NLS OCIMultiByteToWideChar OCIMultiByteInSizeToWideChar OCIWideCharToMultiByte OCIWideCharInSizeToMultiByte OCIWideCharToLower OCIWideCharToUpper OCIWideCharStrcmp OCIWideCharStrncmp OCIWideCharStrcat iv

7 OCIWideCharStrchr OCIWideCharStrcpy OCIWideCharStrlen OCIWideCharStrncat OCIWideCharStrncpy OCIWideCharStrrchr OCIWideCharStrCaseConversion OCIWideCharDisplayLength OCIWideCharMultiByteLength OCIMultiByteStrcmp OCIMultiByteStrncmp OCIMultiByteStrcat OCIMultiByteStrcpy OCIMultiByteStrlen OCIMultiByteStrncat OCIMultiByteStrncpy OCIMultiByteStrnDisplayLength OCIMultiByteStrCaseConversion OCIWideCharIsAlnum OCIWideCharIsAlpha OCIWideCharIsCntrl OCIWideCharIsDigit OCIWideCharIsGraph OCIWideCharIsLower OCIWideCharIsPrint OCIWideCharIsPunct OCIWideCharIsSpace OCIWideCharIsUpper OCIWideCharIsXdigit OCIWideCharIsSingleByte OCICharSetToUnicode v

8 OCIUnicodeToCharSet OCICharSetConversionIsReplacementUsed OCIMessageOpen OCIMessageGet OCIMessageClose LMSGEN Java Oracle8i Java JDBC JDBC JDBC OCI JDBC Thin JDBC oracle.sql.char NLS SQLJ Java Virtual Machine Java CORBA EJB CORBA ORB Enterprise Java Beans Java SQLJ Java SQLJ vi

9 A... A-2... A-3... A-5... A-6... A-7... A-9... A A A A A-24 NLS... A-26 B... B-2... B-2... B-11 NLS Calendar Utility... B B-12 NLS Data Installation Utility... B B B B B-14 NLS Configuration Utility... B B B-18 C NLS... C-2 D 1... D-1 vii

10 ASCII... D-1 DBCS... D-1 EBCDIC... D-1 EUC... D-1 ISO... D-1 ISO D-2 ISO/IEC D-2 ISO... D-2 Latin-1... D-2 NCHAR... D-2 Net8... D-2 NLS... D-3 NLSDATA... D-3 NLSRTL... D-3 SQL*Net... D-3 UCS-2... D-3 UCS2... D-3 UCS4... D-3 UCS UTF Unicode... D-4 Unicode... D-5 Unicode... D-5 UTF D-5 UTF-8... D-5... D-5... D-5... D-6... D-6... D-6... D-6... D-6... D-6... D-6... D-6... D-7... D-7 viii

11 ... D-7... D-7... D-7... D-7... D-7... D-7... D-8... D-8... D-8... D-8... D-8... D-8... D-8... D-9... D-9... D-9... D-9... D-9... D-9... D-9... D D D D D D D D D D D-11 ix

12 x

13 Oracle8i NLS Oracle NLS Oracle8i Oracle8i Enterprise Edition NLS Oracle Server Oracle xi

14 Oracle Oracle8i Oracle Oracle8i Oracle Oracle8i NLS 1 Oracle NLS NLS Oracle NLS 2 NLS Oracle NLS 3 NLS 4 SQL SQL NLS 5 OCI OCI NLS 6 Java Java NLS A Oracle Server xii

15 B NLS C D NLS DEPTNO FOREIGN KEY ROLLBACK_SEGMENTS SQL Oracle Enterprise Manager SQL*Plus INSERT INTO emp (empno, ename) VALUES (1000, 'SMITH'); ALTER TABLESPACE users ADD DATAFILE 'users2.ora' SIZE 50K; xiii

16 ; Oracle SQL / xiv

17 1 Oracle NLS Oracle NLS Oracle Server NLS SQL Oracle NLS 1-1

18 Oracle Server NLS Oracle Server NLS Oracle NLS Oracle Oracle NLS NLS Oracle8i NLS

19 Oracle Server NLS 1-1 NLS ORA_NLS* ORA_NLS Oracle8i ORA_NLS33 UNIX ORA_NLS33 $ORACLE_HOME/ocommon/nls/admin/data Win32 1 ORACLE_HOME 1 Oracle 1-1 NLS 7.2 ORA_NLS 7.3 ORA_NLS ORA_NLS33 Oracle ORA_NLS* ORA_NLS33 NLS Oracle NLS 1-3

20 Oracle Server NLS NLS Oracle NLS / Oracle8i / NLS 2 CHECK Oracle8i NLS Oracle SQL*Plus Oracle UNIX RDBMS Oracle ORACLE_SID SQL*Plus NLS NLS NLS Oracle Developer OCI NLS OCI OCI NLS 5 OCI NLS NLS ALTER SESSION ALTER SESSION NLS NLS NLS SQL PL/SQL 1-4 Oracle8i NLS

21 NLS_LANG ALTER SESSION NLS 2 NLS Oracle Oracle8i Oracle8i Oracle Unicode UTF8 Oracle8i Oracle8i Oracle 1-2 Oracle NLS 1-5

22 1-2 American English * English Italian * Russian * Arabic * Estonian Japanese * Simplified Chinese * Bengali Finnish * Korean * Slovak * Brazilian Portuguese * French * Latin American Spanish * Slovenian Bulgarian German * Latvian Spanish * Canadian French German Din Lithuanian Swedish * Catalan * Greek * Malay Tamil Croatian Hebrew * Mexican Spanish Thai Czech * Hindi Norwegian * Traditional Chinese * Danish * Hungarian * Polish * Turkish * Dutch * Icelandic Portuguese * Ukrainian Egyptian Indonesian Romanian * Vietnamese Oracle A-2 A-3 Oracle8i 1-3 Algeria Estonia Latvia Slovenia America Finland LEBANON Somalia Austria France LIBYA South Africa Australia Germany Lithuania Spain Bahrain Greece Luxembourg Sudan Bangladesh Hong Kong Malaysia Sweden Belgium Hungary Mauritania Switzerland 1-6 Oracle8i NLS

23 1-3 Brazil Iceland Mexico Syria Bulgaria India Morocco Taiwan Canada Indonesia New Zealand Thailand Catalonia Iraq Norway The Netherlands China Ireland Oman Tunisia CIS Israel Poland Turkey Croatia Italy Portugal Ukraine Cyprus Japan Qatar United Arab Emirates Czech Republic Jordan Romania United Kingdom Denmark Kazakhstan Saudi Arabia Uzbekistan Djibouti Korea SINGAPORE Vietnam Egypt Kuwait Slovakia Yemen 3 Gregorian Japanese Imperial ROC Official Thai BuddhaPersianEnglish Hijrah Arabic Hijrah A-22 Oracle8i / Oracle NLS 1-7

24 ARABIC -- ARABIC_MATCH -- ARABIC_ABJ_SORT -- ARABIC_ABJ_MATCH -- ASCII7 -- BENGALI -- BULGARIAN -- CANADIAN FRENCH -- CATALAN XCATALAN æ AE ß CROATIAN XCROATIAN D L N d l n ß CZECH XCZECH ch CH Ch ß DANISH XDANISH A ß Å å DUTCH XDUTCH ij IJ EEC_EURO -- EEC_EUROPA3 -- ESTONIAN -- FINNISH -- FRENCH XFRENCH GERMAN XGERMAN ß GERMAN_DIN XGERMAN_DIN ß ä ö ü Ä Ö Ü GREEK -- HEBREW -- HUNGARIAN XHUNGARIAN cs gy ny sz ty zs ß CS Cs GY Gy NY Ny SZ Sz TY Ty ZS Zs ICELANDIC Oracle8i NLS

25 1-4 INDONESIAN -- ITALIAN -- JAPANESE -- LATIN -- LATVIAN -- LITHUANIAN -- MALAY -- NORWEGIAN -- POLISH -- PUNCTUATION XPUNCTUATION ROMANIAN -- RUSSIAN -- SLOVAK XSLOVAK dz DZ Dz ß caron SLOVENIAN XSLOVENIAN ß SPANISH XSPANISH ch ll CH Ch LL Ll SWEDISH -- SWISS XSWISS ß THAI_DICTIONARY -- THAI_TELEPHONE -- TURKISH XTURKISH æ AE ß UKRAINIAN -- UNICODE_BINARY VIETNAMESE -- WEST_EUROPEAN XWEST_EUROPEAN ß Oracle A-6 Oracle NLS 1-9

26 Oracle SQL Unicode Private Use Area B-2 B-11 NLS SQL SQL NLS 4 SQL 2 NLS 1-10 Oracle8i NLS

27 2 NLS NLS NLS NLS_LANG NLS NLS 2-1

28 NLS NLS NLS NLS 4 1. NLS NLS_TERRITORY = "CZECH REPUBLIC" 2. NLS UNIX % setenv NLS_SORT FRENCH 3. ALTER SESSION ALTER SESSION NLS SQL> ALTER SESSION SET NLS_SORT = FRENCH; ALTER SESSION Oracle8i SQL 4. SQL NLS SQL NLS ALTER SESSION TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH') CREATE DATABASE CREATE DATABASE Oracle8i SQL 2-1 NLS SQL NLS ALTER SESSION 2-2 Oracle8i NLS

29 NLS SQL 2 ALTER SESSION Oracle Server NLS 2-2 I=INIT.ORA E= A=ALTER SESSION NLS_CALENDAR Gregorian I E A NLS_COMP SQL Binary I E A NLS_CREDIT NLS_TERRITORY - E - NLS_CURRENCY NLS_TERRITORY I E A NLS_DATE_FORMAT NLS_TERRITORY I E A NLS_DATE_LANGUAGE NLS_LANGUAGE I E A NLS_DEBIT NLS_TERRITORY - E - NLS_ISO_CURRENCY ISO NLS_TERRITORY I E A NLS_LANG American_ - E - America.US7ASCII NLS_LANGUAGE NLS_LANG I - A NLS_MONETARY_ CHARACTERS NLS_TERRITORY NLS_LIST_SEPARATOR NLS_TERRITORY - E - - E - NLS_NCHAR NLS_LANG - E - NLS 2-3

30 NLS_LANG 2-2 NLS_NUMERIC_CHARACTERS NLS_TERRITORY I E A NLS_SORT NLS_LANGUAGE I E A NLS_TERRITORY NLS_LANG I - A NLS_DUAL_CURRENCY 2 NLS_TERRITORY I E A I=INIT.ORA E= A=ALTER SESSION NLS_LANG NLS_LANG language territory characterset NLS_LANG 3 language territory characterset NLS_LANG = language_territory.characterset NLS language territory Oracle American French Germanlanguage territory characterset territory characterset language American language A territory America France Canada territory language territory A 2-4 Oracle8i NLS

31 NLS_LANG characterset characterset characterset US7ASCII WE8ISO8859P1 WE8DEC WE8EBCDIC500 JA16EUC language characterset A : NLS_LANG territory characterset territory _ characterset. language NLS_LANG 3 NLS_LANG = AMERICAN_AMERICA.US7ASCII NLS_LANG = FRENCH_CANADA.WE8DEC NLS_LANG NLS_LANG NLS_LANG = JAPANESE_JAPAN.JA16EUC characterset NLS_LANG = JAPANESE_JAPAN.WE8DEC WE8DEC NLS_LANG UNIX NLS_LANG % setenv NLS_LANG FRENCH_FRANCE.WE8DEC NLS_LANG NLS_LANG NLS 2-5

32 NLS_LANG NLS_LANG % setenv NLS_LANG American_America.WE8ISO8859P1 SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL Clark 09-DEC Miller 23-MAR Strauß 01-APR NLS_LANG language territory characterset 8 ISO % setenv NLS_LANG French_France.WE8ISO8859P1 SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL Clark 09/12/ ,83 Miller 23/03/ ,67 Strauß 01/04/ ,87 NLS_LANG SQL Oracle Tools language territory language NLS_LANG language territory NLS language territory NLS NLS_LANG NLS_LANG NLS_LANGUAGE NLS_TERRRITORY NLS 2-6 Oracle8i NLS

33 NLS NLS ALTER SESSION NLS_LANGUAGE NLS_TERRITORY NLS ALTER SESSION NLS SQL*Plus Oracle Developer NLS CREATE DATABASE NLS NLS NLS OCI NLS NLS NLS NLS_SESSION_PARAMETERS NLS NLS_INSTANCE_PARAMETERS NLS INIT.ORA NLS NLS_DATABASE_PARAMETERS NLS V$NLS_VALID_VALUES LANGUAGE SORT TERRITORY CHARACTERSET Oracle8i NLS 2-7

34 NLS OCI NLS Oracle8i OCI OCINlsGetInfo 5 OCI NLS_LANGUAGE NLS_TERRITORY NLS NLS_LANGUAGE : : : : ALTER SESSION NLS_LANG NLS_LANGUAGE SQL TO_CHAR TO_DATE AM PM AD BC ORDER BY ORDER BY GROUP BY / NLS_LANGUAGE NLS_LANGUAGE = FRENCH ORA-00942: table or view does not exist 2-8 Oracle8i NLS

35 NLS ORA-00942: table ou vue inexistante ORA_RDBMS 1 <product_id><language_abbrev>.msb oraf.msb F NLS_LANGUAGE NLS_LANGUAGE Oracle NLS_LANGUAGE SQL> ALTER SESSION SET NLS_LANGUAGE=Italian; SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL Clark 09-Dic Miller 23-Mar Strauß 01-Apr SQL> ALTER SESSION SET NLS_LANGUAGE=German; SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL Clark 09-DEZ Miller 23-MÄR Strauß 01-APR NLS 2-9

36 NLS NLS_TERRITORY : : : : ALTER SESSION NLS_LANG NLS_TERRITORY ISO 2 ISO NLS_TERRITORY NLS_TERRITORY = FRANCE NLS_TERRITORY NLS_TERRITORY NLS_LANG ALTER SESSION NLS_TERRITORY NLS_TERRITORY NLS 2-10 Oracle8i NLS

37 NLS SQL> ALTER SESSION SET NLS_TERRITORY=France; NLS_TERRITORY SQL> describe SalaryTable; Name Null? TYPE SALARY NUMBER SQL> column SALARY format L999,999.99; SQL> SELECT * from SalaryTable; SALARY $100, $150, SQL> ALTER SESSION SET NLS_TERRITORY = Germany; Session altered. SQL> SELECT * from SalaryTable; SALARY DM100, DM150, SQL> ALTER SESSION SET NLS_LANGUAGE = German; Sitzung wurde geändert. SQL> SELECT * from SalaryTable; SALARY DM100, DM150, SQL> ALTER SESSION SET NLS_TERRITORY = France; Sitzung wurde geändert. SQL> SELECT * from SalaryTable; SALARY F100, F150, NLS 2-11

38 NLS SQL*Plus ALTER SESSION ALTER SESSION % setenv NLS_LANG Italian_Italy.WE8DEC SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL Clark 09-Dic ,83 Miller 23-Mar ,67 Strauß 01-Apr ,87 SQL> ALTER SESSION SET NLS_LANGUAGE = German 2> NLS_DATE_FORMAT = 'DD.MON.YY' 3> NLS_NUMERIC_CHARACTERS = '.,'; SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL Clark 09.DEZ Miller 23.MÄR Strauß 01.APR NLS_LANGUAGE NLS_TERRITORY NLS_LANG language territory ALTER SESSION NLS_LANG ALTER SESSION NLS_LANG ALTER SESSION NLS ALTER SESSION 2-12 Oracle8i NLS

39 Oracle Developer 3 Oracle Forms 3 NLS Estonia hh24:mi:ss 13:50:23 Germany hh24:mi:ss 13:50:23 Japan hh24:mi:ss 13:50:23 UK hh24:mi:ss 13:50:23 US hh:mi:ss am PM Oracle Estonia dd.mm.yyyy Germany dd-mm-rr NLS 2-13

40 2-4 Japan rr-mm-dd UK dd-mon-rr 28-Feb-98 US dd-mon-rr 28-Feb-98 NLS_DATE_FORMAT : : : : ALTER SESSION NLS_TERRITORY TO_CHAR TO_DATE NLS_TERRITORY NLS_DATE_FORMAT = "MM/DD/YYYY" NLS_DATE_FORMAT = '\"Today\'s date\" MM/DD/YYYY' NLS_DATE_FORMAT = "DD RM YYYY" SELECT SELECT TO_CHAR(SYSDATE) CURRDATE FROM DUAL; CURRDATE II Oracle8i NLS

41 2 1 2 MM/DD/YY : NLS_DATE_FORMAT ALTER SESSION SET NLS_DATE_FORMAT RR 2000 YYYY RRRR NLS_DATE_FORMAT 2000 NLS_DATE_FORMAT RR RR RR RR Oracle8i SQL 4 MM-DD-YYYY 4 DD-MON-YY 11-jan-97 NLS 2-15

42 TO_DATE() 4 TO_DATE('11-jan-1997', 'dd-mon-yyyy') NLS_DATE_FORMAT MM-DD-YY TO_DATE() 4 NLS_DATE_FORMAT 4 TO_DATE() Oracle8i SQL NLS_DATE_LANGUAGE : : : : ALTER SESSION NLS_LANGUAGE TO_CHAR TO_DATE NLS_LANGUAGE NLS_DATE_LANGUAGE NLS_LANGUAGE NLS_DATE_LANGUAGE = FRENCH SQL> SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') > FROM DUAL; Mercredi:12 Février Oracle8i NLS

43 SQL> SELECT TO_CHAR(SYSDATE, 'Dy:dd Mon yyyy') > FROM DUAL; Me:12 Fév 1997 DD-MON-YYYY SQL> INSERT INTO tablename VALUES ('12-Fév-1997'); AM PM AD BC NLS_DATE_LANGUAGE TO_CHAR SQL> SELECT TO_CHAR(TO_DATE('12-Fév'),'Day: ddspth Month') > FROM DUAL; Mercredi: twelfth Février NLS_DATE_LANGUAGE ALTER SESSION SET NLS_DATE_LANGUAGE Oracle 1 NLS 2-17

44 2-5 März 1998 Mo Di Mi Do Fr Sa So Oracle ISO ISO ISO ISO ISO IW Oracle8i NLS

45 2-6 1 January 1998 Mo Tu We Th Fr Sa Su <= <= <= <= <= January 1999 Mo Tu We Th Fr Sa Su <= <= <= <= <= Oracle 6 Japanese Imperial ROC Official Persian 12 Thai Buddha Arabic Hijrah English Hijrah NLS 2-19

46 NLS_CALENDAR : : : : ALTER SESSION Gregorian NLS_CALENDAR Oracle Arabic Hijrah English Hijrah Gregorian Japanese Imperial Persian ROC Official Thai Buddha NLS_CALENDAR Japanese Imperial E YY-MM-DD SYSDATE SELECT SYSDATE FROM DUAL; SYSDATE H Oracle8i NLS

47 Oracle , Estonia ,89 Germany ,89 Japan 1,234, UK 1,234, US 1,234, NLS_NUMERIC_CHARACTERS : : : : ALTER SESSION NLS_TERRITORY 2 NLS_TERRITORY < > NLS 2-21

48 NLS_NUMERIC_CHARACTERS = "<decimal_character><group_separator>" G NLS_NUMERIC_CHARACTERS = ",." : SQL SQL NLS INSERT INTO SIZES (ITEMID, WIDTH, HEIGHT, QUANTITY) VALUES (618, '45,5', 27.86, TO_NUMBER('1.234','9G999')); '45,5' 45.5 WIDTH '1.234' 1234 NLS_NUMERIC_CHARACTERS ",." NLS_NUMERIC_CHARACTERS NLS_NUMERIC_CHARACTERS ALTER SESSION SET NLS_NUMERIC_CHARACTERS NLS_NUMERIC_CHARACTERS Oracle 2-22 Oracle8i NLS

49 Estonia 1 234,56 kr Germany 1.234,56 DM Japan 1, UK 1, US $1, NLS_CURRENCY : : : : ALTER SESSION NLS_TERRITORY 10 NULL L NLS_TERRITORY Dfl NLS_CURRENCY = "Dfl " SQL> SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL" > FROM ORDERS WHERE CUSTNO = 586; TOTAL Dfl ,49 NLS_CURRENCY ALTER SESSION SET NLS_CURRENCY NLS 2-23

50 NLS_ISO_CURRENCY : : : : ALTER SESSION NLS_TERRITORY C ISO NLS_TERRITORY ISO $ ISO ISO USD AUD ISO NLS_ISO_CURRENCY NLS_TERRITORY ISO NLS_ISO_CURRENCY = FRANCE SQL> SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL" > FROM ORDERS WHERE CUSTNO = 586; TOTAL FRF12.673, Oracle8i NLS

51 NLS_ISO_CURRENCY ALTER SESSION SET NLS_ISO_CURRENCY 2-10 ISO 2-10 ISO Estonia Germany Japan UK US ,89 EEK ,89 DEM 1,234, JPY 1,234, GBP 1,234, USD NLS_DUAL_CURRENCY : : : : ALTER SESSION 2 NLS_TERRITORY 2 NLS_DUAL_CURRENCY 2 NLS_DUAL_CURRENCY 2 NLS_DUAL_CURRENCY D8EBCDIC1141 EBCDIC x9F DK8EBCDIC1142 EBCDIC x5A S8EBCDIC1143 EBCDIC x5A I8EBCDIC1144 EBCDIC x9F F8EBCDIC1147 EBCDIC x9F NLS 2-25

52 2-11 WE8PC858 IBM-PC xDF WE8ISO8859P15 ISO xA4 EE8MSWIN1250 MS Windows x80 CL8MSWIN1251 MS Windows / 0x88 WE8MSWIN1252 MS Windows x80 EL8MSWIN1253 MS Windows / 0x80 WE8EBCDIC1140 EBCDIC x9F WE8EBCDIC1140C EBCDIC x9F WE8EBCDIC1145 EBCDIC x9F WE8EBCDIC1146 EBCDIC x9F WE8EBCDIC1148 EBCDIC x9F WE8EBCDIC1148C EBCDIC x9F EL8ISO8859P7 ISO / 0xA4 IW8MSWIN1255 MS Windows / 0x80 AR8MSWIN1256 MS Windows / 0x80 TR8MSWIN1254 MS Windows x80 BLT8MSWIN1257 MS Windows x80 VN8MSWIN1258 MS Windows x80 TH8TISASCII ASCII 8 0x80 AL24UTFFSS Unicode 1.1 UTF-8 U+20AC UTF8 Unicode 2.1 UTF-8 U+20AC UTFE Unicode 2.1 UTF-EBCDIC U+20AC NLS_MONETARY_CHARACTERS : : : : NLS_TERRITORY 2-26 Oracle8i NLS

53 NLS_MONETARY_CHARACTERS $ < > NLS_CREDIT : : : : NLS_TERRITORY 9 NULL NLS_CREDIT NLS_TERRITORY OCIGetNlsInfo NLS_DEBIT : : : : NLS_TERRITORY 9 NULL NLS_DEBIT NLS_TERRITORY OCIGetNlsInfo Oracle NLS 2-27

54 s ß SS 2 SZ 1 ö o œ Oracle ch ll cerveza colorado cheremoya lago luna llama Oracle ASCII EBCDIC A Z ASCII EBCDIC Ä B ORDER BY ABC ABZ BCD ÄBC Oracle Server 2 NLS ORDER BY NLS_SORT A Oracle8i NLS

55 : / UTF8 NLS_SORT SQL> CREATE INDEX nls_index ON my_table (NLSSORT(name, 'NLS_SORT = German')); SQL> SELECT * FROM my_table WHERE NLSSORT(name) IS NOT NULL > ORDER BY name; Oracle8i 1 1 CREATE INDEX french_index ON emp (NLSSORT(emp_name, 'NLS_SORT=FRENCH')); CREATE INDEX german_index ON emp (NLSSORT(emp_name, 'NLS_SORT=GERMAN')); NLS_SORT FRENCH french_index GERMAN german_index NLS 2-29

56 1 QUERY_REWRITE_ENABLED TRUE QUERY_REWRITE_ENABLED ALTER SESSION SET query_rewrite_enabled=true; 2 NLS_COMP ANSI NLS_COMP ALTER SESSION SET NLS_COMP = ANSI; 3 NLS_SORT FRENCH NLS_SORT FRENCH GERMAN NLS_SORT GERMAN NLS_SORT ALTER SESSION NLS_SORT SQL NLS_SORT ALTER SESSION SET NLS_SORT='FRENCH'; 4 column_name ORDER BY column_name WHERE NLSSORT(column_name) IS NOT NULL ORDER BY FRENCH NLS_SORT ALTER SESSION ALTER SESSION SET query_rewrite_enabled=true; ALTER SESSION SET NLS_COMP = ANSI; ALTER SESSION SET NLS_SORT='FRENCH'; CREATE TABLE test(col VARCHAR(20) NOT NULL); CREATE INDEX test_idx ON test(nlssort(col, 'NLS_SORT=FRENCH')); SELECT * FROM test WHERE NLSSORT(col) IS NOT NULL ORDER BY col; SELECT * FROM test WHERE col > 'JJJ'; Oracle8i 2-30 Oracle8i NLS

57 SQL> CREATE INDEX case_insensitive_ind ON my_table(nls_upper(empname)); SQL> SELECT * FROM my_table WHERE NLS_UPPER(empname) = 'KARL'; Oracle8i 1 1 ch ll c d l m 1 s ß SS 2 SZ s ß SS 2 / NLS_UPPER NLS_LOWER NLS_INITCAP UPPER LOWER INITCAP NLS 2-31

58 NLS_SORT : : : : ALTER SESSION NLS_LANGUAGE BINARY NLS_LANGUAGE NLS_SORT NLS_SORT = { BINARY name } BINARY name German NLS_SORT = German : NLS_SORT BINARY ORDER BY NLS_SORT NLS_SORT ORDER BY NLS_SORT ORDER BY NLS_SORT ALTER SESSION SET NLS_SORT A Oracle8i NLS

59 NLS_COMP : : : : ALTER SESSION Binary BINARY ANSI SQL NLS_SORT WHERE NLSSORT NLSSORT NLS_SORT NLS_COMP NLS_SORT SQL> ALTER SESSION SET NLS_COMP = ANSI; WHERE SQL> ALTER SESSION SET NLS_COMP = BINARY; NLS_COMP ANSI SQL> CREATE INDEX i ON t(nlssort(col, 'NLS_SORT=FRENCH')); NLS_LIST_SEPARATOR : : : : NLS_TERRITORY NLS_LIST_SEPARATOR + -< >. NLS 2-33

60 NLS_NCHAR : : : : NLS_LANG NLS_NCHAR NCHAR NVARCHAR2 NCLOB 2-34 Oracle8i NLS

61 3 NLS Oracle Oracle Oracle NCHAR 1 3-1

62 A ASCII! 0x21 # 0x23 $ 0x x x x33 A A 0x41 B B 0x42 C C 0x43 a a 0x61 b b 0x62 c c 0x63 Oracle Oracle Oracle A 3-2 Oracle8i NLS

63

64 0 9 NULL 1 I wrote 32 books skoob 32 etorw I CHAR VARCHAR Oracle 3-4 Oracle8i NLS

65 A Z a z 0 9 % ' ( ) * + -,. / \ : ; < > =! _ & ~ { } ^? $ " [ ] '<space>' '<horizontal tab>' '<vertical tab>' '<form feed>' NCHAR NVARCHAR Oracle CHAR VARCHAR CHR CONVERT CHAR VARCHAR RAW 3-5

66 ASCII ASCII IBM EBCDIC 3-2 ASCII 16 A 0x ASCII NUL DLE SP P ' P 1 SOH DCI! 1 A Q a q 2 STX DC2 " 2 B R b r 3 ETX DC3 # 3 C S c s 4 EOT DC4 $ 4 D T d t 5 ENQ NAK % 5 E U e u 6 ACK SYN & 6 F V f v 7 BEL ETB ' 7 G W g w 8 BS CAN ( 8 H X h x 9 TAB EM ) 9 I Y i y A LF SUB * : J Z j z B VT ESC + ; K [ k { C FF FS, < L \ l D CR GS - = M ] m } E SO RS. > N ^ n ~ F SI US /? O _ o DEL 1 ISO Oracle8i NLS

67 3-3 lso 8859 ISO ISO ISO ISO ISO ISO ISO ISO ISO ISO ISO : 3-7

68 Unicode 1 Unicode 39, ASCII American Standard Code for Information Interchange ISO 8859/1 3-8 Oracle8i NLS

69 n n 2 3-9

70 Oracle Oracle <language_or_region><#_of_bits_representing_a_char><standard_name>[s] [C] [FIXED] UTF8 UTFE US7ASCII U.S. 7 ASCII WE8ISO8859P1 8 ISO 8859 Part 1 JA16SJIS 16 JIS S C S C Macintosh Macintosh EBCDIC S C FIXED Oracle Oracle CHAR VARCHAR2 CLOB LONG PL/SQL SQL PL/SQL Oracle Oracle8i NLS

71 Oracle A Oracle ISO A B B A B "?" ä a "a" 3-11

72 Oracle CHAR VARCHAR2 CHAR(20) Oracle 3-4 JA16EUCFIXED ZHS16GBKFIXED JA16DBCSFIXED KO16DBCSFIXED ZHS16DBCSFIXED JA16SJISFIXED ZHT32TRISFIXED 3-12 Oracle8i NLS

73 Oracle NCHAR 3-4 KO16KSC5601FIXED ZHS16CGB231280FIXED ZHT32EUCFIXED ZHT16BIG5FIXED ZHT16DBCSFIXED Oracle NCHAR NCHAR NVARCHAR2 NCLOB NCHAR NCHAR NVARCHAR2 NCLOB NCHAR NCHAR NCHAR NVARCHAR2 NCLOB 3-13

74 NCHAR NCHAR : SQL NCHAR NCHAR NVARCHAR2 NCLOB NCHAR NCHAR JA16EUC NCHAR(20) 20 JA16EUCFIXED NCHAR(20) 40 SQL LIKE NCHAR CHAR LIKE JA16EUC NCHAR JA16EUCFIXED SQL SQL NCHAR NCHAR 3-14 Oracle8i NLS

75 1 %TYPE 1 NCHAR(10) 10 %TYPE CHAR VARCHAR2 CHAR(20) / 1 o ö 3 1 TH8TISASCII 1 3 UTF

76 NCHAR NVARCHAR2 1 2 Oracle 3-5 Oracle Oracle8i NLS

77 3-5 ASCII EBCDIC Recovery Manager ROLLBACK_SEGMENTS NLS LOB (LOB BLOB CLOB NCLOB) 3-7 CREATE DATABASE CHAR CLOB VARCHAR2 LONG NCHAR NCLOB NVARCHAR2 3-17

78 NLS_LANG NLS_NCHAR CHAR NCHAR BLOB CLOB NCLOB 3-7 ADT 3-7 CHAR NCHAR BLOB CLOB NCLOB 3-18 Oracle8i NLS

79 : BLOB NLS / ALTER DATABASE CHARACTER SET US7ASCII WE8ISO8859P1 ZHS16GBK UTF8 ALTER DATABASE CHARACTER SET 3-8 US7ASCII WE8ISO8859P1 US7ASCII ZHS16GBK US7ASCII UTF8 / ALTER DATABASE [NATIONAL] CHARACTER SET ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>; ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_nchar_character_set>; ALTER DATABASE CHARACTER SET WE8ISO8859P1; 3-19

80 SQL> SHUTDOWN IMMEDIATE; <do a full backup> -- or NORMAL SQL> STARTUP MOUNT; SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE CHARACTER SET <new_character_set_name>; SQL> SHUTDOWN IMMEDIATE; -- or NORMAL SQL> STARTUP; ALTER DATABASE CHARACTER SET ALTER DATABASE NATIONAL CHARACTER SET Oracle8i UDC Oracle UDC Oracle Unicode 2.1 Private Use Area UDC 3-20 Oracle8i NLS

81 1 JIS 1880 UDC 3-9 JIS JIS UDC 0xf040 0xf07e 0xf080 0xf0fc 188 0xf140 0xf17e 0xf180 0xf1fc 188 0xf240 0xf27e 0xf280 0xf2fc 188 0xf340 0xf37e 0xf380 0xf3fc 188 0xf440 0xf47e 0xf480 0xf4fc 188 0xf540 0xf57e 0xf580 0xf5fc 188 0xf640 0xf67e 0xf680 0xf6fc 188 0xf740 0xf77e 0xf780 0xf7fc 188 0xf840 0xf87e 0xf880 0xf8fc 188 0xf940 0xf97e 0xf980 0xf9fc Oracle 3-10 UDC Oracle JA16DBCS 4370 JA16DBCSFIXED 4370 JA16EBCDIC JA16SJIS 1880 JA16SJISFIXED 1880 JA16SJISYEN 1880 KO16DBCS 1880 KO16DBCSFIXED 1880 UDC 3-21

82 3-10 UDC Oracle UDC KO16MSWIN ZHS16DBCS 1880 ZHS16DBCSFIXED 1880 ZHS16GBK 2149 ZHS16GBKFIXED 2149 ZHT16DBCS 6204 ZHT16MSWIN Oracle Unicode JA16SJIS JA16EUC JA16DBCS 0x4E9C 0x889F 0xB0A1 0x4867 Oracle Unicode 2.1 Unicode 2.1 Unicode JA16SJIS JA16EUC JA16SJIS 3-3 0x889F Unicode 0x4E9CJA16EUC 0xB0A Oracle8i NLS

83 1 Unicode 2.1 Private Use Area Unicode 2.1 Private Use Area PUA 0xE000 0xF8FF PUA UDC Unicode 2.1 PUA 2 Oracle UDC UDC ${ORACLE_HOME}/ocommon/nls/demo/udc_ja.txt ${ORACLE_HOME}/ocommon/nls/demo/udc_ko.txt ${ORACLE_HOME}/ocommon/nls/demo/udc_zhs.txt ${ORACLE_HOME}/ocommon/nls/demo/udc_zht.txt UDC JIS IBM UDC JIS 0xF040 IBM 0x6941 JA16SJIS JA16DBCS JIS UDC 0xF040 IBM UDC 0x6941 UDC Unicode PUA 0xE000 B 1 NLS

84 (JA16EUC) Unix (JA16EUC) / Oracle8i NLS

85 1 3-5 (JA16EUC) Unix (JA16EUC) Windows (JA16SJIS) US7ASCII WE8ISO8859P1 ß? ä a WE8ISO8859P1 3-25

86 WE8ISO8859P WE8ISO8859P1 WE8ISO8859P WE8ISO Oracle8i NLS

87 3-6 (WE8ISO8859P1) (WE8ISO8859P1) (WE8DEC) Unicode Unicode UCS2 UTF8 UCS2 2 UTF8 Oracle8i UTF8 UTF8 UTF8 UTF8 A 3-7 Unicode 3-27

88 3-7 (WE8ISO8859P1) (WE8DEC) Unicode (UTF8) (JA16EUC) (JA16SJIS) 3-28 Oracle8i NLS

89 4 SQL NLS SQL SQL / / SQL 4-1

90 SQL SQL NLS SQL NLS TO_CHAR TO_DATE TO_NUMBER NLS_UPPER NLS_LOWER NLS_INITCAP NLSSORT NLS NLS SQL SQL> SELECT ENAME FROM EMP > WHERE HIREDATE > '1-JAN-91'; SQL> SELECT ENAME FROM EMP > WHERE HIREDATE > TO_DATE('1-JAN-91','DD-MON-YY', > 'NLS_DATE_LANGUAGE = AMERICAN'); SQL SQL 4-2 Oracle8i NLS

91 SQL NLS NLS NLS SQL 'parameter = value' NLS NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_SORT 4-1 SQL NLS 4-1 SQL SQL TO_DATE TO_NUMBER TO_CHAR NLS_UPPER NLS_LOWER NLS NLS_DATE_LANGUAGE NLS_CALENDAR NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_DUAL_CURRENCY NLS_ISO_CURRENCY NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_DUAL_CURRENCY NLS_CALENDAR NLS_SORT NLS_SORT SQL 4-3

92 SQL 4-1 SQL SQL NLS_INITCAP NLSSORT NLS NLS_SORT NLS_SORT NLS TO_DATE ('1-JAN-89', 'DD-MON-YY', 'nls_date_language = American') TO_CHAR (hiredate, 'DD/MON/YYYY', 'nls_date_language = French') TO_NUMBER ('13.000,00', '99G999D99', 'nls_numeric_characters = '',.''') TO_CHAR (sal, '9G999D99L', 'nls_numeric_characters = '',.'' nls_currency = '' Dfl''') TO_CHAR (sal, '9G999D99C', 'nls_numeric_characters = ''.,'' nls_iso_currency = Japan') NLS_UPPER (ename, 'nls_sort = Swiss') NLSSORT (ename, 'nls_sort = German') : NLS_UPPER NLS_LOWER NLS_INITCAP NLS_LANGUAGE NLS_TERRITORY NLSSORT SQL NLS NLS_DATE_FORMAT 4-4 Oracle8i NLS

93 SQL CONVERT NLS TO_CHAR TO_NUMBER TO_DATE 2 TO_CHAR (hiredate, 'DD/MON/YYYY', 'nls_date_language = French') TO_CHAR (hiredate, 'nls_date_language = French') TO_CHAR (hiredate, 'nls_date_language = French','DD/MON/YY') NLS TO_CHAR TO_DATE NLS_DATE_FORMAT NLS SQL CONVERT CONVERT CONVERT CONVERT 4-1 CONVERT CONVERT ( char, dest_char_set, source_char_set ) source_char_set dest_char_set SQL 4-5

94 SQL / CONVERT TRANSLATE (...USING...) TRANSLATE CONVERT Oracle8i SQL SQL ID 2 SQL NLS_CHARSET_NAME NLS_CHARSET_ID OCI ID NLS_CHARSET_DECL_LEN NCHAR Oracle8i SQL NLS_CHARSET_NAME(n) ID n n ID NULL NLS_CHARSET_ID(TEXT) TEXT ID TEXT VARCHAR2 TEXT NLSRTL CHAR_CS TEXT ID NCHAR_CS TEXT ID TEXT NULL TEXT NCHAR NLS_CHARSET_DECL_LEN(BYTECNT, CSID) NCHAR BYTECNT NCHAR CSID NCHAR ID 4-6 Oracle8i NLS

95 SQL NLSSORT NLSSORT 2 NLSSORT 2 SQL ORDER BY NLS_SORT NLSSORT() SQL> ALTER SESSION SET NLS_SORT = GERMAN; > SELECT * > FROM table1 > ORDER BY col1; SQL> ALTER SESSION SET NLS_SORT = GERMAN; > SELECT * > FROM table1 > ORDER BY NLSSORT(col1, 'NLS_SORT = FRENCH'); WHERE 2 1. WHERE NLSSORT() SQL> SELECT * > FROM table1 > WHERE NLSSORT(col1, 'NLS_SORT = FRENCH')> > NLSSORT(col2, 'NLS_SORT = FRENCH'); 2. WHERE NLS_SORT NLS_COMP ANSI SQL> ALTER SESSION SET NLS_COMP = ANSI; NLSSORT NLSSORT 4 SQL 4-7

96 SQL NLSSORT() NLS_SORT NLSSORT( 1, 'NLS_SORT=xxxx') NLSSORT( 1, 'NLS_LANG= xxxx') NLSSORT( 1, 'NLS_LANGUAGE=xxxx') NLSSORT NLS_LANG NLS_LANG NLSSORT NLS_LANG US PL SQL> SELECT * FROM emps > ORDER BY NLSSORT(col1, 'NLS_LANG=PL'); WHERE NLSSORT WHERE COL1 ISO 8859/1 8 ABC ABZ BCD ÄBC BCD ÄBC SQL> SELECT col1 FROM tab1 WHERE col1 > 'B'; Ä B Ä B Ä Z WHERE NLSSORT WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string) NLSSORT SELECT col1 FROM tab1 WHERE NLSSORT(col1) > NLSSORT('B') Ä Ä B Ä Z 4-8 Oracle8i NLS

97 SQL NLS_COMP WHERE NLSSORT NLSSORT NLS_SORT NLS_COMP NLS_SORT SQL> ALTER SESSION SET NLS_COMP = ANSI; WHERE SQL> ALTER SESSION SET NLS_COMP = BINARY; NLS_COMP ANSI SQL> CREATE INDEX i ON t(nlssort(col, 'NLS_SORT=FRENCH')); DDL DML VALUES LESS THAN BINARY ORDER BY ORDER BY NLSSORT ORDER BY NLSSORT ORDER BY NLSSORT NLSSORT 1 2 NLSSORT ORDER BY BINARY SELECT ename FROM emp ORDER BY ename SELECT ename FROM emp ORDER BY NLSSORT(ename, 'NLS_SORT = GERMAN') SQL 4-9

98 / / LIKE _% 2 CHAR LIKE NCHAR NVARCHAR2 NCHAR JA16SJISFIXED 0x8151 0x8193 0x8140 JA16EUCFIXED 0xa1b2 0xa1f3 0xa1a1 JA16DBCSFIXED 0x426d 0x426c 0x4040 ZHT32TRISFIXED 0x8eb1a1df 0x8eb1a1a5 0x8ebla1a0 / / TO_CHAR TO_DATE TO_NUMBER RM Roman MonthRM rm DD-rm-YYYY 07-ix-1998 DD-RM-YYYY 07-IX-1998 MON DY 3 Lundi Mardi Lu Ma 4-10 Oracle8i NLS

99 WW int((day-ijan1)/7) ISO ISO ISO IW Y YY YYY YYYY I IY IYY IYYY ISO ISO ISO ISO ISO ISO IW IY D Decimal G Group L Local Currency C International Currency ISO RN Roman Numeral RN rn Oracle8i SQL SQL 4-11

100 ASCII 124 SQL SQL D7DEC F7DEC SF7ASCII Oracle8i NLS

101 5 OCI OCI OCI NLS NLS OCI 5-1

102 OCI NLS OCI NLS OCI NLS OCI NLS NLS ALTER SESSION OCISessionBegin OCI ALTER SESSION NLS OCI NLS SELECT NLS Oracle Yes/No AM/PM AD/BC / 5-2 Oracle8i NLS

103 NLS OCINlsGetInfo sword OCINlsGetInfo(dvoid *hndl, OCIError *errhp, OraText *buf, size_t buflen, ub2 item) item OCI hndl buflen buf OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-1 OCINlsGetInfo / / hndl IN/OUT errhp IN/OUT buf OUT OCI OCI errhp NULL OCIErrorGet() OCI 5-3

104 NLS 5-1 OCINlsGetInfo / / buflen IN item IN OCI_NLS_MAXBUFSZ OCI OCI_NLS_DAYNAME1: Monday OCI_NLS_DAYNAME2: Tuesday OCI_NLS_DAYNAME3: Wednesday OCI_NLS_DAYNAME4: Thursday OCI_NLS_DAYNAME5: Friday OCI_NLS_DAYNAME6: Saturday OCI_NLS_DAYNAME7: Sunday OCI_NLS_ABDAYNAME1: Monday OCI_NLS_ABDAYNAME2: Tuesday OCI_NLS_ABDAYNAME3: Wednesday OCI_NLS_ABDAYNAME4: Thursday OCI_NLS_ABDAYNAME5: Friday OCI_NLS_ABDAYNAME6: Saturday OCI_NLS_ABDAYNAME7: Sunday 5-4 Oracle8i NLS

105 NLS 5-1 OCINlsGetInfo / / OCI_NLS_MONTHNAME1: January OCI_NLS_MONTHNAME2: February OCI_NLS_MONTHNAME3: March OCI_NLS_MONTHNAME4: April OCI_NLS_MONTHNAME5: May OCI_NLS_MONTHNAME6: June OCI_NLS_MONTHNAME7: July OCI_NLS_MONTHNAME8: August OCI_NLS_MONTHNAME9: September OCI_NLS_MONTHNAME10: October OCI_NLS_MONTHNAME11: November OCI_NLS_MONTHNAME12: December OCI_NLS_ABMONTHNAME1: January OCI_NLS_ABMONTHNAME2: February OCI_NLS_ABMONTHNAME3: March OCI_NLS_ABMONTHNAME4: April OCI_NLS_ABMONTHNAME5: May OCI_NLS_ABMONTHNAME6: June OCI_NLS_ABMONTHNAME7: July OCI_NLS_ABMONTHNAME8: August OCI_NLS_ABMONTHNAME9: September OCI_NLS_ABMONTHNAME10: October OCI_NLS_ABMONTHNAME11: November OCI_NLS_ABMONTHNAME12: December OCI 5-5

106 NLS 5-1 OCINlsGetInfo / / OCI_NLS_YES: OCI_NLS_NO: OCI_NLS_AM: AM OCI_NLS_PM: PM OCI_NLS_AD: AD OCI_NLS_BC: BC OCI_NLS_DECIMAL: OCI_NLS_GROUP: OCI_NLS_DEBIT: OCI_NLS_CREDIT: OCI_NLS_DATEFORMAT: Oracle OCI_NLS_INT_CURRENCY: OCI_NLS_DUAL_CURRENCY: 2 OCI_NLS_LOC_CURRENCY: OCI_NLS_LANGUAGE: OCI_NLS_ABLANGUAGE: OCI_NLS_TERRITORY: OCI_NLS_CHARACTER_SET: OCI_NLS_LINGUISTIC_NAME: OCI_NLS_CALENDAR: OCI_Nls_MaxBufSz OCINlsGetInfo() JA16SJIS January OCI_NLS_MAXBUFSZ OCINlsGetInfo() OCINlsGetInfo() 5-6 Oracle8i NLS

107 Oracle8i Oracle8i NLS sword MyPrintLinguisticName(envhp, errhp) OCIEnv *envhp; OCIError *errhp; { OraText infobuf[oci_nls_maxbufsz]; sword ret; ret = OCINlsGetInfo(envhp, /* environment handle */ errhp, /* error handle */ infobuf, /* destination buffer */ (size_t) OCI_NLS_MAXBUFSZ, /* buffer size */ (ub2) OCI_NLS_LINGUISTIC_NAME); /* item */ if (ret!= OCI_SUCCESS) { checkerr(errhp, ret, OCI_HTYPE_ERROR); ret = OCI_ERROR; } else { printf("nls linguistic: %s\n", infobuf); } return(ret); } 2 Oracle 1 wchar Oracle ANSI/ISO C wchar_t Oracle 4 wchar_t Oracle Oracle OCI 5-7

108 5-2 OCI OCIMultiByteToWideChar() OCIMultiByteInSizeTo WideChar() OCIWideCharToMultiByte() OCIWideCharInSizeTo MultiByte() OCIWideCharToLower() OCIWideCharToUpper() OCIWideCharStrcmp() OCIWideCharStrncmp() OCIWideCharStrcat() OCIWideCharStrchr() OCIWideCharStrcpy() OCIWideCharStrlen() NULL wchar wchar NULL 2 str1 len1 str2 len2 OCIWideCharStrcmp() 2 wsrcstr wstr wc wchar wsrcstr wchar wdststr wstr wchar 5-8 Oracle8i NLS

109 5-2 OCI OCIWideCharStrncat() OCIWideCharStrncpy() OCIWideCharStrrchr() OCIWideCharStrCase Conversion() OCIWideCharDisplayLength() OCIWideCharMultibyte Length() OCIMultiByteStrcmp() OCIMultiByteStrncmp() OCIMultiByteStrcat() OCIMultiByteStrcpy() OCIMultiByteStrlen() OCIMultiByteStrncat() OCIMultiByteStrncpy() OCIMultiByteStrnDisplay Length() OCIMultiByteStrCase Conversion() wsrcstr n wsrcstr wchar wdststr n wstr wc wsrcstr wdststr wc wc 2 2 str1 len1 str2 len2 srcstr srcstr dststr str srcstr dststr scrstr dststr n srcstr dststr srcstr dststr n n OCIMultiByteToWideChar sword OCIMultiByteToWideChar(dvoid *hndl, OCIWchar *dst, CONST OraText *src, size_t *rsize); OCI 5-9

110 NULL wchar wchar NULL OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-3 OCIMultiByteToWideChar / / hndl IN/OUT dst OUT src IN rsize OUT OCI wchar NULL NULL OCIMultiByteInSizeToWideChar sword OCIMultiByteInSizeToWideChar(dvoid *hndl, OCIWchar *dst, size_t dstsz, CONST OraText *src, size_t srcsz, size_t *rsize) wchar NULL NULL dstsz 0 NULL OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-4 OCIMultiByteInSizeToWideChar / / hndl IN/OUT dst OUT OCI wchar dstsz 0NULL 5-10 Oracle8i NLS

111 5-4 OCIMultiByteInSizeToWideChar / / dstsz IN src IN srcsz IN rsize OUT 0 dstsz 0 NULL OCIWideCharToMultiByte sword OCIWideCharToMultiByte(dvoid *hndl, OraText *dst, CONST OCIWchar *src, size_t *rsize) NULL NULL OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-5 OCIWideCharToMultiByte / / hndl IN/OUT dst OUT src IN srcsz IN rsize OUT OCI wchar NULL OCI 5-11

112 OCIWideCharInSizeToMultiByte sword OCIWideCharInSizeToMultiByte(dvoid *hndl, OraText *dst, size_t dstsz, CONST OCIWchar *src, size_t srcsz, size_t *rsize) wchar NULL NULL dstsz 0 NULL OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-6 OCIWideCharInSizeToMultiByte / / hndl IN/OUT dst OUT dstsz IN src IN srcsz IN rsize OUT OCI dstsz 0NULL 0 wchar dstsz 0 NULL OCIWideCharToLower OCIWchar OCIWideCharToLower(dvoid *hndl, OCIWchar wc) wc wchar wc 5-12 Oracle8i NLS

113 wchar 5-7 OCIWideCharToLower / / hndl IN/OUT wc IN OCI wchar OCIWideCharToUpper OCIWchar OCIWideCharToUpper(dvoid *hndl, OCIWchar wc) wc wchar wc wchar 5-8 OCIWideCharToUpper / / hndl IN/OUT wc IN OCI wchar OCIWideCharStrcmp int OCIWideCharStrcmp(dvoid *hndl, CONST OCIWchar *wstr1, CONST OCIWchar *wstr2, int flag) 2 wchar wchar wstr = wstr2 0 OCI 5-13

114 wstr > wstr2 wstr < wstr2 5-9 OCIWideCharStrcmp / / hndl IN/OUT wstr1 IN wstr2 IN flag IN OCI NULL wchar NULL wchar OCI_NLS_BINARY: OCI_NLS_LINGUISTIC: OCI_NLS_CASE_INSENSITIVE OR OCIWideCharStrncmp int OCIWideCharStrncmp(dvoid *hndl, CONST OCIWchar *wstr1, size_t len1, CONST OCIWchar *wstr2, size_t len2, int flag) wstr1 len1 wstr2 len2 OCIWideCharStrcmp() NULL wstr = wstr2 0 wstr > wstr2 wstr < wstr OCIWideCharStrncmp / / hndl IN/OUT wstr1 IN OCI wchar 5-14 Oracle8i NLS

115 5-10 OCIWideCharStrncmp / / len1 IN wstr2 IN len2 IN flag IN 2 wchar 2 OCI_NLS_BINARY: OCI_NLS_LINGUISTIC: OCI_NLS_CASE_INSENSITIVE OR OCIWideCharStrcat size_t OCIWideCharStrcat(dvoid *hndl, OCIWchar *wdststr, CONST OCIWchar *wsrcstr) wsrcstr wchar wdststr wchar NULL NULL 5-11 OCIWideCharStrcat / / hndl IN/OUT wdststr IN/OUT wsrcstr IN OCI wchar wchar OCI 5-15

116 OCIWideCharStrchr OCIWchar *OCIWideCharStrchr(dvoid *hndl, CONST OCIWchar *wstr, OCIWchar wc) wstr wchar wc wchar NULL 5-12 OCIWideCharStrchr / / hndl IN/OUT wstr IN wc IN OCI wchar wchar OCIWideCharStrcpy size_t OCIWideCharStrcpy(dvoid *hndl, OCIWchar *wdststr, CONST OCIWchar *wsrcstr) wsrcstr wchar wdststr NULL NULL 5-13 OCIWideCharStrcpy / / hndl IN/OUT wdststr OUT wsrcstr IN OCI wchar wchar 5-16 Oracle8i NLS

117 OCIWideCharStrlen size_t OCIWideCharStrlen(dvoid *hndl, CONST OCIWchar *wstr) wstr wchar NULL NULL 5-14 OCIWideCharStrlen / / hndl IN/OUT wstr IN OCI wchar OCIWideCharStrncat size_t OCIWideCharStrncat(dvoid *hndl, OCIWchar *wdststr, CONST OCIWchar *wsrcstr, size_t n) n wsrcstr wdststr OCIWideCharStrcat() wsrcstr NULL wdststr NULL NULL 5-15 OCIWideCharStrncat / / hndl IN/OUT wdststr IN/OUT wsrcstr IN n IN OCI wchar wchar wsrcstr OCI 5-17

118 OCIWideCharStrncpy size_t OCIWideCharStrncpy(dvoid *hndl, OCIWchar *wdststr, CONST OCIWchar *wsrcstr, size_t n) n wsrcstr wdststr OCIWideCharStrcpy() wdststr NULL NULL NULL 5-16 OCIWideCharStrncpy / / hndl IN/OUT wdststr OUT wsrcstr IN n IN OCI wchar wchar wsrcstr OCIWideCharStrrchr OCIWchar *OCIWideCharStrrchr(dvoid *hndl, CONST OCIWchar *wstr, OCIWchar wc) wstr wchar wc wchar NULL wchar NULL 5-18 Oracle8i NLS

119 5-17 OCIWideCharStrrchr / / hndl IN/OUT wstr IN wc IN OCI wchar wchar OCIWideCharStrCaseConversion size_t OCIWideCharStrCaseConversion(dvoid *hndl, OCIWchar *wdststr, CONST OCIWchar*wsrcstr, ub4 flag) wsrcstr wdststr NULL NULL 5-18 OCIWideCharStrCaseConversion / / hndl IN/OUT wdststr OUT wsrcstr IN flag IN OCI OCI_NLS_UPPERCASE: OCI_NLS_LOWERCASE: / OCI_NLS_LINGUISTIC OR OCI 5-19

120 OCIWideCharDisplayLength size_t OCIWideCharDisplayLength(dvoid *hndl, OCIWchar wc) wc wc NULL OCIWideCharDisplayLength / / hndl IN/OUT wc IN OCI wchar OCIWideCharMultiByteLength size_t OCIWideCharMultiByteLen(dvoid *hndl, OCIWchar wc) wc wc 5-20 OCIWideCharMultiByteLength / / hndl IN/OUT wc IN OCI wchar 5-20 Oracle8i NLS

121 OCIMultiByteStrcmp int OCIMultiByteStrcmp(dvoid *hndl, CONST OraText *str1, CONST OraText *str2, int flag) 2 str1 = str2 0 str1 > str2 str1 < str OCIMultiByteStrcmp / / hndl IN/OUT str1 IN str2 IN flag IN OCI NULL NULL OCI_NLS_BINARY: OCI_NLS_LINGUISTIC: OCI_NLS_CASE_INSENSITIVE OR OCIMultiByteStrncmp int OCIMultiByteStrncmp(dvoid *hndl, CONST OraText *str1, size_t len1, OraText *str2, size_t len2, int flag) str1 len1 str2 len2 OCIMultiByteStrcmp() NULL OCI 5-21

122 str1 = str2 0 str1 > str2 str1 < str OCIMultiByteStrncmp / / hndl IN/OUT str1 IN len1 IN str2 IN len2 IN flag IN OCI 2 2 OCI_NLS_BINARY: OCI_NLS_LINGUISTIC: OCI_NLS_CASE_INSENSITIVE OR OCIMultiByteStrcat size_t OCIMultiByteStrcat(dvoid *hndl, OraText *dststr, CONST OraText *srcstr) srcstr dststr NULL NULL NULL 5-22 Oracle8i NLS

123 5-23 OCIMultiByteStrcat / / hndl IN/OUT dststr IN/OUT srcstr IN OCI OCIMultiByteStrcpy size_t OCIMultiByteStrcpy(dvoid *hndl, OraText *dststr, CONST OraText *srcstr) srcstr dststr NULL NULL NULL 5-24 OCIMultiByteStrcpy / / hndl IN/OUT dststr OUT srcstr IN OCI OCIMultiByteStrlen size_t OCIMultiByteStrlen(dvoid *hndl, CONST OraText *str) str NULL NULL OCI 5-23

124 5-25 OCIMultiByteStrlen / / hndl IN/OUT str IN OCI OCIMultiByteStrncat size_t OCIMultiByteStrncat(dvoid *hndl, OraText *dststr, CONST OraText *srcstr, size_t n) n srcstr dststr OCIMultiByteStrcat() srcstr NULL n dststr NULL NULL 5-26 OCIMultiByteStrncat / / hndl IN/OUT dststr IN/OUT srcstr IN n IN OCI srcstr OCIMultiByteStrncpy size_t OCIMultiByteStrncpy(dvoid *hndl, OraText *dststr, CONST OraText *srcstr, size_t n) n srcstr dststr OCIMultiByteStrcpy() srcstr NULL n NULL 5-24 Oracle8i NLS

125 NULL 5-27 OCIMultiByteStrncpy / / hndl IN/OUT srcstr OUT dststr IN n IN OCI srcstr OCIMultiByteStrnDisplayLength size_t OCIMultiByteStrnDisplayLength(dvoid *hndl, CONST OraText *str1, size_t n) n 5-28 OCIMultiByteStrncpy / / hndl IN/OUT str IN n IN OCI OCI 5-25

126 OCIMultiByteStrCaseConversion size_t OCIMultiByteStrCaseConversion(dvoid *hndl, OraText *dststr, CONST OraText *srcstr, ub4 flag) srcstr flag dststr NULL NULL 5-29 OCIMultibyteStrCaseConversion / / hndl IN/OUT dststr OUT srcstr IN flag IN OCI OCI_NLS_UPPERCASE: OCI_NLS_LOWERCASE: / OCI_NLS_LINGUISTIC OR size_t MyConvertMultiByteToWideChar(envhp, dstbuf, dstsize, srcstr) OCIEnv *envhp; OCIWchar *dstbuf; size_t dstsize; OraText *srcstr; /* null terminated source string */ { sword ret; size_t dstlen = 0; size_t srclen; /* get length of source string */ srclen = OCIMultiByteStrlen(envhp, srcstr); ret = OCIMultiByteInSizeToWideChar(envhp, /* environment handle */ dstbuf, /* destination buffer */ dstsize, /* destination buffer size */ 5-26 Oracle8i NLS

127 srcstr, /* source string */ srclen, /* length of source string */ &dstlen); /* pointer to destination length */ if (ret!= OCI_SUCCESS) { checkerr(envhp, ret, OCI_HTYPE_ENV); } return(dstlen); } Oracle8i Oracle8i Oracle 5-30 OCI OCIWideCharIsAlnum() OCIWideCharIsAlpha() OCIWideCharIsCntrl() OCIWideCharIsDigit() OCIWideCharIsGraph() OCIWideCharIsLower() OCIWideCharIsPrint() OCIWideCharIsPunct() OCIWideCharIsSpace() OCIWideCharIsUpper() OCIWideCharIsXdigit() OCIWideCharIsSingleByte() wc OCI 5-27

128 OCIWideCharIsAlnum boolean OCIWideCharIsAlnum(dvoid *hndl, OCIWchar wc) wc 10 TRUE FALSE 5-31 OCIWideCharIsAlnum / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsAlpha boolean OCIWideCharIsAlpha(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-32 OCIWideCharIsAlpha / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsCntrl boolean OCIWideCharIsCntrl(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-28 Oracle8i NLS

129 5-33 OCIWideCharIsCntrl / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsDigit boolean OCIWideCharIsDigit(dvoid *hndl, OCIWchar wc) wc 10 TRUE FALSE 5-34 OCIWideCharIsDigit / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsGraph boolean OCIWideCharIsGraph(dvoid *hndl, OCIWchar wc) wc 10 TRUE FALSE OCI 5-29

130 5-35 OCIWideCharIsGraph / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsLower boolean OCIWideCharIsLower(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-36 OCIWideCharIsLower / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsPrint boolean OCIWideCharIsPrint(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-30 Oracle8i NLS

131 5-37 OCIWideCharIsPrint / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsPunct boolean OCIWideCharIsPunct(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-38 OCIWideCharIsPunct / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsSpace boolean OCIWideCharIsSpace(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-39 OCIWideCharIsSpace / / hndl IN/OUT wc IN OCI wchar OCI 5-31

132 OCIWideCharIsUpper boolean OCIWideCharIsUpper(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-40 OCIWideCharIsUpper / / hndl IN/OUT wc IN OCI wchar OCIWideCharIsXdigit boolean OCIWideCharIsXdigit(dvoid *hndl, OCIWchar wc) wc A F a f TRUE FALSE 5-41 OCIWideCharIsXdigit / / hndl IN/OUT wc IN OCI wchar 5-32 Oracle8i NLS

133 OCIWideCharIsSingleByte boolean OCIWideCharIsSingleByte(dvoid *hndl, OCIWchar wc) wc TRUE FALSE 5-42 OCIWideCharIsSingleByte / / hndl IN/OUT wc IN OCI wchar /* Character classification sample code */ boolean MyIsNumberWideCharString(envhp, srcstr) OCIEnv *envhp; OCIWchar *srcstr; /* wide char source string */ { OCIWchar *pstr = srcstr; /* define and init pointer */ boolean status = TRUE; /* define and init status variable */ /* Check input */ if (pstr == (OCIWchar*) NULL) return(false); if (*pstr == (OCIWchar) NULL) return(false); /* check each character for digit */ do { if (OCIWideCharIsDigit(envhp, *pstr)!= TRUE) { status = FALSE; break; /* non decimal digit character */ OCI 5-33

134 } } while ( *++pstr!= (OCIWchar) NULL); return(status); } Oracle8i Oracle8i OCICharSetToUnicode Oracle Unicode 16 Unicode Unicode Oracle 5-43 OCI OCICharsetToUnicode() src Unicode dst OCIUnicodeToCharset() src Unicode dst OCICharSetConversionIs ReplacementUsed() OCICharsetConv() sword OCICharSetToUnicode(dvoid *hndl, ub2 *dst, size_t dstlen, CONST OraText *src, size_t srclen, size_t *rsize) src Unicode dst Unicode dstlen 0 rsize 5-34 Oracle8i NLS

135 OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-44 OCICharSetToUnicode / / hndl IN/OUT dst OUT dstlen IN src IN srclen IN rsize OUT OCI NULL OCIUnicodeToCharSet sword OCIUnicodeToCharSet(dvoid *hndl, OraText *dst, size_t dstlen, CONST ub2 *src, size_t srclen, size_t *rsize) src Unicode dst dstlen 0 rsize Unicode OCI OCICharsetConversionIsReplacementUsed() TRUE OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-45 OCIUnicodeToCharSet / / hndl IN/OUT dst OUT OCI OCI 5-35

136 5-45 OCIUnicodeToCharSet / / dstlen IN src IN srclen IN rsize OUT Unicode NULL OCICharSetConversionIsReplacementUsed boolean OCICharSetConversionIsReplacementUsed(dvoid *hndl) OCICharSetToUnicode() OCICharsetConv() TRUE FALSE 5-46 OCICharSetConversionIsReplacementUsed / / hndl IN/OUT OCI Oracle Unicode 16 Unicode Unicode Oracle Unicode size_t MyConvertMultiByteToUnicode(envhp, dstbuf, dstsize, srcstr) OCIEnv *envhp; ub2 *dstbuf; size_t dstsize; OraText *srcstr; { 5-36 Oracle8i NLS

137 sword ret; size_t dstlen = 0; size_t srclen; /* get length of source string */ srclen = OCIMultiByteStrlen(envhp, srcstr); ret = OCICharSetToUnicode(envhp, /* environment handle */ dstbuf, /* destination buffer */ dstsize, /* size of destination buffer */ srcstr, /* source string */ srclen, /* length of source string */ &dstlen); /* pointer to destination length */ if (ret!= OCI_SUCCESS) { checkerr(envhp, ret, OCI_HTYPE_ENV); } return(dstlen); } Oracle8i Oracle8i API Oracle 5-47 OCI OCIMessageOpen() OCIMessageGet() OCIMessageClose() hndl msgno 0 msgbuf msgh OCI 5-37

138 Oracle8i Oracle8i OCIMessageOpen sword OCIMessageOpen(dvoid *hndl, OCIError *errhp, OCIMsg **msghp, CONST OraText *product, CONST OraText *facility, OCIDuration dur) hndl hndl msghp OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-48 OCICharSetConversionIsReplacementUsed / / hndl IN/OUT errhp IN/OUT msghp OUT product IN facility IN OCI OCI errhp NULL OCIErrorGet() Solaris rdbms $ORACLE_HOME/rdbms img imgus.msb us msb 5-38 Oracle8i NLS

139 5-48 OCICharSetConversionIsReplacementUsed / / dur IN OCI_DURATION_PROCESS OCI_DURATION_STATEMENT OCI_DURATION_SESSION OCIMessageGet OraText *OCIMessageGet(OCIMsg *msgh, ub4 msgno, OraText *msgbuf, size_t buflen) msgno buflen 0msgbuf buflen 0 msgh NULL NULL NULL NULL 5-49 OCIMessageGet / / msgh IN/OUT msgno IN msgbuf OUT buflen IN OCIMessageOpen() buflen 0NULL OCI 5-39

140 OCIMessageClose sword OCIMessageClose(dvoid *hndl, OCIError *errhp, OCIMsg *msgh) msgh OCI_SUCCESS OCI_INVALID_HANDLE OCI_ERROR 5-50 OCIMessageClose / / hndl IN/OUT errhp IN/OUT msgh IN/OUT OCI OCI errhp NULL OCIErrorGet() OCIMessageOpen() LMSGEN lmsgen.msg.msb LMSGEN <text file> <product> <facility> [language] WHERE, <text file> is a message text file <product> the name of the product <facility> the name of the facility [language] optional message language in <language>_<territory>.<character set> format 5-40 Oracle8i NLS

141 / // # CHARACTER_SET_NAME= Japanese_Japan.JA16EUC 3 <message #>, <warning level #>, <message text> message # warning level # 0 message text 76 / Copyright (c) 1988 by the Oracle Corporation. All rights reserved. / This is a testing us7ascii message file # CHARACTER_SET_NAME= american_america.us7ascii / 00000, 00000, "Export terminated unsuccessfully\n" 00003, 00000, "no storage definition found for segment(%lu, %lu)".msb product = $HOME/myApp facility = imp Language = American language $HOME/myApp/mesg/impus.msb lmsgen impus.msg impus.msb OCI 5-41

142 impus.msg ,2, "Duplicate entry %s found in %s"... /* Assume that the OCI environment or user session handle, product, facility and cache size are all initialized properly. */... OCIMsg msghnd; /* message handle */ /* initialize a message handle for retrieving messages from impus.msg*/ err = OCIMessageOpen(hndl,errhp, &msghnd, prod,fac,oci_duration_session); if (err!= OCI_SUCCESS) /* error handling */... /* retrieve the message with message number = 128 */ msgptr = OCIMessageGet(msghnd, 128, msgbuf, sizeof(msgbuf)); /* do something with the message, such as display it */... /* close the message handle when we has no more message to retrieve */ OCIMessageClose(hndl, errhp, msghnd); 5-42 Oracle8i NLS

143 6 Java Java NLS Oracle8i Java JDBC SQLJ Java Virtual Machine Java CORBA EJB SQLJ Java 6-1

144 Oracle8i Java Oracle8i Java Java Java Java Java Virtual Machine VMJava Java CORBA Enterprise Java Beans EJB Java SQL Java CORBA EJB CORBA EJB Java Oracle8i JDBC SQLJ JDBC SQLJ SQL Java Java Java Oracle8i Java Java NLS Java Oracle Java Java NLS Unicode Oracle8i Java JDBC - Oracle Oracle8i JDBC Oracle JDBC JDBC OCI Java JDBC Thin Java JDBC Java VM Java SQLJ - SQLJ SQLJ SQL JDBC Java 6-2 Oracle8i NLS

145 JDBC Java Runtime Environment - Java VM JDK Java VM Java Java CORBA - Java Runtime Environment Oracle CORBA Object Request Broker ORB CORBA CORBA ORB Java CORBA EJB - Enterprise Java Bean 1.0 EJB JDBC JDBC JDBC OCI JDBC Thin JDBC oracle.sql.char NLS Oracle JDBC Oracle Java JDBC UCS2 16 Unicode UCS2 Db Charset UCS2 CHAR LONG CLOB VARCHAR2 RAW NLS JDBC Java java.sql.resultset getstring() getunicodestream() Java Unicode oracle.sql.clob getcharacterstream() CLOB Unicode oracle.sql.char getstring() tostring() getstringwithreplacement() Java 6-3

146 JDBC Oracle Java US7ASCII WE8ISO8859P1 Java UCS2 US7ASCII WE8ISO8859P1 UTF8 UCS2 : JDBC 6-1 JDBC 6-4 Oracle8i NLS

147 JDBC 6-1 JDBC Java Java Java Java Java CORBA EJB Java UTF8 WE8ISO8859PI US7ASCII Java JDBC UTF8 WE8ISO8859PI US7ASCII UCS2 UTF8 WE8ISO8859PI US7ASCII JDBC OCI C Oracle OCI JDBC Thin Java Java UTF8 WE8ISO8859PI US7ASCII C JDBC SQL PL/SQL Net8 Oracle8i JDBC JDBC JDBC Java Java US7ASCII UTF8 WE8ISO8859P1 JDBC JDBC UCS2 JDBC UCS2 UTF8 JDBC UTF8-to-UCS2 UTF8 UCS2 US7ASCII WE8ISO8859P1 Java UCS2 US7ASCII UCS2 Java 6-5

148 JDBC JDBC OCI 128 WE8ISO8859P1 UCS2 256 WE8ISO8859P1 US7ASCII UTF8 JDBC NLS_LANGUAGE NLS_TERRITORY JDBC JavaVM JDBC OCI JDBC Thin Java Oracle JDBC OCI NLS_LANG JDBC OCI 3 Java JDBC OCI NLS_LANG NLS_LANG NLS_LANG US7ASCII WE8ISO8859P1 JDBC OCI Java JDBC US7ASCII WE8ISO8859P1 UCS2 NLS_LANG US7ASCII WE8ISO8859P1 NLS_LANG UTF8 OCI NLS_LANG UTF8 OCI JDBC UTF8 UCS2 JDBC 6-6 Oracle8i NLS

149 JDBC : JDBC OCI NLS_LANG UTF8 Java UTF8 C UTF8 JDBC NLS_LANG 2-4 NLS_LANG JDBC Thin JDBC Thin Oracle C OCI JDBC OCI US7ASCII WE8ISO8859P1 Java UCS2 US7ASCII WE8ISO8859P1 UTF8 JDBC Thin Java UCS2 : OCI Thin NLS JDBC Java Oracle8i Java VM SQL SQL PL/SQL JDBC JDBC Oracle SQL PL/SQL SQL PL/SQL US7ASCII WE8ISO8859P1 UTF8 JDBC JDBC JDBC UCS2 Java 6-7

150 JDBC oracle.sql.char oracle.sql.char NLS oracle.sql.char oracle.sql.char NLS oracle.sql.char JDBC oracle.sql.char oracle.sql.char getstring(): oracle.sql.char Java String OracleID getstring() SQLException tostring(): getstring() OracleID tostring() oracle.sql.char 16 SQLException getstringwithreplacement(): getstring() oracle.sql.char Unicode? oracle.sql.char oracle.sql.char oracle.sql.characterset oracle.sql.char CharacterSet Oracle NLS oracle.sql.char 1. CharacterSet.make() CharacterSet OracleId Oracle int OracleId = CharacterSet.JA16SJIS_CHARSET; // this is character set CharacterSet mycharset = CharacterSet.make(OracleId); 6-8 Oracle8i NLS

151 JDBC Oracle OracleId OracleId <Oracle charset name>_charset <Oracle charset name> Oracle 2. oracle.sql.char CharacterSet String mystring = "teststring";... oracle.sql.char mychar = new oracle.sql.char(teststring, mycharset); oracle.sql.char CharacterSet oracle.sql.char CharacterSet oracle.sql.char Javadoc JDBC Oracle oracle.sql.char Oracle8i JDBC Oracle Oracle 6-1 JDBC Oracle oracle.sql.char SQL CREATE TYPE PERSON_TYPE AS OBJECT (NAME VARCHAR2(30), AGE NUMBER); CREATE TABLE EMPLOYEES (ID NUMBER, PERSON PERSON_TYPE) ; Java public class person implement SqlData { oracle.sql.char name; oracle.sql.number age; // SqlData interfaces getsqltype() {...} writesql(sqloutput stream) {...} Java 6-9

152 JDBC } readsql(sqlinput stream, String sqltype) {...} oracle.sql.char Oracle NAME VARCHAR JDBC VARCHAR people person TypeMap map = ((OracleConnection)conn).getTypeMap(); map.put("person_type", Class.forName("person")); conn.settypemap(map); ResultSet rs = stmt.executequery("select PERSON FROM EMPLOYEES"); rs.next(); person p = (person) rs.getobject(1); oracle.sql.char sql_name = p.name; String java_name = sql_name.getstring(); oracle.sql.char getstring() Oracle Java UCS2 Java rs.getobject(1) SqlData person PERSON_TYPE Java Typemap map NLS NLS CHAR VARCHAR2 CHAR VARCHAR2 CHAR VARCHAR2 NLS Ratio 4KB 6-10 Oracle8i NLS

153 JDBC 6-1 Thin OCI CHAR 2000 min(2000,4000/nls_ratio) VARCHAR /NLS_Ratio Oracle8 CHAR NLS_Ratio VARCHAR NLS_Ratio 6-2 NLS Ratio 6-2 NLS Ratio NLS Ratio Oracle8 WE8DEC US7ASCII ISO JA16SJIS JA16EUC NLS Oracle JDBC Java Unicode Oracle Java Unicode Java Unicode 1 Unicode 0xFF5E Oracle JA16SJIS Unicode Unicode 0x301C Java 6-11

154 SQLJ 6-2 Java Unicode Oracle (JA16SJIS) Java Unicode 0x301C 0xFF5E x x301C 0xFF5E Oracle JDBC Oracle JA16SJIS JA16EUC ZHT16BIG5 KO16KS5601 SQLJ SQLJ SQL-to-Java JDBC Java SQL JDBC Oracle8i Java SQL SQLJ Java SQLJ SQL Java JDBC Java SQL Javac Java Java 6-3 SQLJ 6-12 Oracle8i NLS

155 SQLJ 6-3 SQLJ SQLJ UCS2 SQLJ JDBC JDBC Java UCS2 Java Java Oracle8i SQLJ SQLJ JDK Java UCS2 SQLJ SQLJ Java UCS2 SQLJ -encoding sqlj -encoding Unicode <source file> Unicode \uxxxx Unicode SQL SQLJ UCS2 SQLJ SQL SQLJ SQL Unicode int empno = 12345; #sql {insert into E\u0063\u0064 (ENAME, EMPNO) values ('Joe', :empno)}; String name ename = "\ua0a1\ua0a2"; double raise = 0.1; #sql { update EMP set SAL = :(getnewsal(raise, ename)) where ENAME = :ename; Java SQLJ 6-27 SQLJ Java 6-13

156 Java Virtual Machine Java Virtual Machine Oracle8i Java VM Java Oracle8i Java Java SQLJ Java SQL PL/SQL Java SQL Java Java Oracle Java VM JDK 6-4 Java VM 6-4 Oracle Java Virtual Machine Net8 IIOP + Java + Java VM loadjava RDBMS Java VM Java VM Java Java Java Java.class Java Java Java 6-14 Oracle8i NLS

157 Java public class Greeting { public String Hello(String name) { return ("Hello" + name + "!"); } } Java loadjava Greeting.class Greeting US7ASCII RAW Java Java Java Java Greeting DDL Greeting.Hello() Java CREATE FUNCTION MYHELLO(NAME VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Greeting.Hello(java.lang.String) return java.lang.string'; DDL Java SQL Java PL/SQL PL/SQL Java 6-5 Java 6-15

158 Java 6-5 Java SQL>CALL MYHELLO('Oracle'); SQL>SELECT MYHELLO (ENAME)FROM EMP; NLS_LANG= JAPANESE_JAPAN.JA16SJIS Net8 PL/SQL MYHELLO('Oracle') Java VM Greeting.Hello() Java Greeting.Hello() Java VM UCS2 Java VM Oracle8i MYHELLO(VARCHAR2) Greeting.class PL/SQL MYHELLO() Java Greeting.Hello() PL/SQL PL/SQL Java Java VM Java Java VM Java VM Java SQL VARCHAR2 UCS2 Greeting Hello() Hello() VARCHAR2 Java 1. Java SQL UCS2 Java 6-16 Oracle8i NLS

159 CORBA EJB 2. Java VM Java NLS_LANG NLS_LANGUAGE NLS_TERRITORY Oracle Java Java VM 3. loadjava JDK Java SQLJ Java SQLJ Unicode : Java DDL SQL CORBA EJB Visigenic CORBA Object Request Broker ORB IIOP Java CORBA EJB CORBA CORBA Oracle CORBA Oracle8 Database Programming with Java CORBA ORB CORBA ORB Java IIOP IIOP CORBA IIOP 2 CORBA LoginServer PublishContext PublishedObject CORBA Oracle8i Java CORBA Publish Java CORBA PublishedObject CosNaming CORBA ORB Java 6-17

160 CORBA EJB Oracle8i CORBA CosNaming CosNaming CORBA JNDI URL sess_iiop: CORBA IIOP CORBA sess_iiop://local:2222:orcl/demo/mygreeting 2222 IIOP ORCL /Demo/MyGreeting Oracle8i CORBA US7ASCII 6-6 CORBA 6-6 CORBA Java CORBA Greeting= lookup("/demo/mygreeting"); Greeting.Hello('Oracle'); CORBA ORB GreetingImpl LoginServer ORB IIOP IIOP PublishContext GreetingImpl PublishedObject Java VM JDBC KPRB Greeting.jar publish loadjava Oracle8i /Demo/MyGreeting PublishedObject GreetingImpl 6-18 Oracle8i NLS

161 CORBA EJB Java CORBA Oracle8i CORBA Java Java VM CORBA CORBA IDL idl2java CORBA CORBA Java IDL CORBA Greeting.idl Module Demo { interface Greeting { wstring Hello(string str); }; }; >idl2java Greeting.IDL Creating: Demo/Greeting.java Demo/GreetingHolder.java Demo/GreetingHelper.java Demo/_GreetingImpBase.java GreetingImpl.java public class GreetingImpl extends _GreetingImplBase implements ActivatableObject { public GreetingImpl (String name) { super(name); } public GreetingImpl() { super(); } public org.omg.corba.object _intializeauroraobject() { return this } Java 6-19

162 CORBA EJB } public String Hello(String str) { return "Hello" + str; } CORBA Greeting Hello() CORBA CORBA/IIOP wstring Visigenic ORB wstring Unicode wstring string Hello() wstring CORBA Greeting idl2java _GreetingImplBase CORBA Java CosNaming Java CORBA loadjava -user scott/tiger -grant public Greeting.jar publish -user scott -password tiger -service sess_iiop://local:2222:orcl/demo/mygreeting Demo.GreetingImpl Demo.GreetingHelper Java Greeting.jar Greeting public /Demo/MyGreeting CORBA Java CORBA CORBA ORB Greeting Java ORB Oracle JNDL CORBA import java.util.hashtable; import javax.naming.*; import oracle.aurora.jndi.sess_iiop.servicectx; public class Client { public static void main(string args[]) throws Exception { Hashtable environment = new Hashtable(); 6-20 Oracle8i NLS

163 CORBA EJB Enterprise Java Beans environment.put(javax.naming.context.url_pkg_prefixes, "oracle.aurora.jndi"); environment.put(context.security_principal, "scott"); environment.put(context.security_credentials, "tiger"); environment.put(context.security_authentication, ServiceCtx.NON_SSL_CREDENTIAL); Context ic = new InitialContext(environment); Greeting greet = (Greeting) ic.lookup("sess_iiop://local:2222:orcl/demo/mygreeting"); System.out.println(greet.Hello(arg[0])); } } Java CORBA CORBA Java VM CORBA 1. - Java VM IIOP ORB Java VM 2. - Login authenticate() LoginServer CORBA LoginServer authenticate() Java Java Java VM NLS_LANGUAGE NLS_TERRITORY Oracle CORBA Oracle8i EJB EJB CORBA IIOP CORBA EJB EJB EJB EJB Oracle8i Enterprise JavaBeans CORBA GreetingBean EJB GreetingBean CORBA Greeting Java 6-21

164 CORBA EJB SessionBean GreetingServer.GreetingBean { BeanHomeName = "Demo/MyGreeting"; RemoteInterfaceClassName = hello.greeting; HomeInterfaceClassName = hello.greetinghome; AllowedIdentities = { PUBLIC }; RunAsMode = CLIENT_IDENTITY; TransactionAttribute = TX_SUPPORTS; } EJB JDK AllowedIdentities US7ASCII US7ASCII AllowedIdentities 2 1. EJB US7ASCII -encoding ejbdeploy 2. Unicode US7ASCII EJB GreetingBean.java GreetingServer import javax.ejb.sessionbean; import javax.ejb.createexception; import javax.ejb.sessioncontext; import java.rmi.remoteexception; public class GreetingBean implements SessionBean { // Methods of the Greeting interface public String Hello (String str) throws RemoteException { return "Hello" + str; } // Methods of the SessionBean public void ejbcreate () throws RemoteException, CreateException {} public void ejbremove() {} public void setsessioncontext (SessionContext ctx) {} public void ejbactivate () {} public void ejbpassivate () {} } 6-22 Oracle8i NLS

165 CORBA EJB EJB EJB UCS2 Java EJB CORBA EJB EJB deployejb -republish -temp temp -u scott -p tiger -encoding Unicode -s sess_iiop://local:2222:orcl -descriptor Greeting.ejb server.jar deployejb IIOP Oracle IIOP -s server.jar EJB Greeting Bean EJB Greeting.ejb Java VM -encoding Greeting.ejb Unicode EJB EJB Oracle JNDI EJB Java CORBA EJB EJB EJB create() EJB EJB EJB Demo/Greeting EJB Hello() CORBA import Demo.Greeting; //Remote interface object import Demo.GreetingHome; //Home interface object import javax.naming.*; import java.util.hashtable; import oracle.aurora.jndi.sess_iiop.servicectx; import oracle.aurora.client.*; public class Client { public static void main (String[] args) throws Exception { Hashtable environment = new Hashtable (); environment.put (Context.URL_PKG_PREFIXES, "oracle.aurora.jndi"); Java 6-23

166 Context ic = new InitialContext (environment); // Login to the 8i server LoginServer lserver = (LoginServer) ic.lookup ("sess_iiop://local:2222:orcl/etc/login"); Login li = new Login (lserver) li.authenticate (username, password, null); // Activate a Greeting instance in the 8i server // This creates a first session in the server GreetingHome greetinghome = (GreetingHome) ic.lookup ("sess_iiop://local:2222:orcl/demo/mygreeting"); Greeting greet = greetinghome.create (); System.out.println (greet.hello (arg[0])); } } li.authenticate() Java LoginServer Java EJB Java VM Java NLS_LANGUAGE NLS_TERRITORY Java VM EJB EJB Oracle8i Java 1. - WE8ISO8859P1 UTF8 UTF8 Unicode2.1 UTF Oracle8i NLS

167 2. - UTF8 Oracle8i UTF8 WE8ISO8859P1 JA16SJIS 3. - UTF8 UTF8 1. UTF8 Unicode 3 VARCHAR2 CHAR Unicode 3 2. Oracle VARCHAR(30) 10 Unicode Java Oracle8i Java Java VM Oracle8i Java Java VM Java VM Java Java VM NLS_LANG NLS_LANGUAGE NLS_TERRITORY Java Java Java Java CORBA EJB Java VM JDK Java Java CORBA EJB EJB Java Java Java 6-25

168 Java JDBC Java oracle.sql.char Java UCS2 UCS2 oracle.sql.char ID oracle.sql.char Java CORBA 6-20 Java CORBA IDL wstring Unicode Java 1. ORB Java CORBA - Java CORBA IIOP CORBA CORBA Java VM ORB Java VM wstring Unicode 2. Java - Java JDBC Thin Oracle8i Oracle Java SQL PL/SQL JDBC Thin JDBC Thin Java VM Java 3. Java VM Java - Java VM Java JDBC OCI JDBC Thin Java Web Java SQL PL/SQL JDBC JDBC Thin JDBC OCI Java Java VM 4. OCI Pro*C ODBC C - Java PL/SQL Java Java VM NLS_LANG Oracle NLS_LANGUAGE NLS_TERRITORY NLS_LANG 6-26 Oracle8i NLS

169 SQLJ OCI SQLJ SQLJ SQLJ JDBC Java SQLJ Java Java Java UTF Book Inventory ID (PRIMARY KEY) NUMBER(10) ID (PRIMARY KEY) NUMBER(10) NAME VARCHAR(300) LOCATION (PRIMARY KEY) VARCHAR(90) PUBLISH_DATE DATE QUANTITY NUMBER(3) AUTHOR VARCHAR(120) PRICES NUMBER(10,2) BOOK NAME AUTHOR BOOKSEQ Book ID Java 6-27

170 SQLJ Java Book.remove() Book.add() Book Java remove() BookRes.class remove() Java VM JDBC import java.sql.*; import java.util.*; import sqlj.runtime.ref.defaultcontext; /* The book class implementation the transaction logics of the Java stored procedures.*/ public class Book { static ResourceBundle rb; static int q, id; static DefaultContext ctx; public Book() { try { DriverManager.registerDriver(new oracle.jdbc.driver.oracledriver()); DefaultContext.setDefaultContext(ctx); rb = java.util.resourcebundle.getbundle("bookres"); } catch (Exception e) { System.out.println("Transaction failed: " + e.getmessage()); } } public static String Remove(int id, int quantity, String location) throws SQLException { rb = ResourceBundle.getBundle("BookRes"); try { #sql {SELECT QUANTITY INTO :q FROM INVENTORY WHERE ID = :id AND LOCATION = :location}; if (id == 1) return rb.getstring ("NotEnough"); } catch (Exception e) 6-28 Oracle8i NLS

171 SQLJ { return rb.getstring ("NotEnough"); } if ((q - quantity) == 0) { #sql {DELETE FROM INVENTORY WHERE ID = :id AND LOCATION = :location}; try { #sql {SELECT SUM(QUANTITY) INTO :q FROM INVENTORY WHERE ID = :id}; } catch (Exception e) { #sql { DELETE FROM BOOK WHERE ID = :id }; return rb.getstring("removebook"); } return rb.getstring("removeinventory"); } else { if ((q-quantity) < 0) return rb.getstring ("NotEnough"); #sql { UPDATE INVENTORY SET QUANTITY = :(q-quantity) WHERE ID = :id and LOCATION = :location }; return rb.getstring("decreaseinventory"); } } public static String Add( String bname, String author, String location, double price, int quantity, String publishdate ) throws SQLException { rb = ResourceBundle.getBundle("BookRes"); try { #sql { SELECT ID into :id FROM BOOK WHERE NAME = :bname AND AUTHOR = :author }; } catch (Exception e) { #sql { SELECT BOOKSEQ.NEXTVAL INTO :id FROM DUAL }; #sql { INSERT INTO BOOK VALUES (:id, :bname, TO_DATE(:publishdate,'YYYY-MM-DD'), :author, :price) }; #sql { INSERT INTO INVENTORY VALUES (:id, :location, :quantity) }; return rb.getstring("addbook"); } try { #sql { SELECT QUANTITY INTO :q FROM INVENTORY WHERE ID = :id Java 6-29

172 SQLJ } } AND LOCATION = :location }; } catch (Exception e) { #sql { INSERT INTO INVENTORY VALUES (:id, :location, :quantity) }; return rb.getstring("addinventory"); } #sql { UPDATE INVENTORY SET QUANTITY = :(q + quantity) WHERE ID = :id AND LOCATION = :location }; return rb.getstring("increaseinventory"); SQLJ Book.remove() Book.add() Java REMOVEBOOK() ADDBOOK() CREATE FUNCTION REMOVEBOOK (ID NUMBER, QUANTITY NUMBER, LOCATION VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Book.remove(int, int, java.lang.string) return java.lang.string'; CREATE FUNCTION ADDBOOK (NAME VARCHAR2, AUTHOR VARCHAR2, LOCATION VARCHAR2, PRICE NUMBER, QUANTITY NUMBER, PUBLISH_DATE DATE) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Book.add(java.lang.String, java.lang.string, java.lang.string, double, int, java.sql.date) return java.lang.string'; Java VARCHAR2 UTF8 Java Unicode? VARCHAR2 Java Java SQLJ JDBC Thin JDBC OCI GUI Java BookDB 6-30 Oracle8i NLS

173 SQLJ BookDB GUI removebook() addbook() Java searchbyname() searchbyauthor() NAME AUTHOR BookDB books SQLJ BookRecs GUI getnextbook() NULL getnextbook() package sqlj.bookstore; import java.sql.*; import sqlj.bookstore.bookdescription; import sqlj.runtime.ref.defaultcontext; import java.util.locale; /*The iterator used for a book description when communicating with the server*/ #sql iterator BooksRecs( int ID, String NAME, String AUTHOR, Date PUBLISH_DATE, String LOCATION, int QUANTITY, double PRICE); /*This is the class used for connection to the server.*/ class BookDb { static public final String DRIVER = "oracle.jdbc.driver.oracledriver"; static public final String URL_PREFIX = "jdbc:oracle:thin:@"; private DefaultContext m_ctx = null; private String msg; private BooksRecs books; /*Constructor - registers the driver*/ BookDb() { try { DriverManager.registerDriver ((Driver) (Class.forName(DRIVER).newInstance())); } catch (Exception e) { System.exit(1); } } /*Connect to the database.*/ DefaultContext connect(string id, String pwd, String userurl) throws SQLException Java 6-31

174 SQLJ { String url = new String(URL_PREFIX); url = url.concat(userurl); Connection conn = null; if (m_ctx!= null) return m_ctx; try { conn = DriverManager.getConnection(url, id, pwd); } catch (SQLException e) { throw(e); } if (m_ctx == null) { try { m_ctx = new DefaultContext(conn); } catch (SQLException e) { throw(e); } } return m_ctx; } /*Add a new book to the database.*/ public String addbook(bookdescription book) { String name = book.gettitle(); String author = book.getauthor(); String date = book.getpublishdatestring(); String location = book.getlocation(); int quantity = book.getquantity(); double price = book.getprice(); try { #sql [m_ctx] msg = {VALUE ( ADDBOOK ( :name, :author, :location, :price, :quantity, :date))}; #sql [m_ctx] {COMMIT}; } catch (SQLException e) { return (e.getmessage()); } 6-32 Oracle8i NLS

175 SQLJ return msg; } /*Remove a book.*/ public String removebook(int id, int quantity, String location) { try { #sql [m_ctx] msg = {VALUE ( REMOVEBOOK ( :id, :quantity, :location))}; #sql [m_ctx] {COMMIT}; } catch (SQLException e) { return (e.getmessage()); } return msg; } /*Search books by the given author.*/ public void searchbyauthor(string author) { String key = "%" + author + "%"; books = null; System.gc(); try { #sql [m_ctx] books = { SELECT BOOK.ID, NAME, AUTHOR, PUBLISH_DATE, LOCATION, QUANTITY, PRICE FROM BOOK, INVENTORY WHERE BOOK.ID = INVENTORY.ID AND AUTHOR LIKE :key ORDER BY BOOK.ID}; } catch (SQLException e) {} } /*Search books with the given title.*/ public void searchbytitle(string title) { String key = "%" + title + "%"; books = null; System.gc(); try { #sql [m_ctx] books = { SELECT BOOK.ID, NAME, AUTHOR, PUBLISH_DATE, LOCATION, QUANTITY, PRICE FROM BOOK, INVENTORY WHERE BOOK.ID = INVENTORY.ID AND NAME LIKE :key ORDER BY BOOK.ID}; } Java 6-33

176 } catch (SQLException e) {} } /*Returns the next BookDescription from the last search, null if at the end of the result list.*/ public BookDescription getnextbook() { BookDescription book = null; try { if (books.next()) { book = new BookDescription(books.ID(), books.author(), books.name(), books.publish_date(), books.price(), books.location(), books.quantity()); } } catch (SQLException e) {} return book; } Oracle8i Java JDBC SQLJ Java Oracle8i Java VM CORBA ORB EJB Java Java CORBA EJB Oracle8i CORBA ORB CORBA JDBC SQLJ Java VM CORBA ORB EJB Oracle8i Java Unicode Java Java Java VM JDK Java Java CORBA EJB 6-34 Oracle8i NLS

177 A Oracle Server NLS V$NLS_VALID_VALUES Oracle8i A-1

178 A-1 Oracle Server A-1 Oracle AMERICAN ARABIC BENGALI BRAZILIAN PORTUGUESE BULGARIAN CANADIAN FRENCH CATALAN CROATIAN CZECH DANISH DUTCH EGYPTIAN ENGLISH ESTONIAN FINNISH FRENCH GERMAN DIN GERMAN GREEK HEBREW HINDI HUNGARIAN ICELANDIC INDONESIAN ITALIAN us ar bn ptb bg frc ca hr cs dk nl eg gb et sf f din d el iw hi hu is in i A-2 Oracle8i NLS

179 A-1 Oracle JAPANESE KOREAN LATIN AMERICAN SPANISH LATVIAN LITHUANIAN MALAY MEXICAN SPANISH NORWEGIAN POLISH PORTUGUESE ROMANIAN RUSSIAN SIMPLIFIED CHINESE SLOVAK SLOVENIAN SPANISH SWEDISH TAMIL THAI TRADITIONAL CHINESE TURKISH UKRAINIAN VIETNAMESE ja ko esa lv lt ms esm n pl pt ro ru zhs sk sl e s ta th zht tr uk vn Oracle A-2 A-3

180 A-2 Oracle ARABIC ar BRAZILIAN PORTUGUESE ptb CATALAN ca CZECH cs DANISH dk DUTCH nl FINNISH sf FRENCH f GERMAN d GREEK el HEBREW iw HUNGARIAN hu ITALIAN i JAPANESE ja KOREAN ko LATIN AMERICAN SPANISH esa NORWEGIAN n POLISH pl PORTUGUESE pt ROMANIAN ro RUSSIAN ru SIMPLIFIED CHINESE zhs SLOVAK sk SPANISH e SWEDISH s TRADITIONAL CHINESE zht TURKISH tr A-4 Oracle8i NLS

181 A-3 Oracle Server A-3 Oracle ALGERIA ICELAND QATAR AMERICA INDIA ROMANIA AUSTRALIA INDONESIA SAUDI ARABIA AUSTRIA IRAQ SINGAPORE BAHRAIN IRELAND SLOVAKIA BANGLADESH ISRAEL SLOVENIA BELGIUM ITALY SOMALIA BRAZIL JAPAN SOUTH AFRICA BULGARIA JORDAN SPAIN CANADA KAZAKHSTAN SUDAN CATALONIA KOREA SWEDEN CHINA KUWAIT SWITZERLAND CIS LATVIA SYRIA CROATIA LEBANON TAIWAN CYPRUS LIBYA THAILAND CZECH REPUBLIC LITHUANIA THE NETHERLANDS DENMARK LUXEMBOURG TUNISIA DJIBOUTI MALAYSIA TURKEY EGYPT MAURITANIA UKRAINE ESTONIA MEXICO UNITED ARAB EMIRATES FINLAND MOROCCO UNITED KINGDOM FRANCE NEW ZEALAND UZBEKISTAN GERMANY NORWAY VIETNAM A-5

182 A-3 Oracle GREECE OMAN YEMEN HONG KONG POLAND HUNGARY PORTUGAL Oracle 3 Unicode SB = MB = FIXED = 3 NCHAR ALTER DATABASE [NATIONAL] CHARACTER SET ASCII EURO = UDC = A-6 Oracle8i NLS

183 ASCII = ASCII A-4 Oracle A-4 BN8BSCII 8 BSCII SB ASCII ZHT16BIG5 BIG5 16 MB ASCII ZHS16CGB CGB MB ASCII JA16EUC EUC 24 MB ASCII JA16EUCYEN JA16EUCFIXED EUC 24 '\' EUC 16 JA16EUC JA16EUC ASCII MB FIXED ZHT32EUC EUC 32 MB ASCII ZHT32EUCFIXED EUC FIXED ZHS16GBK GBK 16 MB ASCII UDC ZHS16GBKFIXED GBK FIXED UDC ZHT16CCDC HP CCDC 16 MB ASCII JA16DBCS IBM EBCDIC 16 MB UDC JA16EBCDIC930 IBM DBCS MB UDC JA16DBCSFIXED IBM EBCDIC FIXED UDC KO16DBCS IBM EBCDIC 16 MB UDC KO16DBCSFIXED IBM EBCDIC FIXED UDC A-7

184 A-4 ZHS16DBCS IBM EBCDIC 16 MB UDC ZHS16CGB231280FIXED ZHS16DBCSFIXED CGB IBM EBCDIC FIXED FIXED UDC ZHT16DBCS IBM EBCDIC 16 MB UDC ZHT16DBCSFIXED IBM EBCDIC FIXED KO16KSC5601 KSC MB ASCII KO16KSCCS KSCCS 16 MB ASCII KO16KSC5601FIXED KSC FIXED JA16VMS JVMS 16 MB ASCII ZHS16MACCGB Mac CGB MB JA16MACSJIS Mac JIS 16 MB TH8MACTHAI Mac 8 / SB TH8MACTHAIS Mac 8 / SB ASCII TH8TISEBCDICS EBCDIC 8 SB ZHT16MSWIN950 MS Windows 950 MB ASCII UDC KO16MSWIN949 MS Windows 949 MB ASCII UDC VN8MSWIN1258 MS Windows SB ASCII EURO IN8ISCII 8 / SB ASCII JA16SJIS JIS 16 MB ASCII UDC JA16SJISFIXED JA16SJISYEN JIS 16 JA16JIS JA16JIS ASCII JIS 16 '\' FIXED UDC MB UDC ZHT32SOPS SOPS 32 MB ASCII A-8 Oracle8i NLS

185 A-4 ZHT16DBT 16 MB ASCII ZHT16BIG5FIXED BIG FIXED TH8TISASCII ASCII 8 SB ASCII EURO TH8TISEBCDIC EBCDIC 8 SB ZHT32TRIS TRIS 32 MB ASCII ZHT32TRISFIXED TRIS 32 FIXED AL24UTFFSS UTF8 UTFE A-18 A-18 A-18 VN8VN3 VN3 8 SB ASCII A-5 Oracle A-5 US7ASCII ASCII 7 SB ASCII SF7ASCII ASCII 7 SB YUG7ASCII ASCII 7 SB RU8BESTA BESTA 8 / SB ASCII EL8GCOS7 Bull EBCDIC GCOS7 8 SB WE8GCOS7 Bull EBCDIC GCOS7 8 SB EL8DEC DEC 8 / SB TR7DEC DEC VT100 7 SB A-9

186 A-5 TR8DEC DEC 8 SB ASCII TR8EBCDIC1026 EBCDIC SB TR8EBCDIC1026S EBCDIC SB TR8PC857 IBM-PC SB ASCII TR8MACTURKISH Mac 8 SB TR8MACTURKISHS Mac 8 SB ASCII TR8MSWIN1254 MS Windows SB ASCII EURO WE8BS2000L5 Siemens EBCDIC.DF.L5 8 / SB WE8DEC DEC 8 SB ASCII D7DEC DEC VT100 7 SB F7DEC DEC VT100 7 SB S7DEC DEC VT100 7 SB E7DEC DEC VT100 7 SB NDK7DEC DEC VT100 7 / SB I7DEC DEC VT100 7 SB NL7DEC DEC VT100 7 SB CH7DEC DEC VT100 7 / SB SF7DEC DEC VT100 7 SB WE8DG DG 8 SB ASCII WE8EBCDIC37C EBCDIC 37 8 Oracle/c SB WE8EBCDIC37 EBCDIC 37 8 SB D8EBCDIC273 EBCDIC 273/1 8 SB DK8EBCDIC277 EBCDIC 277/1 8 SB S8EBCDIC278 EBCDIC 278/1 8 SB I8EBCDIC280 EBCDIC 280/1 8 SB WE8EBCDIC284 EBCDIC / SB A-10 Oracle8i NLS

187 A-5 WE8EBCDIC285 EBCDIC SB WE8EBCDIC1047 EBCDIC SB WE8EBCDIC1140 EBCDIC SB EURO WE8EBCDIC1140C EBCDIC SB EURO WE8EBCDIC1145 EBCDIC SB EURO WE8EBCDIC1146 EBCDIC SB EURO WE8EBCDIC1148 EBCDIC SB EURO WE8EBCDIC1148C EBCDIC SB EURO F8EBCDIC297 EBCDIC SB WE8EBCDIC500C EBCDIC 37 8 Oracle/c SB WE8EBCDIC500 EBCDIC SB EE8EBCDIC870 EBCDIC SB EE8EBCDIC870C EBCDIC SB EE8EBCDIC870S EBCDIC SB WE8EBCDIC871 EBCDIC SB EL8EBCDIC875 EBCDIC SB EL8EBCDIC875S EBCDIC SB CL8EBCDIC1025 EBCDIC SB CL8EBCDIC1025C EBCDIC SB CL8EBCDIC1025S EBCDIC SB CL8EBCDIC1025X EBCDIC SB BLT8EBCDIC1112 EBCDIC SB BLT8EBCDIC1112S EBCDIC SB D8EBCDIC1141 EBCDIC SB EURO DK8EBCDIC1142 EBCDIC SB EURO S8EBCDIC1143 EBCDIC SB EURO A-11

188 A-5 I8EBCDIC1144 EBCDIC SB EURO F8EBCDIC1147 EBCDIC SB EURO EEC8EUROASCI EEC Targon 35 ASCI / SB EEC8EUROPA3 EEC EUROPA3 8 / SB LA8PASSPORT 8 SB ASCII WE8HP HP LaserJet 8 SB WE8ROMAN8 HP Roman8 8 SB ASCII HU8CWI2 8 CWI-2 SB ASCII HU8ABMOD 8 AB Mod SB ASCII LV8RST IBM-PC 8 / SB ASCII US8PC437 IBM-PC SB ASCII BG8PC437S IBM-PC SB ASCII EL8PC437S IBM-PC SB ASCII EL8PC737 IBM-PC / SB LT8PC772 IBM-PC / SB ASCII LT8PC774 IBM-PC SB ASCII BLT8PC775 IBM-PC SB ASCII WE8PC850 IBM-PC SB ASCII EL8PC851 IBM-PC / SB ASCII EE8PC852 IBM-PC SB ASCII RU8PC855 IBM-PC / SB ASCII WE8PC858 IBM-PC SB ASCII EURO WE8PC860 IBM-PC SB ASCII IS8PC861 IBM-PC SB ASCII CDN8PC863 IBM-PC SB ASCII A-12 Oracle8i NLS

189 A-5 N8PC865 IBM-PC SB ASCII RU8PC866 IBM-PC / SB ASCII EL8PC869 IBM-PC / SB ASCII LV8PC1117 IBM-PC SB ASCII US8ICL ICL EBCDIC 8 SB WE8ICL ICL EBCDIC 8 SB WE8ISOICLUK ICL ISO SB WE8ISO8859P1 ISO SB ASCII EE8ISO8859P2 ISO SB ASCII SE8ISO8859P3 ISO SB ASCII NEE8ISO8859P4 ISO SB ASCII CL8ISO8859P5 ISO / SB ASCII AR8ISO8859P6 ISO / SB ASCII EL8ISO8859P7 ISO / SB ASCII EURO IW8ISO8859P8 ISO / SB ASCII NE8ISO8859P10 ISO SB ASCII WE8ISO8859P15 ISO SB ASCII EURO LA8ISO6937 ISO SB ASCII IW7IS / SB AR8ARABICMAC Mac 8 / SB EE8MACCE Mac 8 SB EE8MACCROATIAN Mac 8 SB WE8MACROMAN8 Mac 8 Roman8 SB EL8MACGREEK Mac 8 SB IS8MACICELANDIC Mac 8 SB CL8MACCYRILLIC Mac 8 / SB A-13

190 A-5 AR8ARABICMACS Mac 8 / SB ASCII EE8MACCES Mac 8 SB ASCII EE8MACCROATIANS Mac 8 SB ASCII WE8MACROMAN8S Mac 8 Roman8 SB ASCII CL8MACCYRILLICS Mac 8 / SB ASCII EL8MACGREEKS Mac 8 SB ASCII IS8MACICELANDICS Mac 8 SB BG8MSWIN MS Windows 8 SB ASCII LT8MSWIN921 MS Windows SB ASCII ET8MSWIN923 MS Windows SB ASCII EE8MSWIN1250 MS Windows SB ASCII EURO CL8MSWIN1251 MS Windows / SB ASCII EURO WE8MSWIN1252 MS Windows SB ASCII EURO EL8MSWIN1253 MS Windows / SB ASCII EURO BLT8MSWIN1257 MS Windows SB ASCII EURO BLT8CP921 LVS8-92(1) Windows/Unix 8 SB ASCII LV8PC8LR IBM-PC / SB ASCII WE8NCR4970 NCR SB ASCII WE8NEXTSTEP NeXTSTEP 8 SB ASCII CL8KOI8R RELCOM 8 / SB ASCII US8BS2000 Siemens EBCDIC 8 SB DK8BS2000 Siemens EBCDIC 8 SB F8BS2000 Siemens EBCDIC 8 SB D8BS2000 Siemens EBCDIC 8 SB A-14 Oracle8i NLS

191 A-5 E8BS2000 Siemens EBCDIC 8 SB S8BS2000 Siemens EBCDIC 8 SB DK7SIEMENS9780X Siemens 97801/ SB F7SIEMENS9780X Siemens 97801/ SB D7SIEMENS9780X Siemens 97801/ SB I7SIEMENS9780X Siemens 97801/ SB N7SIEMENS9780X Siemens 97801/ SB E7SIEMENS9780X Siemens 97801/ SB S7SIEMENS9780X Siemens 97801/ SB WE8BS2000 Siemens EBCDIC.DF.04 8 SB CL8BS2000 Siemens EBCDIC.EHC.LC 8 SB AL24UTFFSS UTF8 UTFE A-18 A-18 A-18 A-6 Oracle A-6 AR8APTEC715 APTEC / SB ASCII AR8APTEC715T APTEC / SB AR8ASMO708PLUS ASMO 708 Plus8 / SB ASCII AR8ASMO8X ASMO / SB ASCII AR8ADOS710 MS-DOS / SB ASCII A-15

192 A-6 AR8ADOS710T MS-DOS / SB AR8ADOS720 MS-DOS / SB ASCII AR8ADOS720T MS-DOS / SB TR7DEC DEC VT100 7 SB TR8DEC DEC 8 SB WE8EBCDIC37C EBCDIC 37 8 Oracle/c SB IW8EBCDIC424 EBCDIC / SB IW8EBCDIC424S EBCDIC / WE8EBCDIC500C EBCDIC 37 8 Oracle/c SB IW8EBCDIC1086 EBCDIC SB AR8EBCDIC420S EBCDIC / AR8EBCDICX EBCDIC XBASIC 8 / SB TR8EBCDIC1026 EBCDIC SB TR8EBCDIC1026S EBCDIC SB AR8HPARABIC8T HP 8 / SB TR8PC857 IBM-PC SB ASCII IW8PC1507 IBM-PC 1507/862 8 / SB ASCII AR8ISO8859P6 ISO / SB ASCII IW8ISO8859P8 ISO / SB ASCII WE8ISO8859P9 ISO SB ASCII LA8ISO6937 ISO SB ASCII IW7IS / SB IW8MACHEBREW Mac 8 SB AR8ARABICMAC Mac 8 / SB AR8ARABICMACT Mac 8 / SB SB SB A-16 Oracle8i NLS

193 A-6 TR8MACTURKISH Mac 8 SB IW8MACHEBREWS Mac 8 SB ASCII AR8ARABICMACS Mac 8 / SB ASCII TR8MACTURKISHS Mac 8 SB ASCII TR8MSWIN1254 MS Windows SB ASCII EURO IW8MSWIN1255 MS Windows / SB ASCII EURO AR8MSWIN1256 MS Windows / IN8ISCII 8 / SB SB ASCII EURO AR8MUSSAD768 Mussa'd Alarabi/ / SB ASCII AR8MUSSAD768T Mussa'd Alarabi/ / SB AR8NAFITHA711 Nafitha / SB ASCII AR8NAFITHA711T Nafitha / SB AR8NAFITHA721 Nafitha International / AR8NAFITHA721T Nafitha International / SB SB ASCII AR8SAKHR706 SAKHR / SB ASCII AR8SAKHR707 SAKHR / SB ASCII AR8SAKHR707T SAKHR / SB AR8XBASIC XBASIC 8 / SB WE8BS2000L5 Siemens EBCDIC.DF.04.L5 8 / SB AL24UTFFSS UTF8 UTFE A-18 A-18 A-18 A-17

194 A-7 Oracle A-7 AL24UTFFSS Unicode 1.1 UTF-8 MB ASCII EURO UTF8 Unicode 2.1 UTF-8 MB ASCII EURO UTFE UTF-EBCDIC Unicode 2.1 EBCDIC-friendly UTF UTF8 UNIX Win32 ASCII UTFE IBM EBCDIC Oracle UTFE 1 4 UTF8 3 UTF8 UTFE A-18 Oracle8i NLS

195 : Unicode 2.1 Unicode ,172 Unicode Unicode 2.1 Unicode Unicode Consortium Unicode Standard UTF8 Oracle UTF8 Unicode 2.1 UCS2 UTF16 U+0000 U+007F UTF8 1 0x00 0x7F ASCII ASCII US7ASCII UTF8 0x00 0x7F Unicode 2.1 UCS2 UTF16 U+0080 U+07FF UTF8 2 0xC0WW 0xDFWW WW 0x80 0xBF Unicode 2.1 UCS2 UTF16 U+0080 U+07FF U+E000 U+FFFF UTF8 3 0xE0WWTT 0xECWWTT 0xED80TT 0xED9FTT 0xEEWWTT 0xEFWWTT WW TT 0x80 0xBF A-19

196 Oracle UTF8 Oracle UTF8 Oracle Unicode 2.1 UTF16 U+D800 U+DFFF Unicode 2.1 UTF16surrogate UTF8 4 Unicode 2.1 Unicode 2.1 Oracle UTF8 Oracle UTF8 UTF16 UTF16 Oracle UTF8 6, ,072 6,400 Oracle UTF8 Unicode 2.1 ASCII ch ll XSPANISH 1 A-8 Oracle Server A-8 ARABIC -- ARABIC_MATCH -- ARABIC_ABJ_SORT -- ARABIC_ABJ_MATCH -- ASCII7 -- BENGALI -- BULGARIAN -- CANADIAN FRENCH -- CATALAN XCATALAN æ AE ß CROATIAN XCROATIAN D L N d l n ß A-20 Oracle8i NLS

197 A-8 CZECH XCZECH ch CH Ch ß DANISH XDANISH A ß Å å DUTCH XDUTCH ij IJ EEC_EURO -- EEC_EUROPA3 -- ESTONIAN -- FINNISH -- FRENCH XFRENCH GERMAN XGERMAN ß GERMAN_DIN XGERMAN_DIN ß ä ö ü Ä Ö Ü GREEK -- HEBREW -- HUNGARIAN XHUNGARIAN cs gy ny sz ty zs ß CS Cs GY Gy NY Ny SZ Sz TY Ty ZS Zs ICELANDIC -- INDONESIAN -- ITALIAN -- JAPANESE -- LATIN -- LATVIAN -- LITHUANIAN -- MALAY -- NORWEGIAN -- POLISH -- PUNCTUATION ROMANIAN -- RUSSIAN -- XPUNCTUATION A-21

198 A-8 SLOVAK XSLOVAK dz DZ Dz ß caron SLOVENIAN XSLOVENIAN ß SPANISH XSPANISH ch ll CH Ch LL Ll SWEDISH -- SWISS XSWISS ß THAI_DICTIONARY -- THAI_TELEPHONE -- TURKISH XTURKISH æ AE ß UKRAINIAN -- UNICODE_BINARY VIETNAMESE -- WEST_EUROPEAN XWEST_EUROPEAN ß A-9 Oracle Server A-9 NLS Japanese Imperial ROC Official Thai Buddha EEYY"\307\257"MM"\267\356"DD"\306\374" EEyy"\310\241"mm"\305\314"dd"\305\312" dd month EE yyyy Persian DD Month YYYY Arabic Hijrah English Hijrah DD Month YYYY DD Month YYYY JA16EUC ZHT32EUC TH8TISASCII AR8ASMO8X AR8ISO8859P6 AR8ISO8859P6 A-22 Oracle8i NLS

199 A-1 ROC Official A-1 ROC Official A-2 Japanese Imperial A-23

200 A-2 Japanese Imperial A-10 A-10 WE8EBCDIC1140 EBCDIC x9F WE8EBCDIC1140C EBCDIC 1140C 8 0x9F D8EBCDIC1141 DK8EBCDIC1142 S8EBCDIC1143 I8EBCDIC1144 EBCDIC EBCDIC EBCDIC EBCDIC x9F 0x5A 0x5A WE8EBCDIC1145 EBCDIC x9F 0x9F A-24 Oracle8i NLS