Domains in 23ai Schema Development

 I had heard of Data Use Case Domains in 23ai. However, the New Features nd Database Concepts documentation didn't provide enough examples for me to build on.

However, this blog post by Ulrike Schwinn  (which was shared by @thatjeffsmith  on X) helped me explore domains.

In this demo, I am using the Pre-Seeded Domains.  However, you can see the example posted by Ulrike Schwimm  or even read in the Database Concepts documentation  to help build your own custom Domains.

A Data Use Case Domain is like defining a Custom DataType such that only valid values are permitted.  The Domain name can be a self-identifier (just as "DATE" or "NUMBER" identifies the type of data being stored).

Here  is my demonstration  (I also use the Annotations feature -- the Data Use Case Domains documentation links above also lead to this feature)

SQL> set pages600 linesize 132
SQL> col contact_person format a32
SQL> col contact_email format a24
SQL>
SQL> drop table forex_rates_contacts;

Table dropped.

SQL>
SQL>
SQL> create table forex_rates_contacts
  2  (
  3   country_iso_code  varchar2(3) domain country_code_d,  -- preseeded SYS domain
  4   currency_code varchar2(3) domain currency_code_d, -- preseeded SYS domain
  5   contact_person varchar2(128),
  6   contact_email     varchar2(4000) domain email_d -- preseed SYS domain
  7  )
  8  annotations (display 'Forex Contact Persons')
  9  /

Table created.

SQL>
SQL> desc forex_rates_contacts
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 COUNTRY_ISO_CODE                                                                  VARCHAR2(3) SYS.COUNTRY_CODE_D
 CURRENCY_CODE                                                                     VARCHAR2(3) SYS.CURRENCY_CODE_D
 CONTACT_PERSON                                                                    VARCHAR2(128)
 CONTACT_EMAIL                                                                     VARCHAR2(4000) SYS.EMAIL_D

SQL>
SQL>
SQL> set long 1000
SQL> set longc 1000
SQL> set serveroutput on
SQL>
SQL> rem  FROM clause is no longer required in 23ai
SQL> select dbms_metadata.get_ddl('TABLE','FOREX_RATES_CONTACTS','HEMANT');

DBMS_METADATA.GET_DDL('TABLE','FOREX_RATES_CONTACTS','HEMANT')
------------------------------------------------------------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."FOREX_RATES_CONTACTS"
   (    "COUNTRY_ISO_CODE" VARCHAR2(3) DOMAIN "SYS"."COUNTRY_CODE_D",
        "CURRENCY_CODE" VARCHAR2(3) DOMAIN "SYS"."CURRENCY_CODE_D",
        "CONTACT_PERSON" VARCHAR2(128),
        "CONTACT_EMAIL" VARCHAR2(4000) DOMAIN "SYS"."EMAIL_D"
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
  ANNOTATIONS("DISPLAY" 'Forex Contact Persons')


SQL>
SQL>
SQL>
SQL> rem  MULTI-ROW Insert
SQL> insert into forex_rates_contacts
  2  values
  3  ('US','USD','Mr Unknown','unknown@nowhere.gov'),
  4  ('IN','INR','Someone at RBI','someone@rbi.gov.in')
  5  /

2 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from forex_rates_contacts
  2  order by country_iso_code
  3  /

COU CUR CONTACT_PERSON                   CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN  INR Someone at RBI                   someone@rbi.gov.in
US  USD Mr Unknown                       unknown@nowhere.gov

SQL>
SQL> -- Note that the country_code_d and currency_code_d do not check validity against really ISO codes
SQL> -- thus, it does not disallow "ZZ" and "ZZZ"
SQL> insert into forex_rates_contacts
  2  values
  3  ('ZZ','ZZZ','Mr Unknown','unknown@nowhere.zz')
  4  /

1 row created.

SQL>
SQL> select * from forex_rates_contacts
  2  order by country_iso_code
  3  /

COU CUR CONTACT_PERSON                   CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN  INR Someone at RBI                   someone@rbi.gov.in
US  USD Mr Unknown                       unknown@nowhere.gov
ZZ  ZZZ Mr Unknown                       unknown@nowhere.zz

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- But the rules for email validation are encoded
SQL> insert into forex_rates_contacts
  2  values
  3  ('UK','GBP','Mr Someone','someone@x')
  4  /
insert into forex_rates_contacts
*
ERROR at line 1:
ORA-11534: check constraint (HEMANT.SYS_C0013464) involving column CONTACT_EMAIL due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/


SQL>
SQL> select * from forex_rates_contacts
  2  order by country_iso_code
  3  /

COU CUR CONTACT_PERSON                   CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN  INR Someone at RBI                   someone@rbi.gov.in
US  USD Mr Unknown                       unknown@nowhere.gov
ZZ  ZZZ Mr Unknown                       unknown@nowhere.zz

SQL>
SQL> spool off

I haven't added my own custom Domains but used the PreSeeded domains for Country, Currency and Email.  Look at "10.1.12 Built-In Use Case Domains" in the documentation.