Oracle | Domains

Oracle Domain Objects Version 23ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose SQL Domains are objects that can be used to define constraints, display, ordering and annotations independent of a specific object and that, much like a Class definition in an object oriented language, allow common attributes to be inherited by the columns (relational or JSON) of one or more tables.

I have opened multiple bugs on this new object type but what I expect to come back as a response is that "it all of it works but most of it does not work in SQL*Plus."

Note Note: The sample code below is copied from the Oracle online documentation and blogs as this page is based on the 23.2 Beta version and not the GA release. These examples will be replaced by Library generated examples after we have had a chance to work with that release. Thank you for your understanding and patience.

https://blogs.oracle.com/coretec/post/less-coding-with-sql-domains-in-23c

Dependencies

ALL_DOMAINS	DOMAIN$	KU$_PARTITION_VIEW
ALL_DOMAIN_COLS	KU$10_2_FHTABLE_VIEW	KU$_PFHTABLE_VIEW
ALL_DOMAIN_CONSTRAINTS	KU$ACPTABLE_VIEW	KU$_PHTABLE_VIEW
CDB_DOMAINS	KU$_DOMAIN_ASSOCIATION_VIEW	KU$_PIOTABLE_VIEW
CDB_DOMAIN_COLS	KU$_DOMAIN_VIEW	KU$_SUBPARTITION_VIEW
CDB_DOMAIN_CONSTRAINTS	KU$_FHTABLE_VIEW	USER_DOMAINS
DBA_DOMAINS	KU$_HTABLE_VIEW	USER_DOMAIN_COLS
DBA_DOMAIN_COLS	KU$_IOTABLE_VIEW	USER_DOMAIN_CONSTRAINTS
DBA_DOMAIN_CONSTRAINTS

Create Domain (new 23ai)

Basic CREATE DOMAIN [schema_name.] <domain_name> AS <data_type>;

CREATE DOMAIN uwphone_domain AS VARCHAR2(14);

Basic with IF NOT EXISTS clause CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type>;

CREATE DOMAIN IF NOT EXISTS uwphone_domain AS VARCHAR2(14);

Basic with Strict CREATE DOMAIN [schema_name.] <domain_name> AS <data_type> STRICT;

CREATE DOMAIN uwphone_domain AS VARCHAR2(14) STRICT;

Basic with DEFAULT ON NULL clause CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> DEFAULT ON NULL <default_value>;

CREATE DOMAIN uwphone_domain AS VARCHAR2(14)

      DEFAULT ON NULL '(000) 000-0000';

Basic with Constraint clause CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> CONSTRAINT <constraint_name> CHECK (<expression>) ..]*;

CREATE DOMAIN uwphone_domain AS VARCHAR2(14)

      CONSTRAINT cc_phone CHECK (

        REGEXP_LIKE(uwphone_domain, '^([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$'));

Basic with Constraint ENABLE/DISABLE clause

The location where this metadata is stored in the data dictionary has not been identified CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> CONSTRAINT <constraint_name> CHECK (<expression>) [<ENABLE | DISABLE>] ..]*;

CREATE DOMAIN uwphone_domain AS VARCHAR2(14)

      CONSTRAINT cc_phone CHECK (

        REGEXP_LIKE(uwphone_domain, '^([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$')) DISABLE;

VALIDATE is the JSON equivalent of a relational Check Constraint. CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> VALIDATE USING <json_schema_string>;

TBD

Basic with Constraint COLLATE clause CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> COLLATE <collation_expression>;

CREATE DOMAIN uwphone_domain AS VARCHAR2(14)

      COLLATE binary_ci;

      ERROR at line 1:

      ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

      

      MAX_STRING_SIZE was set to EXTENDED, a bug has been filed.

Basic with Constraint DISPLAY clause CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> DISPLAY <data_display_expression>;

TBD: Examples copied from the docs do not appear to work.

Basic with Constraint ORDER clause CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> ORDER <order_by_expression>;

TBD: Examples copied from the docs do not appear to work.

Complete CREATE DOMAIN [IF NOT EXISTS] [schema_name.] <domain_name> AS <data_type> [STRICT] [DEFAULT [ON NULL..] <expression>] [[NOT] NULL] [CONSTRAINT [Name] CHECK (<expression>) [<ENABLE | DISABLE>] ..]* [VALIDATE USING <json_schema_string>] [COLLATE collation] [DISPLAY <expression>] [ORDER <expression>];

TBD: Examples copied from the docs do not appear to work. Further work will be done after the source of the issue is identified.

Alter Domain (new 23ai)

Add a display expression to a Domain object ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> ADD DISPLAY <display_expression>;

ALTER DOMAIN day_of_week ADD DISPLAY INITCAP(day_of_week);

Add an order expression to a Domain object ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> ADD ORDER <order_expression>;

ALTER DOMAIN year_of_birth ADD ORDER FLOOR(year_of_birth/100);

Drops a Domain object's Display expression ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> DROP DISPLAY;

ALTER DOMAIN day_of_week DROP DISPLAY;

Drops a Domain object's Order expression ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> DROP ORDER;

ALTER DOMAIN day_of_week DROP ORDER;

Modifies a Domain object's Display expression ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> MODIFY DISPLAY <display_expression>;

ALTER DOMAIN day_of_week MODIFY DISPLAY LOWER(day_of_week);

Modifies a Domain object's Order expression ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> MODIFY ORDER <order_expression>;

ALTER DOMAIN year_of_birth MODIFY ORDER MOD(year_of_birth,100);

Annotate a Domain ALTER DOMAIN [IF EXISTS] [schema_name.] <domain_name> <annotations_clause>;

ALTER DOMAIN day_of_week ANNOTATIONS(DISPLAY 'Day of week');

Drop Domain (new 23ai)

Drops a Domain object DROP DOMAIN [IF EXISTS] domain_name [<FORCE | PRESERVE>];

DROP DOMAIN IF EXISTS uwphone_domain;

Drops an in-use Domain object DROP DOMAIN [IF EXISTS] domain_name FORCE;

DROP DOMAIN IF EXISTS uwphone_domain;

      

      ERROR at line 1:

      ORA-11502: The domain UWPHONE_DOMAIN to be dropped has dependent objects;

      

      DROP DOMAIN IF EXISTS uwphone_domain FORCE;

      

      Domain dropped.

      

      desc person

      

      Name       Null?  Type

      ---------- ------ -------------

      PERSON_ID         NUMBER(5)

      PHONE_NO          VARCHAR2(14)

Domains Demo

Built-in Domains col owner format a30 col name format a30 col cols format 9999 col builtin format 9999999 col data_display format a65 SELECT owner, name, cols, discriminantcols, builtin FROM dba_domains ORDER BY 1,2; col data_display format a65 SELECT owner, name, data_display FROM dba_domains WHERE data_dispaly IS NOT NULL ORDER BY 1,2; OWNER NAME DATA_DISPLAY ------ --------------------- ------------------------------------------------------- SYS CREDIT_CARD_NUMBER_D CASE WHEN length(credit_card_number_d)=15 THEN SUBSTR(credit_card_number_d,1,4) || ' ' || SUBSTR(credit_card_number_d,5,6) || ' ' || UBSTR(credit_card_number_d,11,5) WHEN length(credit_card_number_d)=16 THEN SUBSTR(credit_card_number_d,1,4) || ' ' || SUBSTR(credit_card_number_d,5,4) || ' ' || SUBSTR(credit_card_number_d,9,4) || ' ' || SUBSTR(credit_card_number_d,13,4) WHEN length(credit_card_number_d)=14 THEN SUBSTR(credit_card_number_d,1,4) || ' ' || SUBSTR(credit_card_number_d,5,6) || ' ' || SUBSTR(credit_card_number_d,11,4) ELSE credit_card_number_d END;

Metadata Recovery SELECT dbms_metadata.get_ddl('SQL_DOMAIN', 'CREDIT_CARD_NUMBER_D', 'SYS');

Container Architecture Query SELECT con_id, obj#, displaylength FROM containers(domain$) ORDER BY 1,2;