-- ****************************************************
-- Thai support testing scripts
-- Author: a@fuju.org
-- Created : April 10,2000
-- updated 1: April 13,2001
-- Last updated: April 13,2002
-- ****************************************************
-- NLS_LANG = language_territory.charset 
-- Each component controls the operation of a subset of NLS features: 
-- >>language <<
-- Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. 
-- >>territory <<
-- Specifies conventions such as the default date, monetary, and numeric formats. charset 
-- Specifies the character set used by the client application . For example, US7ASCII, WE8ISO8859P1, TH8TISASCII, or JA16EUC.
--
-- Make sure the current value of NLS_LANG=THAI_THAILAND.TH8THIASCII
-- default:
-- CALENDAR=GREGORIAN
-- NLS_TERRITORY=THAILAND; 
-- NLS_LANGUAGE=THAI; 
-- *****************************************************************
--1:  display the current Database charecter set ( Encoding ) show TH8TISASCII , TH, 8bits, ASCII code
-- We have already support Thai encoding inside database systems.
select * from nls_database_parameters;

--2:  create new tables for storing thai data and date data type

drop table t2sort;

create table t2sort(
id number(2),
name varchar2(20),
upddate date);

-- Display current nls_session_paramters;

select * from nls_session_parameters;

insert into t2sort values(1,'ก็เป็นอย่างนี้',sysdate);
insert into t2sort values(2,'เสือ',to_date('12-12-02','DD-MM-YY'));
insert into t2sort values(3,'สู้',to_date('12-12-2002','DD-MM-YYYY'));
insert into t2sort values(4,'กอด',to_date('12 12 2002','DD MM YYYY'));
insert into t2sort values(5,'เกิดเป็นไทย',to_date('12/12/02','DD/MM/YY'));
insert into t2sort values(6,'ฮ นก ฮูก มาหลังเกิด',to_date('12-12-2002 12:15:56','DD-MM-YYYY HH:MI:SS'));

commit;
-- 3: Displaying in ascii sorting  NOT thai sorting
select * from t2sort 
order by NLSSORT(name, 'NLS_SORT = BINARY');


-- 4: Displaying  NLSSORT function that use THAI_DICTIONARY
-- using NLSSORT for Thai sorting
-- 'ฮ นก ฮูก มาหลังเกิด' must be the last one. 'ฮ' is our last charecter
SELECT *  FROM t2sort
ORDER BY NLSSORT(name, 'NLS_SORT = THAI_DICTIONARY');

-- Or  change session environment
alter session set NLS_SORT='THAI_DICTIONARY';
-- and then show the data again
select * from t2sort order by name;

-- What wrong with the CALENDAR System ?
-- 5: Display current CALENDAR system
select sysdate from dual;
-- Current date month name change for thai language
-- Due to step 2 . We inserted data in different format but we store them in same data type
select *  from t2sort;

-- 6: Thai calendar
-- You can chang calendar system from your setting not hand-cord programming.
alter session set NLS_CALENDAR = 'Thai Buddha';
-- Covert by adding 543 by automatically ค.ศ 2002 พ.ศ. 2545
select sysdate from dual;
select *  from t2sort;

-- 7: Change date format
-- Display current NLS parameter again and point to NLS_DATE_FORMAT
select * from nls_session_parameters;
-- Display new date format
alter session set NLS_DATE_FORMAT='DD-MON-YYYY';
select * from t2sort;
-- or using to_char
select to_char(sysdate,'DD-MONTH-YYYY') from dual;


-- End scripts