The same kind of value appears in many columns: phone numbers, e-mail addresses, currency amounts. Each column usually repeats the type, the check constraint, and the formatting rules, and they drift apart over time. A data use case domain, new in Oracle AI Database 26ai, defines a kind of value once, and every column declared with it takes the type, default, constraint, display, order, and annotations from the domain.
Code for This Guide
The main examples are in the examples/schema-objects folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create domain [if not exists] domain as type [default expr] [constraint name check (condition)] [display expr] [order expr] [annotations (...)] create table t (column domain_name, ...) create table t (column domain domain_name, ...)
Inside the domain, its own name stands for the value being checked or displayed.
A Domain for Phone Numbers
The domain checks international phone numbers with a regular expression, displays them with hyphens, and carries a description. Two columns of a table use it.
Example:
create domain phone_d as varchar2(20)
constraint phone_d_ck check (regexp_like(phone_d, '^\+[0-9 ]{7,18}$'))
display regexp_replace(phone_d, ' ', '-')
annotations (description 'International phone number with country code');
create table crew_contacts (employee_id number, mobile phone_d, office domain phone_d);
insert into crew_contacts values (112, '+971 50 123 4567', '+971 4 555 0100');
select employee_id, mobile, domain_display(mobile) as shown from crew_contacts;
insert into crew_contacts (employee_id, mobile) values (113, '050 123 4567');Output:
Domain PHONE_D created.
Table CREW_CONTACTS created.
1 row inserted.
EMPLOYEE_ID MOBILE SHOWN
______________ ___________________ ___________________
112 +971 50 123 4567 +971-50-123-4567
Error starting at line : 11
In command -
insert into crew_contacts (employee_id, mobile) values (113, '050 123 4567')
Error report -
ORA-11534: check constraint (NIMBUS.SYS_C0016279) involving column MOBILE due to
domain constraint NIMBUS.PHONE_D_CK of domain NIMBUS.PHONE_D violatedDOMAIN_DISPLAY applies the domain's display expression. A number without the country code fails with ORA-11534, a message that names both the column and the domain constraint.
Find Domains and Their Columns
Example:
select name, type, cols, builtin from user_domains order by name; select table_name, column_name, domain_name from user_tab_columns where domain_name is not null order by table_name, column_name;
Output:
NAME TYPE COLS BUILTIN __________ __________ _______ __________ PHONE_D REGULAR 1 false TABLE_NAME COLUMN_NAME DOMAIN_NAME ________________ ______________ ______________ CREW_CONTACTS MOBILE PHONE_D CREW_CONTACTS OFFICE PHONE_D
USER_DOMAINS lists the domains, and USER_TAB_COLUMNS.DOMAIN_NAME shows which columns use each. The example drops the table and domain afterward.
A Domain in Use Cannot Be Dropped Plainly
Example:
drop domain phone2_d;
Output:
Error starting at line : 1 In command - drop domain phone2_d Error report - ORA-11502: The domain PHONE2_D to be dropped has dependent objects.
Drop the dependent columns first, or use DROP DOMAIN ... FORCE, which keeps the columns but removes their link to the domain.
Things to Know
- ALTER DOMAIN changes the display, order, and annotations of every column using the domain at once.
- A domain can cover several columns, for example an address made of street, city, and postal code.
- Oracle provides built-in domains for common types such as e-mail addresses.
Related Guides
Conclusion
Data use case domains define a kind of value once, with its type, constraint, display, order, and annotations, for every column that uses it. Declare columns with the domain, read it from USER_DOMAINS, and change it in one place.
