Default NLS Datum- und Number Formte
Das folgende dynamisch generierte SQL-Script erstellt ein File, welches für sämtliche NLS-Territories die Default Datum und Number Formate auslistet. Es zeigt die Anwendung der Oracle Character Funktionen REPLACE, CHR und TO_CHAR. Mittels ALTER SESSION wird für jedes Territory das Datum- und Number Format generiert. Im weiteren wird der Group-Separator, das Dezimalzeichen und das lokale Währungssymbol generiert. Die zur Verfügung stehenden Territories werden aus der Tabelle V$NLS_VALID_VALUES gelesen.
1). Statement das generiert werden muss
prompt TERRITORY: ALGERIA alter session set nls_territory='ALGERIA'; select ' Date format: ' || sysdate,chr(10), 'Group Separator: ' || replace(replace(to_char(1111,'9G999'),'1',''),' ',''),chr(10), ' Decimal Point: ' || replace(replace(to_char(1.1,'9D9'),'1',''),' ',''),chr(10), ' Example: ' || replace(to_char(1234.123,'9G999D999'),' ',''),chr(10), ' Local currency: ' || replace(replace(to_char(1,'L9'),'1',''),' ',''),chr(10), 'International currency: ' || replace(to_char(1,'C9'),'1',''),chr(10) from dual;
Erläuterungen ------------- Format '9G999' liefert Group Separator an spezifizierter Stelle: select to_char(1111,'9G999') from dual; Liefert: " 1,111"
Ersetzen aller '1' durch '':
select replace(' 1,111','1','') from dual;
Liefert: " ,"
Ersetzen aller ' ' durch '':
select replace(' ,',' ','') from dual;
Liefert: ","
2). SQL-Script zum dynamischen generieren des obigen Statements
--
-- Show NLS default parameters for each territory
--
set echo off termout off feed on pages 0 numwidth 10 linesize 80
spool nls_terr.sql
select 'set echo off termout off feed off pages 0' || chr(10) ||
'spool nls_list.lis' || chr(10) ||
'prompt LISTING OF DEFAULT VALUES FOR TERRITORIES' || chr(10) ||
'prompt -----------------------------------------'
from dual
/
select 'prompt TERRITORY: ' || value || chr(10) ||
'alter session set nls_territory=''' || value || ''';' || chr(10) ||
'select '' Date format: '' || sysdate,' || 'chr(10),' || chr(10) ||
'''Group Separator: ''' ||
' || replace(replace(to_char(1111,''9G999''),''1'',''''),'' '',''''),'
|| 'chr(10),' || chr(10) ||
''' Decimal Point: ''' ||
' || replace(replace(to_char(1.1,''9D9''),''1'',''''),'' '',''''),'
|| 'chr(10),' || chr(10) ||
''' Example: ''' ||
' || replace(to_char(1234.123,''9G999D999''),'' '',''''),'
|| 'chr(10),' || chr(10) ||
''' Local currency: ''' ||
' || replace(replace(to_char(1,''L9''),''1'',''''),'' '',''''),'
|| chr(10) || 'chr(10),' ||
'''International currency: ''' ||
' || replace(to_char(1,''C9''),''1'',''''),'
|| 'chr(10)' || chr(10) ||
'from dual;' || chr(10)
from v$nls_valid_values
where parameter = 'TERRITORY'
order by value
/
select 'spool off' from dual / spool off
@nls_terr.sql
3). Generiertes File
LISTING OF DEFAULT VALUES FOR TERRITORIES
----------------------------------------
TERRITORY: ALGERIA
Date format: 13-06-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: DZD
TERRITORY: AMERICA
Date format: 13-JUN-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: $ International currency: USD
TERRITORY: AUSTRIA
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: ÖS International currency: ATS
TERRITORY: BAHRAIN
Date format: 13-06-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: BHD
TERRITORY: BANGLADESH
Date format: 13-06-1998
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ? International currency: BDT
TERRITORY: BRAZIL
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Cr$ International currency: BRC
TERRITORY: BULGARIA
Date format: 1998-06-13
Group Separator: Decimal Point: . Example: 1234.123
Local currency: ?? International currency: BGL
TERRITORY: CANADA
Date format: 98-06-13
Group Separator: Decimal Point: , Example: 1234,123
Local currency: $ International currency: CAD
TERRITORY: CATALONIA
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: PTA International currency: ESP
TERRITORY: CHINA
Date format: 13-JUN-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: $ International currency: CNY
TERRITORY: CIS
Date format: 13.06.98
Group Separator: Decimal Point: , Example: 1234,123
Local currency: ?. International currency: SUR
TERRITORY: CROATIA
Date format: 13.06.98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: kn International currency: HRK
TERRITORY: CYPRUS
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: ? International currency: CYP
TERRITORY: CZECH REPUBLIC
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: K¿ International currency: CZK
TERRITORY: CZECHOSLOVAKIA
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: K¿s International currency: CSK
TERRITORY: DENMARK
Date format: 98-06-13
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Kr International currency: DKK
TERRITORY: DJIBOUTI
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: DJF
TERRITORY: EGYPT
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: EGP
TERRITORY: ESTONIA
Date format: 13.06.1998
Group Separator: Decimal Point: , Example: 1234,123
Local currency: kr International currency: EEK
TERRITORY: FINLAND
Date format: 13.06.1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: mk International currency: FIM
TERRITORY: FRANCE
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: F International currency: FRF
TERRITORY: GERMANY
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: DM International currency: DEM
TERRITORY: GREECE
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: ??? International currency: GRD
TERRITORY: HONG KONG
Date format: 13-JUN-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: $ International currency: HKD
TERRITORY: HUNGARY
Date format: 98-Jun-13
Group Separator: Decimal Point: . Example: 1234.123
Local currency: Ft International currency: HUF
TERRITORY: ICELAND
Date format: 13.06.1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: kr. International currency: IKR
TERRITORY: INDONESIA
Date format: 13-06-1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Rp International currency: IDR
TERRITORY: IRAQ
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: IQD
TERRITORY: ISRAEL
Date format: 13-Jun-1998
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?? International currency: ILS
TERRITORY: ITALY
Date format: 13-Jun-98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: L. International currency: ITL
TERRITORY: JAPAN
Date format: 98-06-13
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: \ International currency: JPY
TERRITORY: JORDAN
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: JOD
TERRITORY: KOREA
Date format: 98/06/13
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ? International currency: KRW
TERRITORY: KUWAIT
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: KWD
TERRITORY: LATVIA
Date format: 1998.06.13
Group Separator: Decimal Point: , Example: 1234,123
Local currency: Ls International currency: LVL
TERRITORY: LEBANON
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: LBP
TERRITORY: LIBYA
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: LYD
TERRITORY: LITHUANIA
Date format: 1998.06.13
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Lt International currency: LTL
TERRITORY: MALAYSIA
Date format: 13/06/1998
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: RM International currency: MYR
TERRITORY: MAURITANIA
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: MRO
TERRITORY: MEXICO
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: $ International currency: MXP
TERRITORY: MOROCCO
Date format: 13-06-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: MAD
TERRITORY: NORWAY
Date format: 13.06.1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Kr. International currency: NOK
TERRITORY: OMAN
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: OMR
TERRITORY: POLAND
Date format: 98/06/13
Group Separator: Decimal Point: , Example: 1234,123
Local currency: z¿ International currency: PLN
TERRITORY: PORTUGAL
Date format: 98.06.13
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: $ International currency: PTE
TERRITORY: QATAR
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: QAR
TERRITORY: ROMANIA
Date format: 13-06-1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: LEI International currency: ROL
TERRITORY: SAUDI ARABIA
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: SAR
TERRITORY: SLOVAKIA
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Sk International currency: SKK
TERRITORY: SLOVENIA
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: K¿s International currency: CSK
TERRITORY: SOMALIA
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: SOS
TERRITORY: SPAIN
Date format: 13/06/98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Pts International currency: ESP
TERRITORY: SUDAN
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: SDP
TERRITORY: SWEDEN
Date format: 1998-06-13
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: Kr International currency: SEK
TERRITORY: SWITZERLAND
Date format: 13.06.98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: SF International currency: CHF
TERRITORY: SYRIA
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: SYP
TERRITORY: TAIWAN
Date format: 13-JUN-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: $ International currency: TWD
TERRITORY: THAILAND
Date format: 13-JUN-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: $ International currency: THB
TERRITORY: THE NETHERLANDS
Date format: 13-06-98
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: f International currency: NLG
TERRITORY: TUNISIA
Date format: 13-06-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: TND
TERRITORY: TURKEY
Date format: 13/06/1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: TL International currency: TRL
TERRITORY: UKRAINE
Date format: 13.06.1998
Group Separator: Decimal Point: , Example: 1234,123
Local currency: ???. International currency: UAK
TERRITORY: UNITED ARAB EMIRATES
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: AED
TERRITORY: UNITED KINGDOM
Date format: 13-Jun-98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ? International currency: GBP
TERRITORY: VIETNAM
Date format: 13-06-1998
Group Separator: . Decimal Point: , Example: 1.234,123
Local currency: ¿ International currency: VNd
TERRITORY: YEMEN
Date format: 13/06/98
Group Separator: , Decimal Point: . Example: 1,234.123
Local currency: ?.?. International currency: YER