Boolean Datatype 23c (Was: Order of 'default' with boolean produces invalid SQL)

[dmcghan]

This Quick SQL:

foo
  bar /boolean /default y

Creates this SQL:

create table foo (
    id     number generated by default on null as identity
           constraint foo_id_pk primary key,
    bar    varchar2(1 char) constraint foo_bar
           check (bar in ('Y','N')) 
);

Oracle throws ORA-00907: missing right parenthesis.

If default on null 'y' is moved before the constraint then the statement runs fine.

[vadim-tropashko]

1.2.1:

create table foo (
    id     number generated by default on null as identity
           constraint foo_id_pk primary key,
    bar    varchar2(1 char) default on null 'y'
           constraint foo_bar check (bar in ('Y','N'))
);

[neilfernandez]

@vadim-tropashko this fixes the syntax issue, but will we ever have boolean support for database 23 and newer?

Let me know if separate issue needed for that please.

[vadim-tropashko]

Boolean datatype it is

[vadim-tropashko]

#boolean: yn           -- default
#boolean: native     -- 23c

[neilfernandez]

Brilliant. So if my db setting < 23, even if I put boolean, can we convert it to varchar2 for that scenario?

[vadim-tropashko]

db parameter is not used. If there is another db-dependent feature, then we might reintroduce it to influence the dependent options default values in bulk

[vadim-tropashko]

The db option is right there in the doc; will nuke it until further notice (that is, more than one db dependent parameter appearing in the future).