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

Syntax

The syntax goes like this:

NLS_COLLATION_ID(expr)

Where expr is the collation name, of type VARCHAR2.

Example

Here’s a basic example:

SELECT NLS_COLLATION_ID('XAZERBAIJANI')
FROM DUAL;

Result:

70

Here are some more examples:

SELECT 
    NLS_COLLATION_ID('UCA0620_THAI') AS "1",
    NLS_COLLATION_ID('UCA0620_TSPANISH') AS "2",
    NLS_COLLATION_ID('CANADIAN_M') AS "3",
    NLS_COLLATION_ID('FRENCH_M') AS "4"
FROM DUAL;

Result:

        1         2       3       4 
_________ _________ _______ _______ 
   208920    208917    4116    4112

Invalid Collation

Passing an invalid collation name results in null:

SET NULL 'null';
SELECT NLS_COLLATION_ID('Oops!')
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_ID(null)
FROM DUAL;

Result:

null

Incorrect Argument Count

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

SELECT NLS_COLLATION_ID()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_COLLATION_ID()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT NLS_COLLATION_ID('JAPANESE_M', 'THAI_M')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_COLLATION_ID('JAPANESE_M', 'THAI_M')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: