COLLATION() Function in Oracle

In Oracle Database, the COLLATION() function returns the name of the derived collation for its argument.

Syntax

The syntax goes like this:

COLLATION(expr)

Where expr must evaluate to a character string of type CHAR, VARCHAR2, LONG, NCHAR, or NVARCHAR2.

Example

Here’s a basic example:

SELECT COLLATION('Boat')
FROM DUAL;

Result:

USING_NLS_COMP

Example with the COLLATE Clause

Here’s what happens when we use the COLLATE clause to explicitly specify the collation:

SELECT COLLATION('Boat' COLLATE LATIN_AI) 
FROM DUAL;

Result:

LATIN_AI

Consideration When using NLS_SORT

The COLLATION() function returns only the data-bound collation, and not the dynamic collation set by the NLS_SORT parameter.

Example:

SELECT COLLATION('Boat' COLLATE USING_NLS_SORT) 
FROM DUAL;

Result:

USING_NLS_SORT

You can use the SYS_CONTEXT() function to return the value of the NLS_SORT parameter:

SELECT SYS_CONTEXT('USERENV','NLS_SORT')
FROM DUAL;

Result:

BINARY

However, any suffix such as _CI, _AI, and _CS is returned:

SELECT 
    COLLATION('Boat' COLLATE USING_NLS_SORT_CI) AS "_CI",
    COLLATION('Boat' COLLATE USING_NLS_SORT_CS) AS "_CS",
    COLLATION('Boat' COLLATE USING_NLS_SORT_AI) AS "_AI"
FROM DUAL;

Result:

                 _CI                  _CS                  _AI 
____________________ ____________________ ____________________ 
USING_NLS_SORT_CI    USING_NLS_SORT_CS    USING_NLS_SORT_AI    

Null Argument

The collation is returned, even when passing null:

SELECT COLLATION(null)
FROM DUAL;

Result:

USING_NLS_COMP

Incorrect Argument Count

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

SELECT COLLATION()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT COLLATION()
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 COLLATION('Boat', 'Dang')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT COLLATION('Boat', 'Dang')
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: