Oracle COLLATION() Function

Oracle COLLATION() is a built-in function that returns the name of the derived collation for a given expression.

Oracle COLLATION() Syntax

Here is the syntax for the Oracle COLLATION() function:

COLLATION(expr)

Parameters

expr

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

Return Value

The Oracle COLLATION() function returns the name of the derived collation for the given expression.

This function returns both named collations and pseudo-collations. If the derived collation is a Unicode Collation Algorithm (UCA) collation, the function returns its long-form name. This function is evaluated at compile time of the SQL statement that contains it. If the derived collation is undefined due to a collation conflict during the evaluation of expr, the function returns NULL.

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

Oracle COLLATION() Examples

Here are several examples that demonstrate the usage of Oracle COLLATION() function.

Basic Usage

SELECT COLLATION('Hello')
FROM dual;

Output:

COLLATION('HELLO')
_____________________
USING_NLS_COMP

Let’s pass an expression with a specified sorting order to the COLLATION() function and see what happens:

SELECT COLLATION('Hello' COLLATE LATIN_AI)
FROM dual;

Output:

COLLATION('HELLO'COLLATELATIN_AI)
____________________________________
LATIN_AI

In this example, we pass the expression 'Hello' COLLATE LATIN_AI, which specifies the sorting order used by 'Hello' as LATIN_AI, and then COLLATION() returns LATIN_AI.

NULL Parameters

If the parameter is NULL, COLLATION() returns the default sorting order.

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

Output:

COLLATION(NULL)
__________________
USING_NLS_COMP

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

Conclusion

Oracle COLLATION() is a built-in function that returns the name of the derived sorting order for a given expression.