Collation (also called sort ordering) determines if a character string equals, precedes, or follows another string when the two strings are compared and sorted. Oracle Database collations order strings following rules for sorted text used in different languages.
COMPATIBLE initialization parameter must be set to at least 12.2.0, and
MAX_STRING_SIZE must be set to
EXTENDED for collation declarations to be allowed in these SQL statements.
The default_collation_clause can appear in a package specification, a standalone type specification, and in standalone subprograms.
The default collation of a procedure, function, package, type, or trigger must be
USING_NLS_COMP. The default_collation_clause explicitly declares the default collation of a PL/SQL unit to be
USING_NLS_COMP. Without this clause, the unit inherits its default collation from the effective schema default collation. If the effective schema default collation is not
USING_NLS_COMP, the unit is invalid.
If the session parameter
DEFAULT_COLLATION is set, the effective schema default collation is the value of this parameter. The value of the parameter can be checked by querying
SYS_CONTEXT('USERENV', 'SESSION_DEFAULT_COLLATION'). The function returns
DEFAULT_COLLATION is not set. The value of the parameter
DEFAULT_COLLATION can be set with the statement:
ALTER SESSION SET DEFAULT_COLLATION = collation_option;
If the session parameter
DEFAULT_COLLATION is not set, the effective schema default collation is the declared default collation of the schema in which you create the PL/SQL unit. The default collation of a schema can be found in the static data dictionary *_USERS views. It can be set with the DDL statements
CREATE USER and
The session parameter
DEFAULT_COLLATION can be unset with the statement:
ALTER SESSION SET DEFAULT_COLLATION = NONE;
Package body and type body use the default collation of the corresponding specification. All character data containers and attributes in procedures, functions and methods, including parameters and return values, behave as if their data-bound collation were the pseudo-collation
It cannot be specified for nested or packaged subprograms or for type methods.
If the resulting default object collation is different from
USING_NLS_COMP, the database object is created as invalid with a compilation error.
ALTER COMPILE statement is issued for a PL/SQL unit with the
REUSE SETTINGS clause, the stored default collation of the database object being compiled is not changed.
ALTER COMPILE statement is issued without the
REUSE SETTINGS clause, the stored default collation of the database object being compiled is discarded and the effective schema default collation for the object owner at the time of execution of the statement is stored as the default collation of the object, unless the PL/SQL unit contains the
DEFAULT COLLATION clause. If the resulting default collation is not
USING_NLS_COMP, a compilation error is raised.
ALTER COMPILE statement for a package or type body references the stored collation of the corresponding specification.
In other chapters: