1

In Oracle 19c, I want to specify the default value of a column and the collate property at the same time.

create table id_table(
     name varchar2(64) collate binary_ai,
     id varchar2(8) collate binary_ci
);

insert into id_table values('Christopher', 'ABCD1234');

SELECT collation(name), collation(id) from id_table;
COLLATION(NAME) COLLATION(ID)
BINARY_AI BINARY_CI

It was well specified as intended.

but,

create table id_table2(
     name char default 'Y' collate binary_ai,
     id varchar2(8)
);

insert into id_table2 values('c', 'ABCD1234');

SELECT collation(name), collation(id) from id_table2;
COLLATION(NAME) COLLATION(ID)
USING_NLS_COMP USING_NLS_COMP

If you specify the default value and collate together, an unintended value is specified.

The intent was to expect the same values as in the first example.

Are DEFAULT VALUE and COLLATE mutually exclusive?

0 Answers0