Oracle DOMAIN_ Functions

A data use case domain, new in Oracle AI Database 26ai, defines a kind of value once, such as an e-mail address: its data type, its check constraint, how to display it, and how to sort it. Columns then use the domain instead of repeating those rules. Five functions put the definition to work in queries: DOMAIN_NAME, DOMAIN_DISPLAY, DOMAIN_ORDER, DOMAIN_CHECK, and DOMAIN_CHECK_TYPE.

Code for This Guide

The main example is in the examples/environment-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It runs as NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

domain_name(column)
domain_display(expr)
domain_order(expr)
domain_check(domain_name, expr [, ...])
domain_check_type(domain_name, expr [, ...])

A Domain for E-mail Addresses

The example creates a domain email_d with a regular expression check, a DISPLAY expression that shows addresses in lowercase, and an ORDER expression that sorts them without regard to case. A table uses it for a column, and the queries apply the functions.

Example:

create domain email_d as varchar2(60)
  constraint email_d_ck
    check (regexp_like(email_d, '^[^@ ]+@[^@ ]+\.[a-z]+$', 'i'))
  display lower(email_d)
  order upper(email_d);

create table staff_contacts (id number, email email_d);
insert into staff_contacts values (1, 'Layla.Haddad@Nimbus.example'),
                                  (2, 'OMAR@nimbus.example');

select id, email, domain_name(email) as domain, domain_display(email) as display
from   staff_contacts
order  by domain_order(email);

select domain_check(email_d, 'ops@nimbus.example') as good,
       domain_check(email_d, 'bad address')        as bad,
       domain_check_type(email_d, 42)              as number_type
from   dual;

Output:

Domain EMAIL_D created.

Table STAFF_CONTACTS created.

2 rows inserted.

   ID EMAIL                          DOMAIN            DISPLAY
_____ ______________________________ _________________ ______________________________
    1 Layla.Haddad@Nimbus.example    NIMBUS.EMAIL_D    layla.haddad@nimbus.example
    2 OMAR@nimbus.example            NIMBUS.EMAIL_D    omar@nimbus.example

GOOD    BAD      NUMBER_TYPE
_______ ________ ______________
true    false    true

DOMAIN_NAME reports NIMBUS.EMAIL_D for the column, DOMAIN_DISPLAY lowercases the mixed-case addresses, and ORDER BY DOMAIN_ORDER sorts by the uppercase form. DOMAIN_CHECK accepts a valid address and rejects 'bad address'. DOMAIN_CHECK_TYPE returns true for 42, because a number converts to VARCHAR2, even though 42 would fail the check constraint.

To run it again, drop the table and the domain first: drop table if exists staff_contacts purge, then drop domain if exists email_d.

Things to Know

• DOMAIN_CHECK lets you validate input against the same rule the column enforces, before inserting, without duplicating the regular expression in application code.
• DOMAIN_DISPLAY and DOMAIN_ORDER apply only to expressions whose domain is known, such as a column declared with the domain.
• Changing the domain's DISPLAY or ORDER expression changes the behavior of every column that uses it.

Related Guides

• Oracle REGEXP_LIKE Function

Conclusion

The DOMAIN_ functions use the definitions of data use case domains in queries: DOMAIN_NAME identifies a column's domain, DOMAIN_DISPLAY and DOMAIN_ORDER format and sort values by the domain's rules, and DOMAIN_CHECK and DOMAIN_CHECK_TYPE test values against the domain's constraints and type.