Add support for Oracle 23ai enumeration domains

[lukaseder]

A feature that hasn't been announced earlier in Oracle 23c (now 23ai) is enumeration domains!

• https://blogs.oracle.com/sql/post/list-months-of-the-year-or-days-of-the-week-in-oracle-sql-with-enumeration-domains

We'll add support for this as well in:

• DDL
• Parser
  • Specific optional USECASE keyword. Meaning unclear, see: https://forums.oracle.com/ords/apexds/post/create-usecase-domain-isn-t-explained-in-23ai-documentation-2024
• Code generator
  • Domains are stored as their ordinals (NUMBER, starting from 1) by default
  • If an alias is provided, then that defines the domain type (any data type, including e.g. dates!)
  • A special "domain table" by the name of the domain (described by ALL_DOMAINS, ALL_DOMAIN_COLS) is created. It isn't listed in ALL_TABLES, though!
    • It lists all aliases for a value, without saying which alias is the important one
• Runtime
  • The domain alias is being returned when querying an enum column. This could be an ordinal, for example!

After a quick glance, it seems quite different from PostgreSQL's implementation:

-- Ordinal enums
create domain dow_n as enum ( 
  mon, tue, wed, thu, fri, sat, sun
);

create domain dow_s as enum ( 
  monday    = mon = 'MO', 
  tuesday   = tue = 'TU', 
  wednesday = wed = 'WE', 
  thursday  = thu = 'TH', 
  friday    = fri = 'FR', 
  saturday  = sat = 'SA', 
  sunday    = sun = 'SU'
);

And then:

select * from dow_n;

Produces:

|ENUM_NAME|ENUM_VALUE|
|---------|----------|
|MON      |1         |
|TUE      |2         |
|WED      |3         |
|THU      |4         |
|FRI      |5         |
|SAT      |6         |
|SUN      |7         |

Whereas:

select * from dow_s;

Is:

|ENUM_NAME|ENUM_VALUE|
|---------|----------|
|MONDAY   |MO        |
|MON      |MO        |
|TUESDAY  |TU        |
|TUE      |TU        |
|WEDNESDAY|WE        |
|WED      |WE        |
|THURSDAY |TH        |
|THU      |TH        |
|FRIDAY   |FR        |
|FRI      |FR        |
|SATURDAY |SA        |
|SAT      |SA        |
|SUNDAY   |SU        |
|SUN      |SU        |

Also:

select dow_s.mon;

Is:

|MON|
|---|
|MO |

And:

create table t (n dow_n, s dow_s);
insert into t values (1, dow_s.mon);
select domain_display(n), domain_display(s) from t;

Is:

|DOMAIN_DISPLAY(N)|DOMAIN_DISPLAY(S)|
|-----------------|-----------------|
|MON              |MONDAY           |

---

Related work:

• Add support for Oracle 23ai's DDL DOMAIN type reference syntax <base-type> DOMAIN <domain-name> #16645