Wrong Behavior With COLLATION_CLAUSE = 'NON_DEFAULT' (Doc ID 2892064.1)

Last updated on APRIL 17, 2023

Applies to:

Goal

 Wrong behavior with COLLATION_CLAUSE = 'NON_DEFAULT'

Documentation[1] about DBMS_METADATA says regarding COLLATION_CLAUSE that:

<

NEVER — Collation clauses are never generated.
ALWAYS — Collation clauses are always generated.
NON_DEFAULT — Collation clauses other than USING_NLS_COMP are generated.>>

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_METADATA.html#GUID-0ACD6E3B-C699-469F-8CC3-32F9DA20CA60

But when values distinct that ALWAYS or NEVER you always get the literal value in the DDL clause, ex:

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,
'COLLATION_CLAUSE', 'NON_DEFAULT');
select dbms_metadata.get_ddl('TABLE','MYTABLE','TEST') from dual;

CREATE TABLE "TEST"."MYTABLE"
(
"COL1" VARCHAR2(13 CHAR) COLLATE "NON_DEFAULT" NOT NULL ENABLE,
"COL2" VARCHAR2(3 CHAR) COLLATE "NON_DEFAULT" NOT NULL ENABLE,
"COL3" VARCHAR2(3 CHAR) COLLATE "NON_DEFAULT" NOT NULL ENABLE
) DEFAULT COLLATION "NON_DEFAULT" SEGMENT CREATION IMMEDIATE

You can see COLLATE "NON_DEFAULT" that is a wrong value.

With COLLATE "NON_DEFAULT" only must be generated if the table has a non-default collation (different from NLS_COMP) established.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.