Oracle NLS_COLLATION_NAME() Function

Oracle NLS_COLLATION_NAME() is a built-in function that returns the corresponding collation name based on the given collation ID.

Oracle NLS_COLLATION_NAME() Syntax

Here’s the syntax for the Oracle NLS_COLLATION_NAME() function:

NLS_COLLATION_NAME(collation_id [, flag ])

Parameters

collation_id

Required. The collation ID. It should be a NUMBER value.

flag

Optional. The optional flag parameter applies only to the Unicode Collation Algorithm (UCA) collations. This parameter determines whether the function returns the short form or long form of the collation name. It must be a character expression that evaluates to the value 'S', 's', 'L', or 'l', with the following meanings:

  • 'S' or 's' - Returns the short form of the collation name.
  • 'L' or 'l' - Returns the long form of the collation name.

If the flag is omitted, the default value is 'L'.

Return Value

The Oracle NLS_COLLATION_NAME() function returns the corresponding collation name based on the given collation ID.

This function returns a VARCHAR2 value. If you specify an invalid collation ID, the function returns null.

If any argument is NULL, NLS_COLLATION_NAME() returns NULL.

Oracle NLS_COLLATION_NAME() Examples

Here are some examples demonstrating the usage of the Oracle NLS_COLLATION_NAME() function.

Basic Usage

The following statement returns the collation name for collation ID 16383:

SELECT
    NLS_COLLATION_NAME(16383)
FROM dual;

Output:

NLS_COLLATION_NAME(16383)
____________________________
BINARY

The following statement returns the collation name for collation ID 147455:

SELECT
    NLS_COLLATION_NAME(66)
FROM dual;

Output:

NLS_COLLATION_NAME(66)
_________________________
UNICODE_BINARY

flag Parameter

The Oracle NLS_COLLATION_NAME() function allows you to specify whether to return the short or long form of the collation name using the flag parameter.

The following example returns the short form of the collation name:

SELECT
    NLS_COLLATION_NAME(208897, 'S')
FROM dual;

Output:

NLS_COLLATION_NAME(208897,'S')
_________________________________
UCA0610_DUCET

The following example returns the long form of the collation name:

SELECT
    NLS_COLLATION_NAME(208897, 'L')
FROM dual;

Output:

NLS_COLLATION_NAME(208897,'L')
___________________________________________
UCA0610_DUCET_S4_VS_BN_NY_EN_FN_HN_DN_MN

If you need to return the long form of the collation name, you can omit the flag parameter as this is the default behavior. The following example illustrates this:

SELECT
    NLS_COLLATION_NAME(208897)
FROM dual;

Output:

NLS_COLLATION_NAME(208897)
___________________________________________
UCA0610_DUCET_S4_VS_BN_NY_EN_FN_HN_DN_MN

NULL Parameter

If any argument is NULL, NLS_COLLATION_NAME() returns NULL.

SET NULL 'NULL';
SELECT
    NLS_COLLATION_NAME(NULL)
FROM dual;

Output:

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Conclusion

Oracle NLS_COLLATION_NAME() is a built-in function that returns the corresponding collation name based on the given collation ID.