oracle031A brief overview of the character set character set: is the corresponding table of characters and encodings. 1. function and meaning of character set
Character encoding
Use occasions
Database Character Set
(1) used to store char, VARCHAR2, CLOB, long and other types of data
(2) to indicate such as table name, column name, and PL/SQL variable, etc.
(3) used to store SQL and PL + + program units, etc.
National Character Set
(1) to store nchar, NVARCHAR2, NCLOB and other types of data
2, Client OS character set, Nls_lang settings, server-side OS character set, Oracle database character set
The relationship between character sets
Set character sets correctly
3, the character set after the problem of the judgment process
The wrong character encoding is stored
The correct character encoding is stored
4. Locale, locale-a, CHCP
5. Character Set naming
Oracle's character set naming follows the following naming conventions:
<language><bit size><encoding>
That is: < language >< bits >< encoding >
For example: ZHS16GBK in GBK encoded format, 16-bit (two-byte) Simplified Chinese character set
US7ASCII//Americans Select a character set that can only be used by the United States with no more than 128 characters
zhs16cgb231280//Chinese use character set, store Chinese part character
Al32utf8//oracle Database Character Set
Af16utf16//oracle Select national Character Set
zhs16gbk//China uses a character set that is a superset of all Chinese characters, which are zhs16chb231280, not strictly superset utf8//
Hyper-Set Strict superset
Nls_lang=<language>_<territory>.<client character set>
Language: Display Oracle message, checksum, date name
Territory: Specify default date, number, currency, and other formats
Client Character Set: Specifies the character set that clients will use
For example: Nls_lang=american_america. Us7ascii
American is the language is the login to the database, the database itself is prompted by the information is that language is not character encoding;America is a region that shows different information in different regions, such as time zone, currency, etc.;Us7ascii is the client character set
Oracle provides several NLS parameters to customize the database and the user's computer to fit the local format, such as Nls_language,nls_date_format,nls_calender, which can be viewed by querying the following data dictionary or v$ view.
nls_database_parameters--Display database current NLS parameter values, including database character set values
nls_session_parameters--Displays the parameters set by Nls_lang or the parameter values after alter SESSION (not including the client character set by Nls_lang)
nls_instance_paramete--display parameters defined by parameter file Init<sid>.ora v$nls_parameters--display database current NLS parameter values
SELECT * FROM Nls_session_parameters
nls_language Simplified Chinese nls_territory China Nls_currency ¥ nls_iso_currency nls_numeric_characters ., nls_calendar GREGORIAN NLS_ date_format dd-mon-rr nls_date_language SIMPLIFIED CHINESE nls_sort BINARY nls_time_format hh.mi. Ssxff AM nls_timestamp_format dd-mon-rr hh.mi. Ssxff AM nls_time_tz_format hh.mi. Ssxff AM TZR nls_timestamp_tz_format dd-mon-rr hh.mi. Ssxff AM TZR Nls_dual_currency ¥ nls_comp BINARY NLS_ length_semantics BYTE nls_nchar_conv_excp FALSE |
SELECT * FROM Nls_database_parameters
Nls_language AMERICAN Nls_territory AMERICA Nls_currency $ Nls_iso_currency AMERICA Nls_numeric_characters., Nls_characterset ZHS16GBK Nls_calendar Gregorian Nls_date_format DD-MON-RR Nls_date_language AMERICAN Nls_sort BINARY Nls_time_format HH.MI. Ssxff AM Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM Nls_time_tz_format HH.MI. Ssxff AM TZR Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR Nls_dual_currency $ Nls_comp BINARY Nls_length_semantics BYTE NLS_NCHAR_CONV_EXCP FALSE Nls_nchar_characterset AL16UTF16 Nls_rdbms_version 11.2.0.1.0 |
Select Userenv (' language ') from dual;//queries the client's
USERENV (' LANGUAGE ') ---------------------------------------------------- American_america. Zhs16gbk |
Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual;
Nls_charset_name (To_number (' 0354 ', ' XXXX ') ---------------------------------------- Zhs16gbk |
Echo $NLS _lang
sql>!echo $NLS _lang American_america.zhs16gbk |
Select To_char (nls_charset_id (' ZHS16GBK '), ' XXXX ') from dual;
Select dump (' abc ', 1016) from dual;
DUMP (' ABC ', 1016) -------------------------------------------- typ=96 len=3 characterset=zhs16gbk:61,62,63
Sql> |
SELECT * FROM v$nls_valid_values;//This view lists all valid the values for NLS parameters.
If the software has a character set, then the system's character set will be invalidated, and if the software does not have a character set, then use the operating system's character set
All of Oracle's character set conversions are the Oracle-side conversion Oracle accepts all the characters that have been encoded in the Oracle database. Oracle stores an encoding format stored in a data base that is encoded using the encoding used by the end of the encoding, if the same is stored directly , the transcoding is different and the client only encodes
The Nls_lang parameter is consistent with the character set of the client operating system. Nls_lang equivalent to Oracle dealing with SQL developer and sqlplus like no character set
A brief overview of character sets