PL/SQL UNICODE support tips

This is an excerpt from the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.

Unicode was accepted and implemented in Oracle starting from the version 7. From then on, there were many series of migrations in the encoding methods resulting in a character set with the support of most of the characters used in the world.

The Oracle naming convention for its character set is shown below.

The encoding methods used in Oracle are explained below.

AL24UTF-FSS

This was the first Unicode character set supported by Oracle in the version 7.2 as a database character set. This is an acronym for AL - All languages, 24- Bit size, UTF-FSS - encoding scheme. This followed the UTF-8 encoding methodology in the Unicode standard 1.1 which is unsupported in the Oracle version 9i. The migration of the existing AL24UTF-FSS is to upgrade to UTF8 prior to the version 9i migration.

UTF8

This was the UTF-8 encoded character set in the Oracle version 8 and 8i and it did not follow the Oracle naming convention for the Unicode characters. UTF8 followed the Unicode version of 2.1 between the versions 8.0 and 8.1.6, and was upgraded to the version of 3.0 during 8.1.7 and 9i. Even though the specific supplementary characters were not assigned in the version 3.0, their allocation were already done so that they don't corrupt the actual data inside the database when they are migrated to the version 3.1.

UTFE

This is the UTF8 database character set for the Extended Binary Coded Decimal Interchange Code (EBCDIC) platforms, which is an 8-bit character encoding mainly used on IBM mainframe and IBM midrange computer operating systems.

AL32UTF-8

This is the UTF-8 encoded character set introduced in the Oracle version 9i and continues till 12c as the database character set. This database character set supports the latest version of the Unicode standard with the support for the newly defined supplementary characters which are stored in 32-bit. The database character set supports CHAR, VARCHAR2, CLOB and LONG data types. This does not support the national character set.

AL16UTF-16

This is the UTF-16 encoded character set introduced in the Oracle version 9i and continues till 12c as the national character set. The national character determines the set of SQL data types such as NCHAR, NVARCHAR2 and NCLOB. This does not support the database character set.

Need to learn to program with PL/SQL?  For complete notes on programming in PL/SQL, we recommend the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan. This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code.