Oracle | Collation Functions

Oracle Collation Functions Version 21c

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose These functions return collation setting information.

Note

Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions

Dependencies

STANDARD

COLLATION

Returns the name of the derived collation named collations and pseudo-collations. If the derived collation is a Unicode Collation Algorithm (UCA) collation, then the function returns the long form of its name. If the derived collation is undefined due to a collation conflict returns NULL. COLLATION(<expression>) RETURN VARCHAR2;

SQL> CREATE TABLE id_table (

        2  coll_name VARCHAR2(64) COLLATE BINARY_AI,

        3  coll_id VARCHAR2(8)    COLLATE BINARY_CI);

      CREATE TABLE id_table (

      *

      ERROR at line 1:

      ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

      

      ALTER SYSTEM SET max_string_size=extended COMMENT='Collation enabled 15-Mar-2018' SID='*' SCOPE=SPFILE;

      SHUTDOWN IMMEDIATE;

      STARTUP UPGRADE;

      SHUTDOWN IMMEDIATE;

      STARTUP;

      

      CREATE TABLE id_table (

      coll_name VARCHAR2(64) COLLATE BINARY_AI,

      coll_id VARCHAR2(8) COLLATE BINARY_CI);

      

      INSERT INTO id_table VALUES('Morgan', 'ZZYZX4242');

      

      SELECT collation(name), collation(id)

      FROM id_table;

      

      COLLATION COLLATION

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

      BINARY_AI BINARY_CI

NLS_COLLATION_ID

Takes as its argument a collation name and returns the corresponding collation ID number NLS_COLLATION_ID(<expression>) RETURN NUMBER;

SELECT nls_collation_id('BINARY_CI')

      FROM dual;

      

      NLS_COLLATION_ID('BINARY_CI')

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

                             147455

NLS_COLLATION_NAME

Takes as its argument a collation name and returns the corresponding collation name NLS_COLLATION_NAMEk(<expression>) RETURN VARCHAR2;

SELECT nls_collation_name(81919)

      FROM dual;

      

      NLS_COLLA

      ---------

      BINARY_AI

      

      

      SELECT nls_collation_name(208897,'S')

      FROM dual;

      

      NLS_COLLATION

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

      UCA0610_DUCET

      

      

      SELECT nls_collation_name(208897,'L')

      FROM dual;

      

      NLS_COLLATION_NAME(208897,'L')

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

      UCA0610_DUCET_S4_VS_BN_NY_EN_FN_HN_DN_MN