Managing Data Use Case Domains

24 Managing Data Use Case Domains

You can perform tasks such as creating domains, using domains, and dropping domains.

About Data Use Case Domains
A data use case domain, or simply domain, describes a business value.
Guidelines for Managing Domains
Following these guidelines can make the management of your domains easier.
Creating Domains
You need the CREATE DOMAIN privilege to create domains in your schema. To create domains in another schema you need the CREATE ANY DOMAIN privilege.
Using Domains
You can associate a domain with new columns when creating and altering tables.
Altering Domains
You can only add, change, or remove the display and order expressions, and annotations on a domain.
Dropping Domains
You can drop a domain with the DROP DOMAIN statement.
Evolving Domains
You can only change the annotations, display, and order expressions on a domain.
Domains Data Dictionary Views
You can query a set of data dictionary views for information about domains.

24.1 About Data Use Case Domains

A data use case domain, or simply domain, describes a business value.

A domain gives data consumers like applications, developers, or DBAs, additional context to help them process values correctly and consistently.

A domain has a data type and any of these optional properties:

STRICT
NOT NULL and CHECK constraints
Default collation, only for character types when extended data types are enabled
Default value
Annotations
Display and order expressions

You can associate a domain with table columns of the same data type.

Parent topic: Managing Data Use Case Domains

24.2 Guidelines for Managing Domains

Following these guidelines can make the management of your domains easier.

Specify the Type of Domain
You should specify the type of use case domain.
Consider Using Built-in Domains
Built-in domains provide an easy way to implement common domains.
Consider Defining Strict Domain Columns
Strict domain columns provide an exact type match.
Define Annotations on Domains
Annotations provide additional documentation for a domain.
Consider Multi-column Domains for Composite Values
When multiple columns need to be constrained together, create a multi-column domain.
Consider Defining Display and Order Expressions
You can create rules for displaying and ordering domain values.
Consider Using Enum Domains for Small Static Lists
You can restrict values of small static lists using an enumeration, or enum, domain.
Consider Using Flex Domains for Values With Different Rules Per Type
To create flexible rules based on type, use flex domains.
Consider Using JSON Schema Domains for JSON Data
To verify JSON data, use JSON schema domains.

Parent topic: Managing Data Use Case Domains

24.2.1 Specify the Type of Domain

You should specify the type of use case domain.

You can create the following types of use case domain:

Single column – a description of a single value
Multi-column – a description of a composite value
Enumeration (enum) domain – a list of name-value pairs
Flexible (flex) domain – a domain that chooses between subdomains
JSON schema domain – a description of a JSON value

Parent topic: Guidelines for Managing Domains

24.2.2 Consider Using Built-in Domains

Built-in domains provide an easy way to implement common domains.

The Oracle Database provides domains for common values like email addresses, credit card numbers, and social security numbers starting with release 23ai.

Parent topic: Guidelines for Managing Domains

24.2.3 Consider Defining Strict Domain Columns

Strict domain columns provide an exact type match.

By default, you can associate a domain with any column with the same data type. Strict domain columns can only be associated with table columns with an exact type match. This includes the scale, precision, and length of the data type.

Consider using the strict property for values with a standardized length or scale and precision. For example, ISO country and currency codes, two and three letters respectively.

Parent topic: Guidelines for Managing Domains

24.2.4 Define Annotations on Domains

Annotations provide additional documentation for a domain.

Add annotations to document a domain and add metadata to further describe values. For example, how to name the values in the UI and which DML operations are allowed on the values.

Parent topic: Guidelines for Managing Domains

24.2.5 Consider Multi-column Domains for Composite Values

When multiple columns need to be constrained together, create a multi-column domain.

For example, monetary amounts with value, currency, and exchange rate, phone numbers including country code and national number, and geographic points such as latitude and longitude.

Parent topic: Guidelines for Managing Domains

24.2.6 Consider Defining Display and Order Expressions

You can create rules for displaying and ordering domain values.

You can define custom rules for showing and sorting domain values in the DISPLAY and ORDER clauses respectively. Use these rules when querying domain values using the DOMAIN_DISPLAY and DOMAIN_ORDER functions.

Use these expressions to standardize formulas for domain values. For example, currency conversion logic for monetary values.

Parent topic: Guidelines for Managing Domains

24.2.7 Consider Using Enum Domains for Small Static Lists

You can restrict values of small static lists using an enumeration, or enum, domain.

For example, days of the week or blood types.

Parent topic: Guidelines for Managing Domains

24.2.8 Consider Using Flex Domains for Values With Different Rules Per Type

To create flexible rules based on type, use flex domains.

For example, measurements with different units such as temperatures, distance, or weights, or internationalization such as address or phone number formats which vary by country.

Parent topic: Guidelines for Managing Domains

24.2.9 Consider Using JSON Schema Domains for JSON Data

To verify JSON data, use JSON schema domains.

For example, to ensure JSON documents describing a department and its staff contains an array of named employees.

Parent topic: Guidelines for Managing Domains

24.3 Creating Domains

You need the CREATE DOMAIN privilege to create domains in your schema. To create domains in another schema you need the CREATE ANY DOMAIN privilege.

Creating Single-Column Domains
A single-column domains describes a value.
Creating Multi-Column Domains
Use multi-column domains to describe values with many parts.
Creating Enum Domains
Use enum domains to create named lists of values.
Creating Flex Domains
Flexible domains choose from a set of domains based on the value of a discriminator column.
Creating JSON Schema Domains
JSON schema domains validate the structure of JSON documents.

Parent topic: Managing Data Use Case Domains

24.3.1 Creating Single-Column Domains

A single-column domains describes a value.

Example: Creating a single-column domain

This creates a single column domain to describe insert timestamp values with these properties:

Its data type is TIMESTAMP WITH TIME ZONE.
It has a NOT NULL constraint.
Its default value is SYSTIMESTAMP.
It has annotations stating that it is system generated and SELECT is the only operation allowed on these values.

Note:

Annotations are metadata only, that is, it is up to you to respect these properties. Users with the appropriate privileges can still insert and update these values.

CREATE USECASE DOMAIN insert_timestamp_d AS 
  TIMESTAMP WITH LOCAL TIME ZONE 
  NOT NULL
  DEFAULT SYSTIMESTAMP
  ANNOTATIONS (
    system_generated,
    allowed_operations '["select"]' );

Parent topic: Creating Domains

24.3.2 Creating Multi-Column Domains

Use multi-column domains to describe values with many parts.

Example: Creating a multi-column domain

For example, the price, currency, and exchange rate for monetary values. A multi-column domain has a column for each component of the value. Like tables, you can define constraints and annotations at the column and domain level.

This example creates a multi-column domain to describe currency values with these properties:

It has columns for the amount, currency, and exchange rate from this currency to US Dollars.
The ISO_CURRENCY_CODE has the strict property. This means you can only associate it with columns defined as CHAR(3 CHAR), or the equivalent number of bytes per character in the database's character set.
The AMOUNT and USD_EXCHANGE_RATE columns are non-strict, so you can associate these with any column of type number, such as INTEGER, NUMBER(16,2), DECIMAL.
All its columns are mandatory.
There is a check constraint to ensure the currency codes are three uppercase letters.
The USD exchange rate must be greater than zero and defaults to 1.
There is a domain level constraint to ensure that the USD exchange rate for USD values is one.
It has display and order expressions to convert the amount from the source currency into USD.

CREATE USECASE DOMAIN currency_d AS (
  amount AS NUMBER NOT NULL,
  iso_currency_code AS CHAR(3 CHAR) STRICT NOT NULL
    CONSTRAINT from_curr_3_uppercase_letters_ck
    CHECK ( regexp_like ( iso_currency_code, '[[:upper:]]{3}' ) ),
  usd_exchange_rate AS NUMBER NOT NULL
    CONSTRAINT ex_rate_gt_zero_ck 
    CHECK ( usd_exchange_rate > 0 )
    DEFAULT 1 )
CONSTRAINT currency_usd_ex_rate_one_ck
CHECK ( CASE 
  WHEN iso_currency_code = 'USD' THEN 1 
  ELSE usd_exchange_rate END = usd_exchange_rate )
DISPLAY TO_CHAR ( amount * usd_exchange_rate, '$999,999,990.00' ) || '(' || iso_currency_code || ')'
ORDER TO_CHAR ( amount * usd_exchange_rate, '999,999,990.00' ) || iso_currency_code;

Parent topic: Creating Domains

24.3.3 Creating Enum Domains

Use enum domains to create named lists of values.

Each value can have many associated names. The names must be valid SQL identifiers.

Enum values can be any data type supported by domains. All values in an enum must use the same data type. If you omit the values they are integers starting at one. The value increases by one for each entry in the enum.

Example: Creating an enum domain

This example creates an enum for the days of the week.

There are two names associated with each value: the full name of the day and its two letter abbreviation.
Sunday has the value zero. The other values are unspecified, so they increase by one for each entry. Therefore Monday = 1, Tuesday = 2, and so on up to Saturday = 6.

CREATE DOMAIN days_of_week_d AS ENUM (
  Sunday    = Su = 0,
  Monday    = Mo,
  Tuesday   = Tu,
  Wednesday = We,
  Thursday  = Th,
  Friday    = Fr,
  Saturday  = Sa );

Example: Using enum name in a SQL statement

You can view all the name-value pairs for an enum by querying it like a table. For example:

SELECT * FROM days_of_week_d;

Example: Using enum names in a SQL statement

You can use enum names to access their values in SQL statements in place of literal values. For example:

SELECT * 
FROM   product_prices 
WHERE  discount_day IN ( days_of_week_d.saturday, days_of_week_d.sunday );

Example: Getting the name for a enum value

Enum domains have an implicit display expression which converts their values to the first name available. For example:

SELECT DOMAIN_DISPLAY ( CAST ( 1 AS days_of_week_d ) );

Parent topic: Creating Domains

24.3.4 Creating Flex Domains

Flexible domains choose from a set of domains based on the value of a discriminator column.

To create a flex domain, you must first create its subdomains. Then create the flex domain with a CASE or DECODE expression to determine which subdomain to use. The subdomains must be in the same schema as the flex domain or the flex domain owner must have EXECUTE privileges on the subdomains.

Each subdomain can map to a subset of the columns in the flex domain. For each flex column, all the associated subdomain columns must have a matching data type.

The flex domain has no other properties such as constraints or defaults. The database applies the properties to the table column based on the subdomain chosen.

Use the DOMAIN clause of CREATE TABLE and ALTER TABLE to assign a flex domain. The USING clause denotes the discriminator column.

Example: Creating a flex domain

This example creates domains for the temperature units Celcius, Fahrenheit, and Kelvin. Each of these domains has a check constraint to define the value of absolute zero on that temperature scale and a display expression to show the units. The flex domain temperature selects which of these domains to apply based on the value of its units discriminator column.

CREATE DOMAIN celcius_d AS NUMBER
  CONSTRAINT abs_zero_c_ck
    CHECK ( celcius_d >= -273.15 )
  DISPLAY celcius_d || ' °C';
   
CREATE DOMAIN fahrenheit_d AS NUMBER
  CONSTRAINT abs_zero_f_ck
    CHECK ( fahrenheit_d >= -459.67 )
  DISPLAY fahrenheit_d || ' °F';
   
CREATE DOMAIN kelvin_d AS NUMBER
  CONSTRAINT abs_zero_k_ck
    CHECK ( kelvin_d >= 0 )
  DISPLAY kelvin_d || ' K';

CREATE FLEXIBLE DOMAIN temperature_d ( temp )
CHOOSE DOMAIN USING ( units CHAR(1) ) 
FROM (
  CASE units
    WHEN 'C' THEN celcius_d ( temp )
    WHEN 'F' THEN fahrenheit_d ( temp )
    WHEN 'K' THEN kelvin_d ( temp )
  END );

Example: Associating a flex domain with table columns

The database applies the absolute zero check for the temperature using the subdomain corresponding to the units. For example, if you insert K for temperature_units, the database applies the constraint for the KELVIN_D domain. Therefore the values for temperature must be zero or greater.

The values for TEMPERATURE_UNITS are unconstrained, so you can insert characters other than C, F, and K. If you do insert other characters the values for temperature are also unconstrained.

Note that the column READING_DATETIME uses the INSERT_TIMESTAMP_D domain defined in Creating Single-Column Domains. This means it is a mandatory TIMESTAMP WITH TIME ZONE with the default SYSTIMESTAMP.

Next, you must associate the flex domain with table columns:

CREATE TABLE temperature_readings (
  reading_datetime   insert_timestamp_d, 
  temperature        NUMBER,
  temperature_units  CHAR(1 CHAR),
  DOMAIN temperature_d ( temperature ) 
    USING ( temperature_units ) );

Parent topic: Creating Domains

24.3.5 Creating JSON Schema Domains

JSON schema domains validate the structure of JSON documents.

Example: Creating a JSON schema domain

This example defines a JSON document to describe the staff in a department. It is an object that contains only these two attributes:

The string departmentName.
The array employees. The elements of this array can be any valid JSON value.

CREATE DOMAIN department_json_d AS JSON 
  VALIDATE USING '{
    "type": "object",
    "properties": {
      "departmentName": { "type": "string" },
      "employees": { "type": "array" }
    },
    "required" : [ "departmentName", "employees" ],
    "additionalProperties": false
  }';

Parent topic: Creating Domains

24.4 Using Domains

You can associate a domain with new columns when creating and altering tables.

Table DDL for Domains
Oracle DDL has been extended to allow full control of domains.
Domain Functions
Oracle Database provides five domain functions for working with domains.

Parent topic: Managing Data Use Case Domains

24.4.1 Table DDL for Domains

Oracle DDL has been extended to allow full control of domains.

The domain must be in your schema or you must have the EXECUTE ANY DOMAIN privilege.

Example: Associating a domain when creating a table

This statement associates the currency domain defined in Creating Multi-Column Domains with the PRICE, CURRENCY_CODE, and USD_EXCHANGE_RATE columns in the table.

Column names in the table and domain can be different and the database associates these by position. Therefore, PRICE is associated with AMOUNT, CURRENCY_CODE withISO_CURRENCY_CODE, and USD_EXCHANGE_RATE with USD_EXCHANGE_RATE.

CREATE TABLE product_prices (
  product_id        INTEGER,
  insert_datetime   TIMESTAMP WITH LOCAL TIME ZONE,
  price             NUMBER(10, 2),
  currency_code     CHAR(3 CHAR), 
  usd_exchange_rate NUMBER,
  DOMAIN currency_d ( price, currency_code, usd_exchange_rate ) );

Example: Associating a domain when adding a new column

This example adds the column DISCOUNT_DAY associated with the enum defined in section Creating Enum Domains. The values in this domain are numeric, so the column will have the data type NUMBER. The enum will also apply a check constraint to ensure you can only store its values in DISCOUNT_DAY, which are the integers zero to six.

ALTER TABLE product_prices ADD 
  discount_day DOMAIN days_of_week_d;

To associate a domain with an existing column you can use ALTER TABLE to link a domain with an existing column. Doing this copies all the domain properties, such as constraints and annotations, from the domain to the column.

Example: Associating a domain with an existing column

This associates the INSERT_TIMESTAMP_D domain defined in Creating Single-Column Domains with the INSERT_DATETIME column. For this to succeed, insert_datetime must:

Have the data type TIMESTAMP WITH LOCAL TIME ZONE.
Have no default or the same as the domain, which is SYSTIMESTAMP.
Be associated with no other domains.

ALTER TABLE product_prices 
  MODIFY ( insert_datetime ) 
  ADD DOMAIN insert_timestamp_d;

You can only associate a column with one domain. If you try to associate a second domain with a column, the statement will raise an error.

Example: Disassociating a domain

You can disassociate a domain from a column by using

ALTER
                    TABLE

to remove a domain from a column. This statement removes the domain from the DISCOUNT_DAY column. This will also remove the constraint from the column.

ALTER TABLE product_prices
  MODIFY ( discount_day ) 
  DROP DOMAIN;

Parent topic: Using Domains

24.4.2 Domain Functions

Oracle Database provides five domain functions for working with domains.

The domain functions are:

DOMAIN_CHECK – test whether a value can be converted to match the data type of the domain and satisfies the constraints for a domain.
DOMAIN_CHECK_TYPE – test whether a value can be converted to match the data type of the domain.
DOMAIN_NAME - returns the name of the domain associated with the values.
DOMAIN_DISPLAY – applies the display expression to a domain value.
DOMAIN_ORDER – applies the order expression to a domain value.

Example: Verifying whether values conform to a domain

This query checks the value 42 against the enum domain created in Creating Enum Domains.

DOMAIN_CHECK returns FALSE because 42 is outside the list of permitted values for the domain DAYS_OF_WEEK_D.
DOMAIN_CHECK_TYPE only checks the data types are compatible. Both the value 42 and domain DAYS_OF_WEEK_D use numeric types, so this returns TRUE.
```
SELECT DOMAIN_CHECK ( days_of_week_d, 42 ),
       DOMAIN_CHECK_TYPE ( days_of_week_d, '42' );
```

Example: Getting the name of a domain associated with a column

This query returns the schema qualified name of the domain associated with the table column INSERT_DATETIME. If it has no associated domain, the query returns NULL.

SELECT DOMAIN_NAME ( insert_datetime )
FROM   product_prices;

Example: Using domain display and order expressions in a query

Using the domain defined in Creating Multi-Column Domains, this sorts and shows the currency values converted into US dollars.

SELECT price, currency_code,
       DOMAIN_DISPLAY ( price, currency_code, usd_exchange_rate ) price_in_usd
FROM   product_prices 
ORDER  BY DOMAIN_ORDER ( price, currency_code, usd_exchange_rate );

Parent topic: Using Domains

24.5 Altering Domains

You can only add, change, or remove the display and order expressions, and annotations on a domain.

The domain must be in your schema or you must have the ALTER ANY DOMAIN privilege to change a domain in another schema.

Example: Changing the DISPLAY expression on a domain

ALTER USECASE DOMAIN currency_d
  MODIFY DISPLAY TO_CHAR ( floor ( amount * usd_exchange_rate ), '$999,999,990.00' ) 
    || ' (' || iso_currency_code || ')';

Parent topic: Managing Data Use Case Domains

24.6 Dropping Domains

You can drop a domain with the DROP DOMAIN statement.

The domain must be in your schema or you must have the DROP ANY DOMAIN privilege.

Example: Dropping a domain

To drop a domain, use the DROP USECASE DOMAIN statement. For example:

DROP USECASE DOMAIN currency_d;

If the domain is associated with table columns, this will raise an error. Use the FORCE option to disassociate the domain from a column and remove it. For example:

DROP USECASE DOMAIN currency_d FORCE;

This also removes all domain properties except default collations from the columns. To keep these properties use the FORCE PRESERVE option. For example:

DROP USECASE DOMAIN currency_d FORCE PRESERVE;

Parent topic: Managing Data Use Case Domains

24.7 Evolving Domains

You can only change the annotations, display, and order expressions on a domain.

If you want to change other properties such as constraints, then you need to create a new domain.

The process to evolve a domain is:

Create a new domain with the new properties.
Drop the existing domain with the FORCE option. Optionally use PRESERVE to keep the domain properties on any of the columns that were associated with the domain.
Associate the new domain with the columns.
Remove any unwanted properties from the original domain from the table columns.

Example: Evolving a domain

For example, to change the default for insert timestamps, this creates a new domain, INSERT_TIMESTAMP_ON_NULL_D. It then drops the original domain with the FORCE option. This disassociates the domain from the table columns and removes the domain default. You can't preserve the default in this case, because the domain and column defaults must match.

It then associates the new domain with the table columns.

CREATE USECASE DOMAIN insert_timestamp_on_null_d AS 
  TIMESTAMP WITH LOCAL TIME ZONE 
  DEFAULT ON NULL FOR INSERT ONLY SYSTIMESTAMP
  ANNOTATIONS (
    system_generated,
    allowed_operations '["select"]' );

DROP DOMAIN insert_timestamp_d FORCE;

ALTER TABLE product_prices
  MODIFY ( insert_datetime ) 
  ADD DOMAIN insert_timestamp_on_null_d;

Parent topic: Managing Data Use Case Domains

24.8 Domains Data Dictionary Views

You can query a set of data dictionary views for information about domains.

The following views show information about domains:

View	Description
`DBA_DOMAINS` `ALL_DOMAINS` `USER_DOMAINS`	List of domains in the database and their properties.
`DBA_DOMAIN_COLS` `ALL_DOMAIN_COLS` `USER_DOMAIN_COLS`	List of columns associated with each domain.
`DBA_DOMAIN_CONSTRAINTS` `ALL_DOMAIN_CONSTRAINTS` `USER_DOMAIN_CONSTRAINTS`	List of constraints associated with each domain.
`DBA_ANNOTATIONS_USAGE` `ALL_ANNOTATIONS_USAGE` `USER_ANNOTATIONS_USAGE`	Use these views to see the annotations for a domain where `object_type = 'DOMAIN'`.

Parent topic: Managing Data Use Case Domains