In Oracle Database, the NLS_COLLATION_NAME() function returns the name of a given collation, based on its ID. You pass the collation ID when calling the function, and it returns the collation name.

Syntax

The syntax goes like this:

NLS_COLLATION_NAME(expr [, flag ])

Where expr is the collation ID of type NUMBER.

The flag argument is an optional argument that only applies to Unicode Collation Algorithm (UCA) collations. It determines whether the function should return the short form or long form of the collation name.

The flag argument can be one of the following:

Flag	Description
'S' or 's'	Returns the short form of the collation name.
'L' or 'l'	Returns the long form of the collation name.

The default value for this flag is 'L'.

Example

Here’s a basic example:

SELECT NLS_COLLATION_NAME(70)
FROM DUAL;

Result:

XAZERBAIJANI

Here are some more examples:

SELECT 
    NLS_COLLATION_NAME(4112) AS "1",
    NLS_COLLATION_NAME(4116) AS "2"
FROM DUAL;

Result:

          1             2 
___________ _____________ 
FRENCH_M    CANADIAN_M   

Long/Short Form Flag

As mentioned, we can pass an optional flag to determine whether to return the short form or long form of the collation name.

SELECT 
    NLS_COLLATION_NAME(208920, 'L') AS "Long",
    NLS_COLLATION_NAME(208920, 'S') AS "Short"
FROM DUAL;

Result:

                                      Long           Short 
__________________________________________ _______________ 
UCA0620_THAI_S4_VS_BN_NY_EN_FN_HN_DN_MN    UCA0620_THAI   

The default value for this flag is 'L'. So if we omit the flag, the long form is returned:

SELECT 
    NLS_COLLATION_NAME(208920) AS "Default"
FROM DUAL;

Result:

                                   Default 
__________________________________________ 
UCA0620_THAI_S4_VS_BN_NY_EN_FN_HN_DN_MN   

This flag only applies to Unicode Collation Algorithm (UCA) collations though.

If we apply the flag to a non-UCA collation, we see no difference:

SELECT 
    NLS_COLLATION_NAME(4112, 'L') AS "1",
    NLS_COLLATION_NAME(4112, 'S') AS "2"
FROM DUAL;

Result:

          1           2 
___________ ___________ 
FRENCH_M    FRENCH_M   

Invalid Collation

Passing an invalid collation name results in null:

SET NULL 'null';
SELECT NLS_COLLATION_NAME(4113)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Null Argument

Passing null returns null:

SET NULL 'null';
SELECT NLS_COLLATION_NAME(null)
FROM DUAL;

Result:

null

Incorrect Argument Count

Calling the function without passing any arguments results in an error:

SELECT NLS_COLLATION_NAME()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_COLLATION_NAME()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT NLS_COLLATION_NAME(4112, 'S', 'L')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_COLLATION_NAME(4112, 'S', 'L')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: