How to Find Character Set?

Oracle recommends Unicode AL32UTF8 as the database character set.

Character Set Control:

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

PARAMETER                     VALUE

------------------------------ ------------------------------

NLS_CHARACTERSET       AL32UTF8

It should return the value AL32UTF8.

Another option is to run the following SQL :

SQL> select * from nls_database_parameters;

PARAMETER VALUE
 ------------------------------ -------------------- ----------
 NLS_RDBMS_VERSION 12.1.0.2.0
 NLS_NCHAR_CONV_EXCP FALSE
 NLS_LENGTH_SEMANTICS BYTE
 NLS_COMP BINARY
 NLS_DUAL_CURRENCY $
 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
 NLS_TIME_FORMAT HH.MI.SSXFF AM
 NLS_SORT BINARY
 NLS_DATE_LANGUAGE AMERICAN
 NLS_DATE_FORMAT DD-MON-RR
 NLS_CALENDAR GREGORIAN
 NLS_NUMERIC_CHARACTERS.,
 NLS_NCHAR_CHARACTERSET AL16UTF16
 NLS_CHARACTERSET AL32UTF8
 NLS_ISO_CURRENCY AMERICA
 NLS_CURRENCY $
 NLS_TERRITORY AMERICA
 NLS_LANGUAGE AMERICAN

20 rows selected.

SQL>

The most important settings have been highlighted, which are:

  • NLS_CHARACTERSET = AL32UTF8
  • NLS_NCHAR_CHARACTERSET = AL16UTF16
  • NLS_RDBMS_VERSION = 12.1.0.2.0

Character set is used by CHAR, VARCHAR2 and CLOB data types and national character set is used by NCHAR, NVARCHAR2 and NCLOB data types.

  • Use char when the sizes of the column data entries are consistent.
  • Use varchar when the sizes of the column data entries vary considerably.
  • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
  • A CLOB (character large object) value can be up to 2,147,483,647 characters long.

Screen Shot 2017-08-17 at 18.45.37

Hits: 9

Leave a Reply

Your email address will not be published. Required fields are marked *