1. Database Development Guide
  2. SQL for Application Developers
  3. Registering Application Data Usage with the Database

10 Registering Application Data Usage with the Database

This chapter details how you can use centralized database-centric entities called application data usage domains and annotations to register information about the intended application data usage.

Oracle Database 23c introduces a centralized, database-centric approach to handling intended data usage information using application usage domains and annotations. You can add usage domains and annotations centrally in the database to register data usage intent, which is then accessible to various applications and tools.

See Also:

Sections:

10.1 Application Usage Domains

This section explains how you can use application usage domains (hereinafter "usage domains") in your applications.

Topics:

10.1.1 Overview of Usage Domains

Usage domains are lightweight usage specifiers with the optional database-side enforcement that applications can use to centrally document intended data usage. As a high-level dictionary object, a usage domain includes built-in usage properties associated with table columns, such as the default value, check constraints, collations, display and order formats, and annotations. Using centralized domain information, applications can standardize operations without depending on application-level metadata. For example, you can use usage domains to mask credit card numbers or to format phone numbers and currency values.

As a database object, a usage domain belongs to a schema and provides a common column definition without modifying the underlying column data type. A usage domain encapsulates some common characteristics of a table column into a reusable object that can be reused on other table columns without having to repeat these characteristics.

For example, a schema may have many tables with columns that hold email addresses, such as billing emails, invoice emails, and customer contact emails. Email addresses have a special format because they require the "@" sign. You can define a usage domain for the email address using a check constraint such as regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'), and associate the domain with other email columns to have your application display the associated column's email addresses with the standard "@" sign prior to the domain name. Likewise, you may want to standardize display formats for vehicle license plates, which may require hyphens to separate the number from the other information. Using a usage domain, you can show the license information as "ABC-123" even when the license column has a varchar2(6) data type.

Benefits of using a usage domain include: improved data quality because it enables you to handle values consistently across applications; reduced coding because you can reuse a column's properties and constraints across your application; and consistency and clarity in operations because applications can inquire about these usage domains to understand what data they are operating against.

See Also:

Oracle Database Concepts for more information about Application Usage Domains.

10.1.2 Usage Domain Types and When to Use Them

There are three different types of domains: single-column, multi-column, and flexible domains.

Single-column Usage Domains

A single-column usage domain is created for one column, and is used when you want to make a column usage definition consistent throughout your application. For example, you can create a single-column domain for email addresses, postal codes, or vehicle numbers.

Unless prefixed with multi-column or flexible, a "usage domain" or "domain" means a single-column usage domain for the purposes of this document.

Multi-column Usage Domains

A multi-column usage domain creates column usage definitions for multiple columns under a single domain. A multi-column usage domain is ideal when you have logical entities spanning across two or more table columns. For example, you can use a multi-column usage domain to represent an address.

Flexible Usage Domains

A flexible usage domain is a domain that dynamically selects from a set of single-column or multi-column usage domains based on a discriminant column's values. A flexible domain assigns a specific domain (also known as a constituent domain) to a set of value columns based on a mapping expression composed from one or more discriminant columns.

In addition to these usage domain types, you can also use built-in usage domains directly on table columns. Some examples are email_d, ssn_d, and credit_card_number_d.

See Also:

10.1.3 Privileges Required for Usage Domains

To work with usage domains, you require the following privileges:

Note:

The Database Administrator (DBA) role includes all the following privileges.

DDL Privilege Action

CREATE DOMAIN

You can create a domain in your own schema.

The RESOURCE and DB_DEVELOPER_ROLE roles include the CREATE DOMAIN privilege.

CREATE ANY DOMAIN

You can create a domain in any schema.

ALTER ANY DOMAIN

You can alter a domain in any schema.

DROP ANY DOMAIN

You can drop a domain in any schema.

EXECUTE ANY DOMAIN

You can reference or use a domain in any schema.

To explicitly grant execute privileges to a user on a domain in any schema, use the following code:

GRANT EXECUTE ON <schemaName.domainName> TO <user>;

10.1.4 Using a Single-column Usage Domain

This section explains how you can create, associate, alter, disassociate, and drop a single-column usage domain (hereinafter "usage domain" or "domain").

Topics

10.1.4.1 Creating a Usage Domain

You can define a usage domain that encapsulates a set of optional properties and constraints to represent common values, such as email addresses and credit card numbers.

The following are some examples of creating usage domains, where a domain is based on the properties of a table column, such as default value, constraints, annotations, or display and order expressions.

Example 10-1 Creating a Usage Domain for Hourly Wages

A Human Resource Application (HRA) creates many tables for different companies. Most companies have a column that stores hourly wages. The HRA can create a usage domain for the hourly wages columns as follows:

CREATE DOMAIN HourlyWages AS NUMBER
  DEFAULT ON NULL 15
  CONSTRAINT MinimalWage CHECK (HourlyWages > = 7 AND HourlyWages <=1000) ENABLE
  DISPLAY TO_CHAR(HourlyWages, '$999.99')  ORDER ( -1*HourlyWages )
  ANNOTATIONS (properties '{"Purpose": "Wages", "Applicability": "USA", "Industry": {"Sales", "Manufacturing"} }');

Example 10-2 Creating a Usage Domain for Surrogate Keys

If you want to annotate surrogate key columns in your database application and maintain standard meta-data for such columns, create a domain similar to the following:

CREATE DOMAIN surrogate_id AS INTEGER
  STRICT
  NOT NULL
  ANNOTATIONS ( primary_key, mandatory, operations '["insert", "delete"]' );

Example 10-3 Creating a Usage Domain for Birth Dates

The following is a usage domain that ensures that all columns with birth dates are "date only" and displays the age in years.

CREATE DOMAIN birth_date AS DATE
  CONSTRAINT birth_date_only_c check ( birth_date = trunc ( birth_date ) )
  DISPLAY FLOOR ( months_between ( sysdate, birth_date ) / 12 ) || ' years'
  ANNOTATIONS ( sensitive 'PII Data', operations '["insert", "update"]' );

Example 10-4 Creating a Usage Domain for Default Date and Time Values

You can create a usage domain for date and time values to ensure that the inserts are in a standard format.

CREATE DOMAIN insert_timestamp AS
  TIMESTAMP WITH LOCAL TIME ZONE
  DEFAULT ON NULL systimestamp;

Example 10-5 Creating a Usage Domain for Positive Heights

The following usage domain ensures that people have positive heights and the heights are sorted in a descending order.

CREATE DOMAIN height AS NUMBER
  CONSTRAINT positive_height_c CHECK ( value > 0 )
  ORDER value * -1
  ANNOTATIONS ( operations '["insert", "update"]' );

Example 10-6 Creating a Usage Domain for Positive Weights

The following usage domain ensures that people have positive weights.

CREATE DOMAIN weight AS NUMBER
  CONSTRAINT positive_weight_c CHECK ( value > 0 )
  ANNOTATIONS ( operations '["insert", "update"]' );

Example 10-7 Defining a Domain with Multiple Check Constraints

You can define a usage domain for email addresses, similar to the one in the following example, which also has multiple CHECK constraints. 

CREATE SEQUENCE IF NOT EXISTS email_seq;

CREATE DOMAIN email AS VARCHAR2(100) 
  DEFAULT ON NULL email_seq.NEXTVAL || '@domain.com' 
  CONSTRAINT email_c CHECK (REGEXP_LIKE (email, '^(\S+)\@(\S+)\.(\S+)$')) 
  CONSTRAINT email_max_len_c CHECK (LENGTH(email) <=100) DEFERRABLE INITIALLY DEFERRED
  DISPLAY '---' || SUBSTR(email, INSTR(email, '@') + 1);

Any column of VARCHAR2(L [BYTE|CHAR]) data type that satisfies both constraints can be associated with the domain. The INITIALLY DEFERRED clause delays validation of the constraint email_max_len_c values until commit time.

Example 10-8 JSON Schema Validation

A domain can also be used for reusable JSON schema validation, as in the following example.

CREATE DOMAIN department_json_doc AS JSON 
  CONSTRAINT CHECK ( 
    department_json_doc IS JSON VALIDATE USING '{
      "type": "object",
      "properties": {
        "departmentName": { "type": "string" },
        "employees": { "type": "array" }
      },
      "required" : [ "departmentName", "employees" ],
      "additionalProperties": false
    }' );

See Also:

10.1.4.2 Associating Usage Domains with Columns at Table Creation

After defining a usage domain, you can associate the domain with new table columns when creating a table or associate it with existing columns in an existing table. Associating a usage domain with a column explicitly applies the domain's optional properties and constraints to the column.

You can use the CREATE TABLE DDL to associate a usage domain with a newly created column. The following examples show how you can associate a usage domain with columns in a new table. These examples use the usage domains created in the earlier examples.

Example 10-9 Associating HourlyWages Usage Domain at Table Creation

Using the HourlyWages domain, the HRA can create multiple tables where wage columns have the same domain characteristics. 

CREATE TABLE employee (
  name VARCHAR2(100),
  id NUMBER,
  wage NUMBER DOMAIN HourlyWages);
CREATE TABLE wage (
  name VARCHAR2(100),
  id NUMBER,
  wage NUMBER DOMAIN HourlyWages,
  gross_pay NUMBER,
  deductions NUMBER,
  net_pay NUMBER);

Example 10-10 Associating the surrogate_id Usage Domain at Table Creation

When you associate a strict usage domain such as surrogate_id with columns, ensure that the associated columns have the same data type as the domain. A strict domain also requires that the column length, scale, and precision match with the domain.

The following code fails with ORA-11517: the column data type does not match the domain column because you are trying to link a NUMBER data type column with a INTEGER/NUMBER(*,0) data type domain. 

CREATE TABLE orders (
  order_id NUMBER DOMAIN surrogate_id,
  customer_id NUMBER,
  order_datetime TIMESTAMP WITH LOCAL TIME ZONE
    DEFAULT SYSTIMESTAMP);

To ensure that the association works, you can use the NUMBER(*,0) column data type to associate with the surrogate_id usage domain (INTEGER == NUMBER(*,0)). 

CREATE TABLE orders (
  order_id NUMBER(*,0) DOMAIN surrogate_id,
  customer_id NUMBER,
  order_datetime TIMESTAMP WITH LOCAL TIME ZONE
    DEFAULT SYSTIMESTAMP);

Example 10-11 Associating the surrogate_id, birth_date, height, and weight Usage Domains at Table Creation

The DOMAIN keyword is optional. You can see in the following example that the birth_date domain is associated with date_of_birth column without the DOMAIN keyword. The example also shows that you can define a more precise data type for the columns with regards to the precision and scale than what is in the domain. The height_in_cm and weight_in_kg columns have their associated domain data type as NUMBER whereas the column data type has precision and scale values, such as NUMBER(4,1). 

CREATE TABLE people (
  person_id     DOMAIN surrogate_id
    GENERATED BY DEFAULT AS IDENTITY
    PRIMARY KEY,
  full_name     VARCHAR2(256),
  date_of_birth birth_date,
  height_in_cm  NUMBER(4, 1) DOMAIN height,
  weight_in_kg  NUMBER(6, 3) DOMAIN weight);

Example 10-12 Associating the department_json_doc Usage Domain at Table Creation 

CREATE TABLE departments (
  department_id  INTEGER PRIMARY KEY,
  department_doc JSON DOMAIN department_json_doc);

Guidelines

  • When associating a domain with a column, you can specify the domain name in addition to the column's data type, in which case the column's data type is used, provided that the domain data type is compatible with the column's data type.

  • When associating a domain with a column, you can specify a domain name instead of the column's data type, in which case the domain data type is used for the column, wherein the DOMAIN keyword is optional.

  • If a domain is defined as STRICT, the domain's data type, scale, and precision must match the column's data type, scale, and precision.

  • If a domain is not defined as STRICT, you can associate a domain of any length with a column of any length. For instance, you can associate a domain of VARCHAR2(10) with any VARCHAR2 column.

See Also:

10.1.4.2.1 Using DML on Columns Associated with Usage Domains

The following are some examples of DML statements that you can use on the newly created table columns with associated usage domains.

Example 10-13 Using DML Statements on the people table

The following INSERT commands insert data into the people table columns, while verifying that the check constraint specified in the associated domain is not violated. 

INSERT INTO people
  VALUES ( 1, 'Sally Squirell', date'1981-01-01', 180.1, 61 );

INSERT INTO people
  VALUES ( 2, 'Brian Badger', date'2016-12-31', 120.4, 27.181 );

The following INSERT command fails because height is specified as a negative number, and hence the associated check constraint is violated. 

INSERT INTO people
  VALUES ( 3, 'Fergal Fox', date'2023-04-12', -99, 1 );

The output is:

ORA-11534: check constraint (HR.SYS_C009232) due to domain constraint HR.POSITIVE_HEIGHT_C of domain HR.HEIGHT violated

You can use the associated domains to display data with the heights sorted in a descending order and also view the corresponding age and weight.

SELECT full_name, DOMAIN_DISPLAY ( date_of_birth ) age,
       height_in_cm, weight_in_kg
  FROM people
  ORDER BY DOMAIN_ORDER ( height_in_cm );

See Also:

SQL Functions for Usage Domains for details about domain functions, such as DOMAIN_DISPLAY and DOMAIN_ORDER.

The output is:


FULL_NAME            AGE        HEIGHT_IN_CM WEIGHT_IN_KG
-------------------- ---------- ------------ ------------
Sally Squirell       42 years          180.1           61
Brian Badger         6 years           120.4       27.181

You can describe the people table to see the data type definition and the referenced domain information. 

DESC people;

The output is:


 Name                   Null?    Type
 ---------------------- -------- ----------------------------------------
 PERSON_ID              NOT NULL NUMBER(38) HR.SURROGATE_ID
 FULL_NAME                       VARCHAR2(256)
 DATE_OF_BIRTH                   DATE HR.BIRTH_DATE
 HEIGHT_IN_CM                    NUMBER(4,1) HR.HEIGHT
 WEIGHT_IN_KG                    NUMBER(6,3) HR.WEIGHT

The following SELECT command enables you to view the column annotations that are inherited from the associated domains. 


SELECT column_name, annotation_name, annotation_value 
  FROM user_annotations_usage
  WHERE object_name = 'PEOPLE';

The output is:


COLUMN_NAME          ANNOTATION_NAME      ANNOTATION_VALUE    
-------------------- -------------------- --------------------
PERSON_ID            PRIMARY_KEY          <null>              
PERSON_ID            MANDATORY            <null>              
PERSON_ID            OPERATIONS           ["insert", "delete"]
DATE_OF_BIRTH        OPERATIONS           ["insert", "update"]
DATE_OF_BIRTH        SENSITIVE            PII Data            
HEIGHT_IN_CM         OPERATIONS           ["insert", "update"]
WEIGHT_IN_KG         OPERATIONS           ["insert", "update"]

Example 10-14 Using DML Statements on the departments table with JSON data

The following INSERT command on the departments table succeeds because it includes all the JSON attributes in the department_json_doc domain. 

INSERT INTO departments 
  VALUES ( 1, '{
    "departmentName" : "Accounting", 
    "employees" : [
      {"empName":"William"},
      {"empName":"Shelley"}
    ]
  }');

The following INSERT command fails with ORA-40875: JSON schema validation error - missing employees attribute because the employees attribute is missing. 

INSERT INTO departments 
  VALUES ( 2, '{
    "departmentName" : "Finance"
  }');

The following INSERT command fails with ORA-40875: JSON schema validation error - extra manager attribute because the manager attribute is not found in the associated department_json_doc usage domain. 

INSERT INTO departments
  VALUES ( 3, '{
    "departmentName" : "Executive", 
    "employees" : [
      {"empName":"Lex"},
      {"empName":"Neena"}
    ],
    "manager" : {"empName":"Lex"}
  }');
10.1.4.3 Associating Usage Domains with Existing or New Columns

You can use the ALTER TABLE DDL with the MODIFY or ADD clause to associate a usage domain with an existing or a newly added column.

Example 10-15 Associating a Usage Domain with a Newly Created Column

For a newly created customers table: 

CREATE TABLE customers (
  cust_id NUMBER,
  cust_email VARCHAR2(100));

You can use ADD to add a new column: cust_new_email and associate it with the email domain. 

ALTER TABLE customers
  ADD (cust_new_email VARCHAR2(100) DOMAIN email);

Example 10-16 Associating the email Usage Domain with an Existing Column

You can use MODIFY to modify an existing column: cust_email and associate it with the email domain. 

ALTER TABLE customers
  MODIFY (cust_email  VARCHAR2(100) DOMAIN email);

You can also add a usage domain to a column using the ALTER TABLE ... MODIFY statement. In the following example, the orders table column, namely order_datetime and the insert_timestamp domain have different defaults. The insert_timestamp domain has the DEFAULT with ON NULL clause, which is missing in the order_datetime column. Therefore, when you try to associate the domain with the column, you get an error ORA-11501: The column default does not match the domain default of the column. 

ALTER TABLE orders
  MODIFY order_datetime DOMAIN insert_timestamp;

To overcome the default mismatch, specify a column default clause that matches the domain default.

ALTER TABLE orders
  MODIFY order_datetime DOMAIN insert_timestamp
    DEFAULT ON NULL systimestamp;

Example 10-17 Querying to View Associated Domains

SELECT constraint_name, search_condition_vc, domain_constraint_name
  FROM  user_constraints
  JOIN  user_cons_columns
  USING ( constraint_name, table_name )
  WHERE  table_name = 'PEOPLE'
    AND  constraint_type = 'C'
    AND  column_name = 'WEIGHT_IN_KG';

The output is:


CONSTRAINT_NAME      SEARCH_CONDITION_VC            DOMAIN_CONSTRAINT_NAME   
-------------------- ------------------------------ --------------------------
SYS_C008493          "WEIGHT_IN_KG">0               POSITIVE_WEIGHT_C

Guidelines

  • The DOMAIN keyword is optional for the ALTER TABLE .. ADD statement if a domain only is specified for the newly added column.

  • The DOMAIN keyword is mandatory for the ALTER TABLE .. MODIFY statement.

  • The column data type should be compatible with the domain data type.

  • If a domain has default expression or collation, it should match the associated column's default expression and collation.

  • If the associated column already has a domain associated with it, an error is returned.

10.1.4.4 Altering a Usage Domain

You can alter a usage domain for its display expression, order expression, and annotation. The following ALTER DOMAIN DDL statements are supported.

Example 10-18 Removing the Order Expression from a Domain

The height usage domain definition has the order expression as VALUE, so to remove the order expression, you must drop the order expression.

ALTER DOMAIN height DROP ORDER;

Example 10-19 Adding a Display Expression to a Usage Domain

To add a display expression to the height usage domain, use the following ALTER command. 

ALTER DOMAIN height 
  ADD DISPLAY round ( value ) || ' cm';

Example 10-20 Changing the Display Expression of the birth_date Domain

To change the display expression of the birth_date domain to years and months, use the following ALTER command. 

ALTER DOMAIN birth_date 
  MODIFY DISPLAY 
    FLOOR ( months_between ( sysdate, birth_date ) / 12 ) || ' years ' ||
    MOD ( FLOOR ( months_between ( sysdate, birth_date ) ), 12 ) || ' months';

Example 10-21 Querying the people table for the Altered birth_date, height, and weight Domains 

COLUMN age FORMAT A20

SELECT full_name, DOMAIN_DISPLAY ( date_of_birth ) age,
       DOMAIN_DISPLAY ( height_in_cm ) height_in_cm, weight_in_kg
  FROM  people
  ORDER  BY DOMAIN_ORDER ( height_in_cm );

See Also:

SQL Functions for Usage Domains for details about domain functions, such as DOMAIN_DISPLAY and DOMAIN_ORDER.

The output is:

FULL_NAME            AGE                  HEIGHT_IN_CM  WEIGHT_IN_KG
-------------------- -------------------- ------------- ------------
Sally Squirell       42 years 5 months    180 cm                  61
Brian Badger         6 years 5 months     120 cm              27.181

Example 10-22 Changing the Annotations on a Usage Domain

The following code adds the annotations for the height usage domain. 

ALTER DOMAIN height
  ANNOTATIONS ( 
    operations '["insert", "update", "sort"]', 
    sensitive 'Private data');

Example 10-23 Querying the Dictionary Views for the Annotation Changes


COLUMN annotation_value FORMAT A40

SELECT column_name, annotation_name, annotation_value 
  FROM user_annotations_usage
  WHERE object_name = 'PEOPLE';

The output is:


COLUMN_NAME          ANNOTATION_NAME      ANNOTATION_VALUE                        
-------------------- -------------------- ----------------------------------------
PERSON_ID            PRIMARY_KEY          <null>                                  
PERSON_ID            MANDATORY            <null>                                  
PERSON_ID            OPERATIONS           ["insert", "delete"]                    
DATE_OF_BIRTH        SENSITIVE            PII Data                                
DATE_OF_BIRTH        OPERATIONS           ["insert", "update"]                    
HEIGHT_IN_CM         OPERATIONS           ["insert", "update"]                    
HEIGHT_IN_CM         OPERATIONS           ["insert", "update", "sort"]            
HEIGHT_IN_CM         SENSITIVE            Private data                            
WEIGHT_IN_KG         OPERATIONS           ["insert", "update"]

Guidelines

  • You can alter the display expression of a domain only if the domain is not constituent in a flexible domain.

  • You can alter the order expression of a domain only if the domain is not constituent in a flexible domain.

  • You can alter only domain-level annotations.

See Also:

  • Oracle Database SQL Language Reference for the syntactic and semantic information about altering a usage domain: ALTER DOMAIN.

  • Viewing Domain Information for the usage domain dictionary views

10.1.4.5 Disassociating a Usage Domain from a Column

You can use the ALTER TABLE DDL with the DROP clause to disassociate a usage domain from a column.

Example 10-24 Disassociating a Usage Domain from a Column

To drop the associated domain from the cust_email column of the customers table: 

ALTER TABLE customers
  MODIFY ( cust_email ) DROP DOMAIN;

To drop the domain but keep the domain's constraint:

ALTER TABLE customers
  MODIFY ( cust_email ) DROP DOMAIN PRESERVE CONSTRAINTS;

Example 10-25 Disassociating a Usage Domain from a Column

The following code removes the domain association of the height domain from the height_in_cm column in the people table, while preserving the constraint. 

ALTER TABLE people
  MODIFY ( height_in_cm ) DROP DOMAIN PRESERVE CONSTRAINTS;

Example 10-26 Querying the Columns with Disassociated Usage Domain

The SELECT query on the user_constraints dictionary table reveals the changes. 

SELECT constraint_name, search_condition_vc, domain_constraint_name
  FROM user_constraints
  JOIN user_cons_columns
  USING ( constraint_name, table_name )
  WHERE table_name = 'PEOPLE'
    AND constraint_type = 'C';

The output is:


CONSTRAINT_NAME      SEARCH_CONDITION_VC                      DOMAIN_CONSTRAINT_NAME       
-------------------- ---------------------------------------- ------------------------------
SYS_C009491          "DATE_OF_BIRTH"=TRUNC("DATE_OF_BIRTH")   BIRTH_DATE_ONLY_C            
SYS_C009494          "WEIGHT_IN_KG">0                         POSITIVE_WEIGHT_C            
SYS_C009489          "PERSON_ID" IS NOT NULL                  <null>                       
SYS_C009490          "HEIGHT_IN_CM">0                         <null>

Example 10-27 Re-adding the Removed height Domain

The following code re-adds the removed height domain to the height_in_cm column in the people table. 

ALTER TABLE people
  MODIFY ( height_in_cm ) ADD DOMAIN height;

Example 10-28 Querying the Re-added Domain

A duplicate height_in_cm > 0 constraint is created with the re-added height domain. 

SELECT constraint_name, search_condition_vc, domain_constraint_name
  FROM user_constraints
  JOIN user_cons_columns
  USING ( constraint_name, table_name )
  WHERE table_name = 'PEOPLE'
    AND constraint_type = 'C'
  ORDER BY search_condition_vc;

The output is:


CONSTRAINT_NAME      SEARCH_CONDITION_VC                      DOMAIN_CONSTRAINT_NAME       
-------------------- ---------------------------------------- ------------------------------
SYS_C009491          "DATE_OF_BIRTH"=TRUNC("DATE_OF_BIRTH")   BIRTH_DATE_ONLY_C            
SYS_C009495          "HEIGHT_IN_CM">0                         POSITIVE_HEIGHT_C            
SYS_C009490          "HEIGHT_IN_CM">0                         <null>                       
SYS_C009489          "PERSON_ID" IS NOT NULL                  <null>                       
SYS_C009494          "WEIGHT_IN_KG">0                         POSITIVE_WEIGHT_C

Guidelines

On dropping the domain for a column, the following are preserved by default:

  • The domain's collation.

  • The non-domain constraint that is added to the column.

The domain's default value is not preserved. It is only kept if the default is explicitly applied to the column.

See Also:

  • Oracle Database SQL Language Reference for the syntactic and semantic information about altering a usage domain: ALTER DOMAIN.

  • Viewing Domain Information for the usage domain dictionary views

10.1.4.6 Dropping a Usage Domain

You can drop a usage domain. See Oracle Database SQL Language Reference for more information about dropping a domain.

Example 10-29 Dropping a Usage Domain

The following customers table has one of its column associated with the email domain: 

CREATE TABLE customers (
  cust_id NUMBER,
  cust_email VARCHAR2(100) DOMAIN email);

The following DROP command returns an error because the customers table has the cust_email column associated with email domain. 

DROP DOMAIN email;

The following DROP command succeeds: 

DROP DOMAIN email FORCE;

The cust_email column is disassociated from the email domain and all statements mentioning the email domain are invalidated.

Example 10-30 Dropping a Domain but Preserving the Constraint

The following DROP command succeeds but preserves the default and constraint expressions. 

DROP DOMAIN email FORCE PRESERVE;

The cust_email column retains the default ON NULL email_seq.NEXTVAL || '@domain.com' and preserves the constraint after replacing the domain name in the constraint with the column name: CONSTRAINT email_c CHECK (REGEXP_LIKE (cust_email, '^(\S+)\@(\S+)\.(\S+)$')).

Guidelines

  • To drop a domain that is referenced in a flexible domain, use DROP DOMAIN with the FORCE option.

  • If the domain is not associated with any table column and the domain is not constituent in a flexible domain, the domain is dropped. If the usage domain is in use, the DROP statement fails.

  • If the domain is associated with any table column, you must use the FORCE option to drop the domain. Using the FORCE option also:

    • Removes the default expression, if only domain default is set.

    • Preserves the column default expression, if both domain and column defaults are set.

    • Removes the domain annotation from all associated columns.

    • Preserves the collation of any domain associated columns.

    • Invalidates all SQL dependent statements in the cursor cache.

    • Preserves the constraints on any domain associated columns, if FORCE PRESERVE is used.

See Also:

10.1.4.6.1 About Drop Domain and Recycle Bin

The dropped tables are placed in the recycle bin. To drop a domain associated with tables in the recycle bin, you must use the FORCE option with the DROP command.

You can restore the dropped tables that are in the recycle bin to the before-the-drop position using the FLASHBACK TABLE command. If a table is restored to the before-the-drop position (using FLASHBACK TABLE TO BEFORE DROP) after the associated domain has been dropped, then the table has the same default, collation, nullability, and constraints as was there before the drop, except that none of these attributes would be marked as being inherited from the domain.

Example 10-31 Dropping a Usage Domain Associated with a Dropped Table

The following DROP command tries to remove the weight domain that is associated with the weight_in_kg column of the people table. 


DROP DOMAIN weight;

The command returns the following error output:

ORA-11502: The domain WEIGHT to be dropped has dependent objects.

If you drop the people table, and then drop the weight domain, it returns an error because the table is still in the recycle bin. 

DROP TABLE people;
DROP DOMAIN weight;

The command returns the following error output:

ORA-11502: The domain WEIGHT to be dropped has dependent objects.

Removing the people table from the recycle bin permanently, and then running the DROP command on the weight domain, drops the weight domain. 


PURGE TABLE people;
DROP DOMAIN weight;

Guidelines

Here are some points to note when dropping domains that are associated with dropped tables (tables in recycle bin):

  • While a table is in the recycle bin, the ALTER command on the table is not allowed.

  • If a table with a domain association is in the recycle bin, the associated domain cannot be dropped and the DROP DOMAIN command fails.

  • When the DROP DOMAIN FORCE and DROP DOMAIN FORCE PRESERVE commands are used, the tables in the recycle bin are disassociated from the domain. The database uses the FORCE PRESERVE semantics for tables in the recycle bin, even if you only specify FORCE.

  • If you want to drop the domain that is associated with a table in the recycle bin, you can use the PURGE TABLE command to remove a table from the recycle bin and run the DROP DOMAIN command to drop the domain.

10.1.5 Using a Multi-column Usage Domain

This section explains how you can create, associate, alter, disassociate, and drop a multi-column usage domain.

Topics

10.1.5.1 Creating a Multi-column Usage Domain

You can use a multi-column usage domain to group logical entities that span across table columns, such as addresses.

Example 10-32 Creating a Multi-column Usage Domain for Addresses

You can create a multi-column usage domain called "US_city" with three columns for address entries, as follows:

CREATE DOMAIN US_city AS (
    name  AS VARCHAR2(30) ANNOTATIONS (Address),
    state AS VARCHAR2(2) ANNOTATIONS (Address),
    zip AS NUMBER ANNOTATIONS (Address)
  )
  CONSTRAINT City_CK CHECK(state in ('CA','AZ','TX') and zip < 100000)
  DISPLAY name||', '|| state ||', '||TO_CHAR(zip)
  ORDER state||', '||TO_CHAR(zip)||', '||name
  ANNOTATIONS (Title 'Domain Annotation');

Example 10-33 Creating a Multi-column Usage Domain for Currency

The following code creates a multi-column usage domain called currency that displays monetary values as an amount in a given currency, and the currency codes. The display is sorted by value (low to high), and then by the currency code. 

CREATE DOMAIN currency AS (
    amount  AS NUMBER,
    iso_currency_code  AS CHAR(3 CHAR)
  )
  DISPLAY iso_currency_code || TO_CHAR ( amount, '999,999,990.00' )
  ORDER TO_CHAR ( amount, '999,999,990.00' ) || iso_currency_code;

Guidelines

  • You can have the same data types for the individual columns in a multi-column usage domain as a single-column usage domain.

  • For a multi-column usage domain, a column must not overlap between different domains. For example, on a table T(TC1, TC2, TC3, TC4), domains D1(C1, C2) and D2(C1, C2) cannot be associated as D1(TC1, TC2) and D2(TC2, TC3).

  • Multiple ordered subsets of columns in the same table can be associated with the same domain. For example, domain D1 can be associated as D1(TC1, TC2) and D1(TC3, TC4).

  • Unlike tables that can have at most one LONG column, domains can have multiple columns of LONG data type. Such domains would be useful for evaluating check conditions involving multiple LONG columns using the DOMAIN_CHECK operator.

See Also:

10.1.5.2 Associating a Multi-column Usage Domain at Table Creation

You can use the CREATE TABLE DDL statement to associate a multi-column usage domain with a newly created column.

Example 10-34 Associating the US_city Domain with Multiple Columns

You can create a customer table and associate the US_city domain with the table's three columns. 

CREATE TABLE customer(
  cust_id  NUMBER, 
  cust_name  VARCHAR2(30), 
  city_name  VARCHAR2(30), 
  state  VARCHAR2(2), 
  zip  NUMBER, 
  DOMAIN US_city(city_name, state, zip));

The following example returns an error because CITY and STATE columns are overlapped between domains. 

CREATE TABLE customer(
  cust_id  NUMBER, 
  cust_name  VARCHAR2(30), 
  city_name  VARCHAR2(30), 
  state  VARCHAR2(2), 
  zip  NUMBER, 
  DOMAIN US_city(city_name, state, zip),
  DOMAIN US_city(cust_name, state, zip));

The following example also returns an error because the CITY_NAME column is repeated. 

CREATE TABLE customer(
  cust_id  NUMBER, 
  cust_name  VARCHAR2(30), 
  city_name  VARCHAR2(30), 
  state  VARCHAR2(2), 
  zip  NUMBER, 
  DOMAIN US_city(city_name, city_name, zip));

Example 10-35 Associating the currency Domain with Multiple Columns

You can create an order_items table with its total_paid and currency_code columns associated with the currency domain. 

CREATE TABLE order_items (
  order_id INTEGER, product_id INTEGER,
  total_paid NUMBER(10, 2), currency_code char (3 CHAR ),
  DOMAIN currency ( total_paid, currency_code ));

Guidelines

  • The column names that are passed as the actual parameters to the domain must be unique.

  • Domain columns can be associated with table columns with a different name.

  • The DOMAIN keyword is mandatory.

10.1.5.2.1 Using DML on Columns Associated with Multi-column Domains

The following are some examples of DML statements that you can use on the newly created table columns with associated multi-column usage domains.

Example 10-36 Using DML Commands on the Associated Columns

Inserting values and querying the table display the results based on the currency domain display and order expressions. 

INSERT INTO order_items
VALUES (1, 1,    9.99, 'USD'),
       (2, 2,    8.99, 'GBP'),
       (3, 3,    8.99, 'EUR'),
       (4, 4, 1399,    'JPY'),
       (5, 5,  825,    'INR');
        
SELECT order_id, product_id, 
  DOMAIN_DISPLAY ( total_paid, currency_code ) amount_paid
  FROM order_items
  ORDER BY DOMAIN_ORDER ( total_paid, currency_code );

See Also:

SQL Functions for Usage Domains for details about domain functions, such as DOMAIN_DISPLAY and DOMAIN_ORDER.

The output is:


  ORDER_ID PRODUCT_ID AMOUNT_PAID       
---------- ---------- ------------------
         3          3 EUR           8.99
         2          2 GBP           8.99
         1          1 USD           9.99
         5          5 INR         825.00
         4          4 JPY       1,399.00
10.1.5.3 Associating a Multi-column Usage Domain with Existing Columns

You can use the ALTER TABLE DDL statement with the MODIFY or ADD clause to associate a multi-column usage domain with an existing column or a newly added column in an existing table.

Example 10-37 Associating a Multi-column Usage Domain with Existing Columns

The following example applies the US_city domain to the three columns of the customer table. 

ALTER TABLE customer
  MODIFY (city_name, state, zip) ADD DOMAIN US_city;

Note:

The DOMAIN keyword is mandatory for the ALTER TABLE .. MODIFY statement.

10.1.5.4 Altering a Multi-column Usage Domain

You can alter a multi-column usage domain just as you can alter a single-column usage domain. In a multi-column usage domain, you can change the DISPLAY and ORDER properties. For multi-column domains, altering annotations at the column-level is currently not supported but you can alter the object-level annotations.

Example 10-38 Altering Display and Order Expressions for a Multi-column Usage Domain

The following ALTER statement changes the display expression of the currency domain. The current display expression shows the currency code and then the currency value. The altered display expression shows the currency value and then the currency code. 

ALTER DOMAIN currency
  MODIFY DISPLAY TO_CHAR ( amount, '999,990.00' ) || '-' || iso_currency_code;

The following ALTER statement changes the order expression of the currency domain. The current order expression sorts by the currency value and then by the currency code. The altered order expression sorts by the currency code and then by the currency value. 

ALTER DOMAIN currency
  MODIFY ORDER iso_currency_code || TO_CHAR ( amount, '999,990.00' );

Example 10-39 Querying the Table Associated with the Altered Multi-column Domain

SELECT order_id, product_id,
  DOMAIN_DISPLAY ( total_paid, currency_code ) amount_paid
  FROM order_items
  ORDER BY DOMAIN_ORDER ( total_paid, currency_code );

The output is:


ORDER_ID    PRODUCT_ID    AMOUNT_PAID   
----------- ------------- ---------------
          3             3        8.99-EUR   
          2             2        8.99-GBP   
          5             5      825.00-INR   
          4             4    1,399.00-JPY   
          1             1        9.99-USD

See Also:

10.1.5.5 Disassociating a Multi-column Usage Domain from a Column

You can use the ALTER TABLE DDL statement with the DROP clause to disassociate a multi-column usage domain from a column.

Example 10-40 Examples of Disassociating a Multi-column Usage Domain

The following ALTER TABLE command drops the US_City domain from the city_name, state and zip columns of the customer table. 

ALTER TABLE customer
  MODIFY(city_name, state, zip) DROP DOMAIN;

If a table T with columns (c1, c2, c3) is associated with domain D and another set of columns (c4, c5, c6) is also associated with the domain D, you can drop the domain for all the columns:

ALTER TABLE T
  MODIFY (c1, c2, c6, c5, c4, c3) DROP DOMAIN;

You cannot drop only a subset of the columns that are associated with a multi-column domain. For example, for table T, dropping only c1 and c2 columns, returns an error:

ALTER TABLE T
  MODIFY (c1, c2) DROP DOMAIN;

Example 10-41 More Examples of Disassociating a Multi-column Usage Domain

The following code removes the currency domain from the total_paid and currency_code columns of the order_items table. 


ALTER TABLE order_items
  MODIFY ( total_paid, currency_code ) DROP DOMAIN;

Guidelines

  • There can be multiple ordered subsets of columns in the same table that are associated with the same domain. The removing multi-column domain syntax must specify the list of associated columns to be dissociated.

  • Domain name cannot be specified.

  • You cannot specify other options for ALTER TABLE ..MODIFY with ALTER TABLE ..DROP DOMAIN.

10.1.5.6 Dropping a Multi-column Usage Domain

To drop a multi-column usage domain, use the same syntax as used for a single-column usage domain.

Guidelines

  • To drop a domain that is referenced in a flexible domain, use DROP DOMAIN with the FORCE option.

See Also:

  • Oracle Database SQL Language Reference for the syntactic and semantic information about dropping a usage domain: DROP DOMAIN

  • Dropping a Usage Domain for more information about dropping a usage domain.

10.1.6 Using a Flexible Usage Domain

This section explains how you can create, associate, disassociate, and drop a flexible usage domain.

Note:

You cannot alter a flexible usage domain but as an alternative, you can disassociate the flexible domain from the tables, DROP the domain, recreate the domain, and re-associate it with the tables.

Topics:

10.1.6.1 Creating a Flexible Usage Domain

You can create a flexible usage domain that references other non-flexible domains (single and multi-column usage domains) and enables you to apply one of them to table columns depending on the context of the data. For example, you can create multi-column domains to validate address formats for each country. Table columns can only belong to one domain. So, to enable the database to use the address domain corresponding to each country for each row, create a flexible domain over the country domains. Associate columns with the flexible domain using the country as the discriminant column. Each row can then apply the address rules for the corresponding country domain.

Example 10-42 Creating a Flexible Usage Domain for Temperature Readings

The following code creates a flexible usage domain called temperature from three domains, namely celcius, fahrenheit, and kelvin. The domains that are created for each temperature scale have appropriate absolute zero checks and display expressions. There is no ELSE clause in the flexible domain, so you can insert values for other temperature units, and temperature values for such temperature units are unconstrained. 


CREATE DOMAIN celcius AS NUMBER
  CONSTRAINT abs_zero_c_c CHECK ( celcius >= -273.15 )
  DISPLAY celcius || ' °C';
   
CREATE DOMAIN fahrenheit AS NUMBER
  CONSTRAINT abs_zero_f_c CHECK ( fahrenheit >= -459.67 )
  DISPLAY fahrenheit || ' °F';
   
CREATE DOMAIN kelvin AS NUMBER
  CONSTRAINT abs_zero_k_c CHECK ( kelvin >= 0 )
  DISPLAY kelvin || ' K';

The following code creates a flexible domain that selects which domain to use based on the temperature units.


CREATE FLEXIBLE DOMAIN temperature (
  temp
 ) CHOOSE DOMAIN USING ( units char(1) ) 
 FROM (
  CASE units
    WHEN 'C' THEN celcius ( temp )
    WHEN 'F' THEN fahrenheit ( temp )
    WHEN 'K' THEN kelvin ( temp )
  END);

Example 10-43 Creating a Flexible Usage Domain for Addresses

The following code creates multi-column usage domains to represent United States and United Kingdom (British) addresses, and a default address domain for other countries.

/* US addresses */
CREATE DOMAIN us_address AS (
  line_1  AS VARCHAR2(255 CHAR) NOT NULL,
  town    AS VARCHAR2(255 CHAR) NOT NULL,
  state   AS VARCHAR2(255 CHAR) NOT NULL,
  zipcode AS VARCHAR2(10 CHAR) NOT NULL
 ) CONSTRAINT us_address_c check ( 
   REGEXP_LIKE ( zipcode, '^[0-9]{5}(-[0-9]{4}){0,1}$' ));
 
/* British addresses */
CREATE DOMAIN gb_address AS ( 
  street   AS VARCHAR2(255 CHAR) NOT NULL,
  locality AS VARCHAR2(255 CHAR),
  town     AS VARCHAR2(255 CHAR) NOT NULL,
  postcode AS VARCHAR2(10 CHAR) NOT NULL
 ) CONSTRAINT gb_postcode_c check (
   REGEXP_LIKE ( 
    postcode, '^[A-Z]{1,2}[0-9][A-Z]{0,1} [0-9][A-Z]{2}$' ));
   
/* Default address */
CREATE DOMAIN global_address AS ( 
  line_1   AS VARCHAR2(255) NOT NULL,
  line_2   AS VARCHAR2(255),
  line_3   AS VARCHAR2(255),
  line_4   AS VARCHAR2(255),
  postcode AS VARCHAR2(10));

The following code creates a flexible domain that selects which multi-column address domain to use based on the country code.

CREATE FLEXIBLE DOMAIN address (
  line_1, line_2, line_3, line_4,
  postal_code      
 )
 CHOOSE DOMAIN USING ( country_code VARCHAR2(2 char) )
 FROM (
  CASE country_code
    WHEN 'GB' THEN gb_address ( line_1, line_2, line_3, postal_code )
    WHEN 'US' THEN us_address ( line_1, line_2, line_3, postal_code )
    ELSE global_address ( line_1, line_2, line_3, line_4, postal_code )
  END);

Note:

To create a flexible domain, you must have the EXECUTE privilege on each constituent domain.

See Also:

Oracle Database SQL Language Reference for the syntactic and semantic information about creating a usage domain: CREATE DOMAIN

10.1.6.2 Associating a Flexible Usage Domain at Table Creation

You can use the CREATE TABLE DDL to associate a flexible usage domain with a set of columns that are newly created by the new table. To add a flexible domain to a set of columns, specify the list of columns to be associated with the domain (in the domain column order), followed with the list of columns to be used as the discriminant (in discriminant column order in the flexible domain).

Example 10-44 Associating the temperature Flexible Domain with New Table Columns

Create a sensor_readings table using the temperature flexible domain, while specifying the discriminant column with the USING keyword. 


CREATE TABLE sensor_readings (
  sensor_id integer, reading_timestamp TIMESTAMP,
  temperature_reading NUMBER,
  temperature_units   CHAR(1 CHAR),
  DOMAIN temperature ( temperature_reading )
    USING ( temperature_units ));

Example 10-45 Associating the address Flexible Domain with New Table Columns

The following code creates a new table called addresses and associates its columns with the address flexible domain. 

CREATE TABLE addresses (
  line_1 VARCHAR2(255) NOT NULL,
  line_2 VARCHAR2(255),
  line_3 VARCHAR2(255),
  line_4 VARCHAR2(255),
  country_code   VARCHAR2(2 CHAR) NOT NULL,
  postal_code    VARCHAR2(10 CHAR), 
  DOMAIN address ( 
    line_1, line_2, line_3, line_4, postal_code)
    USING ( country_code ));

Note:

The DOMAIN and USING keywords are mandatory when associating flexible domains.

10.1.6.2.1 Using DML on Columns Associated with Flexible Domains

The following are some examples of DML statements on the newly created table columns that have associated flexible usage domains.

Example 10-46 Using DML Commands on Columns Associated with the temperature Flexible Domain 

INSERT INTO sensor_readings 
VALUES ( 1, timestamp'2023-06-08 12:00:00', 21.1, 'C' ),
       ( 1, timestamp'2023-06-08 12:05:00', 21.2, 'C' ),
       ( 1, timestamp'2023-06-08 12:10:00', 20.9, 'C' ),
       ( 2, timestamp'2023-06-08 12:00:00', 68.5, 'F' ),
       ( 2, timestamp'2023-06-08 12:05:00', 68.1, 'F' ),
       ( 2, timestamp'2023-06-08 12:10:00', 68.9, 'F' ),
       ( 3, timestamp'2023-06-08 12:00:00', 290.23, 'K' ),
       ( 3, timestamp'2023-06-08 12:05:00', 289.96, 'K' ),
       ( 3, timestamp'2023-06-08 12:10:00', 289.65, 'K' ),
       ( 4, timestamp'2023-06-08 12:00:00', 528.15, 'R' ),
       ( 4, timestamp'2023-06-08 12:05:00', 528.42, 'R' ),
       ( 4, timestamp'2023-06-08 12:10:00', 527.99, 'R' );

SELECT sensor_id, reading_timestamp,
  DOMAIN_DISPLAY ( temperature_reading, temperature_units ) temp
  FROM  sensor_readings;

See Also:

SQL Functions for Usage Domains for details about domain functions, such as DOMAIN_DISPLAY and DOMAIN_ORDER.

The output is:

 SENSOR_ID READING_TIMESTAMP              TEMP                                       
---------- ------------------------------ -------------------------------------------
         1 08-JUN-2023 12.00.00.000000000 21.1 °C                                    
         1 08-JUN-2023 12.05.00.000000000 21.2 °C                                    
         1 08-JUN-2023 12.10.00.000000000 20.9 °C                                    
         2 08-JUN-2023 12.00.00.000000000 68.5 °F                                    
         2 08-JUN-2023 12.05.00.000000000 68.1 °F                                    
         2 08-JUN-2023 12.10.00.000000000 68.9 °F                                    
         3 08-JUN-2023 12.00.00.000000000 290.23 K                                   
         3 08-JUN-2023 12.05.00.000000000 289.96 K                                   
         3 08-JUN-2023 12.10.00.000000000 289.65 K 
         4 08-JUN-2023 12.00.00.000000000 <null>                                    
         4 08-JUN-2023 12.05.00.000000000 <null>                                    
         4 08-JUN-2023 12.10.00.000000000 <null>

Example 10-47 Out-of-bounds Constraint Errors

The following values are out-of-bounds constraint errors for their respective temperature scales.


INSERT INTO sensor_readings 
  VALUES ( 1, timestamp'2023-06-08 12:15:00', -400, 'C' );

INSERT INTO sensor_readings 
  VALUES ( 2, timestamp'2023-06-08 12:15:00', -999, 'F' );

INSERT INTO sensor_readings 
  VALUES ( 3, timestamp'2023-06-08 12:15:00',   -1, 'K' );

Example 10-48 Using DML on Columns Associated with the address Flexible Domain 

-- Great Britian
INSERT INTO addresses ( line_1, line_3, country_code, postal_code ) 
  VALUES ( '10 Big street', 'London', 'GB', 'N1 2LA' );
 
-- United States
INSERT INTO addresses ( line_1, line_2, line_3, country_code, postal_code ) 
  VALUES ( '10 another road', 'Las Vegas', 'NV', 'US', '87654-3210' );  
 
-- Tuvalu
INSERT INTO addresses ( line_1, country_code ) 
  VALUES ( '10 Main street', 'TV' );  
 
SELECT * FROM addresses;

The output is:


LINE_1             LINE_2       LINE_3    LINE_4    COUNTRY_CODE    POSTAL_CODE  
------------------ ------------ --------- --------- --------------- -----------
10 Big street      <null>       London    <null>    GB              N1 2LA         
10 another road    Las Vegas    NV        <null>    US              87654-3210     
10 Main street     <null>       <null>    <null>    TV              <null>

The following INSERT command returns an error because it tries to insert UK address with US zip code. 

INSERT INTO addresses ( line_1, line_3, country_code, postal_code ) 
  VALUES ( '10 Big street', 'London', 'GB', '12345-6789' );
ORA-11534: check constraint (schema.SYS_C0010286) due to domain constraint schema.SYS_DOMAIN_C00639 of domain schema.ADDRESS violated

The following INSERT command returns an error because it tries to insert US address without values for the state. 

INSERT INTO addresses ( line_1, line_2, country_code, postal_code ) 
  VALUES ( '10 another road', 'Las Vegas', 'US', '87654-3210' ); 
ORA-11534: check constraint (schema.SYS_C0010289) due to domain constraint schema.SYS_DOMAIN_C00636 of domain schema.ADDRESS violated
10.1.6.3 Associating a Flexible Domain with Existing Columns

You can use the ALTER TABLE DDL with the MODIFY or ADD clauses to associate a flexible usage domain with an existing column or a newly added column in an existing table.

Example 10-49

The following code creates a new table called temp_sensor_readings. 

CREATE TABLE temp_sensor_readings (
  sensor_id integer, reading_timestamp TIMESTAMP,
  temperature_reading NUMBER,
  temperature_units   CHAR(1 CHAR));

The following code associates the temperature flexible domain with an existing column called temperature_reading. 

ALTER TABLE temp_sensor_readings
  MODIFY (temperature_reading, temperature_units)
  ADD DOMAIN temperature;

Guidelines

  • The DOMAIN keyword is mandatory when associating flexible domains.

  • The USING keyword is mandatory for ALTER TABLE .. ADD statement.

  • You cannot have the same column associated with multiple flexible domains, whether as a domain column or as a discriminant column.

  • You cannot have the column associated with the same domain, but with a different column positioning.

10.1.6.4 Disassociating a Flexible Usage Domain from Columns

You can use the ALTER TABLE DDL with the DROP clause to disassociate a flexible usage domain from a column.

Example 10-50

The following code drops the temperature domain from the temp_sensor_readings table. 

ALTER TABLE temp_sensor_readings
  MODIFY (temperature_reading, temperature_units) DROP DOMAIN;

Guidelines

  • The domain name is not required because the database knows which columns are associated with which domains and one column can only be associated with one domain.

  • You cannot specify other options for ALTER TABLE ..MODIFY with ALTER TABLE ..DROP DOMAIN.

10.1.6.5 Dropping a Flexible Usage Domain

To drop a flexible usage domain, use the same syntax as used for a single-column usage domain.

Guidelines

  • To drop a domain that is referenced in a flexible domain, use DROP DOMAIN with the FORCE option. Doing this also drops the flexible domain.

  • To drop a flexible domain in the FORCE mode, you must have privileges to drop the constituent flexible domains.

See Also:

  • Oracle Database SQL Language Reference for the syntactic and semantic information about dropping a usage domain: DROP DOMAIN.

  • Dropping a Usage Domain for more information about dropping a usage domain.

10.1.7 Specifying a Data Type for a Domain

A domain data type can be one of Oracle data types. However, a qualified domain name must not collide with the qualified user-defined data types, or with Oracle built-in types.

If a column is associated with a domain, and the column data type is not specified, then the domain data type is used for the associated column as the default data type. If the associated column already has a data type, the column's data type is used.

If the domain data type is defined as non-STRICT, the associated column's data type only needs to be compatible with the domain data type, meaning that their data type must be the same, but the length, precision, and scale can be different. For instance, for a non-strict domain, you can associate a domain of VARCHAR2(10) with any VARCHAR2 column.

If the domain data type is defined as STRICT, the associated column's data type must be compatible with the domain data type and also match the length, precision, and scale of the domain data type.

Example 10-51 Associating Columns with Domain Data Type

The following example creates a year_of_birth domain and a email_dom domain and associates the domains with columns to show the compatibility of domain and column data types. 


DROP DOMAIN IF EXISTS year_of_birth;

CREATE DOMAIN year_of_birth AS NUMBER(4)
    CONSTRAINT CHECK ( (TRUNC(year_of_birth) = year_of_birth) AND (year_of_birth >= 1900) );


DROP DOMAIN IF EXISTS email_dom;

CREATE DOMAIN email_dom AS VARCHAR2(100)
    CONSTRAINT email_chk check (REGEXP_LIKE (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

DROP TABLE IF EXISTS newcustomers PURGE;
CREATE TABLE newcustomers (
  cust_Id NUMBER,
  cust_year_of_birth NUMBER(6) DOMAIN year_of_birth,
  cust_hq_email VARCHAR2(50),
  cust_office_email VARCHAR2(100),
  cust_rep_email VARCHAR2(100));

DESC newcustomers;

The output is:


 Name                          Null?    Type
 ----------------------------- -------- ----------------------------
 CUST_ID                                NUMBER
 CUST_YEAR_OF_BIRTH                     NUMBER(6) SH.YEAR_OF_BIRTH
 CUST_HQ_EMAIL                          VARCHAR2(50)
 CUST_OFFICE_EMAIL                      VARCHAR2(100)
 CUST_REP_EMAIL                         VARCHAR2(200)

The cust_year_of_birth column is defined as an Oracle data type: Number, and also associated with the year_of_birth domain, so the column's data type is assigned to the column. The cust_year_of_birth column inherits all the properties defined in the year_of_birth domain, such as constraint, display, and ordering properties.

The following example creates a ukcustomers table with a column associated with the year_of_birth domain, but without the column's data type: 

CREATE TABLE ukcustomers (
  cust_Id NUMBER,
  cust_year_of_birth DOMAIN year_of_birth);

DESC ukcustomers;

The output is:


 Name                           Null?    Type
 ------------------------------ -------- ----------------------------
 CUST_ID                                 NUMBER
 CUST_YEAR_OF_BIRTH                      NUMBER(4) SH.YEAR_OF_BIRTH

Here, the cust_year_of_birth column is assigned the domain's data type, which is NUMBER(4).

In the following example, the DOMAIN keyword is omitted. 

CREATE TABLE incustomers (
  cust_id NUMBER,
  cust_year_of_birth year_of_birth);

DESC incustomers;

The output is:


 Name                          Null?    Type
 ----------------------------- -------- ----------------------------
 CUST_ID                                NUMBER
 CUST_YEAR_OF_BIRTH                     NUMBER(4) SH.YEAR_OF_BIRTH

In the column definition clause, the domain clause must either replace the data type clause, or immediately follow it.

If a domain column data type is not defined as STRICT, you can associate a domain to any column with the same data type, irrespective of the column length.

The following ALTER commands succeed because the domain and column data type has the same data type and the column lengths are not checked for non-STRICT domains. 

ALTER TABLE newcustomers
  MODIFY cust_hq_email DOMAIN email_dom;

ALTER TABLE newcustomers
  MODIFY cust_office_email DOMAIN email_dom;

ALTER TABLE newcustomers
  MODIFY cust_rep_email DOMAIN email_dom;

If a domain column data type is defined as STRICT, the domain association works only when the column and the domain have the same data type and their lengths also match. 


DROP DOMAIN IF EXISTS email_dom;

CREATE DOMAIN email_dom AS VARCHAR2(100) STRICT
  CONSTRAINT email_chk check (REGEXP_LIKE (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

The following ALTER command succeeds. 

ALTER TABLE newcustomers
  MODIFY cust_office_email DOMAIN email_dom;

The following ALTER commands fail because the column length and the domain length do not match. 

ALTER TABLE newcustomers
  MODIFY cust_hq_email DOMAIN email_dom;

ALTER TABLE newcustomers
  MODIFY cust_rep_email DOMAIN email_dom;

Table Column Data Type to non-STRICT Domain Column Data Type Compatibility

Each of the following points lists the compatible types. You can associate any table column with a domain column that has a compatible type. The table and domain columns can have different lengths, precisions, and scales for non-STRICT domains.

  • NUMBER, NUMER(p), NUMBER(p, s), NUMERIC, NUMERIC(p), NUMERIC(p, s), DECIMAL, DECIMAL(p), DEC, DEC(p), INTEGER, INT, SMALLINT, FLOAT, FLOAT(p), REAL, DOUBLE_PRECISION

  • CHAR(L), CHAR(L CHAR), CHAR(L BYTE), CHARACTER(L CHAR), CHARACTER(L BYTE), CHARACTER(L)

  • NCHAR(L), NATIONAL CHARACTER(L), NATIONAL CHAR (L)

  • VARCHAR2(L), VARCHAR2(L CHAR), VARCHAR2(L BYTE), CHAR VARYING(L CHAR), CHAR VARYING(L BYTE), CHAR VARYING(L), CHARACTER VARYING(L CHAR), CHARACTER VARYING(L BYTE), CHARACTER VARYING(L)

  • NVARCHAR2(L), NATIONAL CHAR VARYING (L), NATIONAL CHARACTER VARYING (L)

  • TIMESTAMP, TIMESTAMP(p)

  • TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP(p) WITH LOCAL TIME ZONE

  • INTERVAL YEAR TO MONTH, INTERVAL YEAR(p) TO MONTH

  • INTERVAL DAY TO SECOND, INTERVAL DAY(p) TO SECOND, INTERVAL DAY TO SECOND(s), INTERVAL DAY(p) TO SECOND(s)

  • ROWID, UROWID, UROWID(p)

  • RAW(p), RAW(p)

Table Column Data Type to STRICT Domain Column Data Type Compatibility

Each of the following points lists the compatible types. You can associate any table column with a domain column that has a compatible type. The table and domain columns must have an exact match for length, precision, and scale for STRICT domains.

  • NUMBER(*), NUMBER

  • NUMBER(p, 0), NUMERIC(p), NUMERIC(p, 0), DECIMAL(p), DEC(p), provided the table column data type and the domain column data type have the same precision.

  • NUMBER(p, s), NUMERIC(p, s), DECIMAL(p, s), DEC(p, s), provided the table column data type and the domain column data type have the same precision and scale.

  • NUMBER(*,0), NUMERIC(*), NUMERIC(*,0), DEC(*), DEC(*,0), DECIMAL(*), DECIMAL(*,0), INTEGER, INT, SMALLINT

  • FLOAT(63), REAL

  • FLOAT, FLOAT(126), DOUBLE PRECISION

  • CHAR(L CHAR), CHAR(L BYTE), CHARACTER (L), provided the size in bytes is the same for the column data type and domain column data type. For example, CHAR (4 BYTE) can be associated with a STRICT domain column of CHAR(1 CHAR) if 1 character takes 4 bytes.

  • NCHAR(L), NATIONAL CHARACTER(L), NATIONAL CHAR (L), provided the size in bytes is the same for the column data type and domain column data type.

  • VARCHAR2(L CHAR), VARCHAR2(L BYTE), CHARACTER VARYING (L), CHAR VARYING(L), provided the size in bytes is the same for the column data type and domain column data type.

  • NVARCHAR2(L), NATIONAL CHAR VARYING (L), NATIONAL CHARACTER VARYING (L), provided the size in bytes is the same for the column data type and domain column data type.

  • TIMESTAMP, TIMESTAMP(6)

  • TIMESTAMP WITH TIME ZONE, TIMESTAMP(6) WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP(6) WITH LOCAL TIME ZONE

  • INTERVAL YEAR TO MONTH, INTERVAL YEAR(2) TO MONTH

  • INTERVAL DAY TO SECOND, INTERVAL DAY(2) TO SECOND, INTERVAL DAY TO SECOND(6), INTERVAL DAY(2) TO SECOND(6)

  • ROWID, UROWID(10)

  • UROWID, UROWID(4000), ROWID(4000)

Table Column Data Type to Domain Column Data Type Compatibility

The following column data types are only compatible with an equivalent domain column data type. For example, a DATE column type can be associated only with a DATE domain column type.

  • BINARY_FLOAT

  • BINARY_DOUBLE

  • DATE

  • BLOB

  • CLOB

  • NCLOB

  • BFILE

  • LONG

  • LONG RAW

  • JSON

Rules of Associating Table Column and Usage Domain Based on Data Type

For a domain's data type of VARCHAR2(L [CHAR|BYTE]), let L-bytes be the maximum length in bytes corresponding to L, given that the National Language Support (NLS) setting has the session-level length semantics value in NLS_LENGTH_SEMANTICS as BYTE.

The following rules apply when you associate a column with the domain:

  • If the domain is defined as non-STRICT, the domain can be associated with columns of data type VARCHAR2(x) for any x-bytes. For non-STRICT domains, L and x can be different.

  • If the domain is defined as STRICT, the domain can be associated with columns of data type VARCHAR2(x) for any x-bytes = L-bytes. For STRICT domains, L and x must be the same number of bytes, even after converting L|x CHAR to BYTES, if needed.

For instance, if a domain data type specification is VARCHAR2 STRICT, and if MAX_STRING_SIZE is STANDARD, then the domain can associate with columns of VARCHAR2(L BYTE) data type, where L = 4000. If the current NLS settings in the session are such that at most 2 bytes are needed to represent a character, then a column of VARCHAR2(2000 CHAR) data type can be associated with the domain. If MAX_STRING_SEMANTICS is changed to EXTENDED, then columns of data type: VARCHAR2(L BYTE) for L = 32767 or VARCHAR2(16383 CHAR) can be associated with the domain.

Similar rules apply to NVARCHAR2(L), CHAR(L [CHAR|BYTE]), and NCHAR(L).

10.1.8 Changing the Usage Domain Properties

As your application evolves, column definitions may need to change, and with that the properties of the associated usage domains must also change. At the time of altering a domain, you can only alter the annotations, display expression, and order expression for domains. The following suggests ways to change the other domain properties.

You can use these examples as a means to get started, or to build upon and adapt them to your specific data and business requirements. The first example is a manual method and the next one is an online method using the dbms_redefinition package.

Example 10-52 Changing the Domain Properties Manually

Suppose that you want to change the string length of an email domain field from 100 to 200. To make that change, you must complete the following steps:

  • Alter the associated column's string length to 200.

  • Drop the current domain associated with the column.

  • Create a new domain with the email string length as 200.

  • Re-associate columns associated with the current domain with the new domain.

If the current domain definition and column association are as follows:

CREATE DOMAIN email_dom AS VARCHAR2(100)
  CONSTRAINT email_chk CHECK (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

CREATE TABLE t1 (
  id NUMBER,
  email DOMAIN email_dom
);

To view the list of columns associated with the domain:

SELECT table_name, column_name
  FROM user_tab_columns
  WHERE domain_name = 'EMAIL_DOM';

TABLE_NAME              COLUMN_NAME
---------------------- -------------------
T1                      EMAIL

Alter the t1 table to change the email column with string size as 200:

ALTER TABLE t1 modify email varchar2(200);

Drop the domain with the FORCE PRESERVE option: 

DROP DOMAIN email_dom FORCE PRESERVE;

Then, create a new domain with the email string size as 200:

CREATE DOMAIN email_dom AS VARCHAR2(200)
  CONSTRAINT email_chk CHECK (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

And, re-associate the new domain with the email column:

ALTER TABLE t1
  MODIFY email DOMAIN email_dom;

Example 10-53 Changing the Domain Properties Using the online dbms_redefinition Package

You can use the Oracle online table reorganization package called dbms_redefinition to change permitted values, such as updating which currencies are supported. For instance, to change the supported currencies in a currency domain, the required steps are:

  • Create a new domain.

  • Migrate columns associated with the current domain to use the new domain.

The following example has only basic error handling. There can be instances where an error can occur after several tables are migrated, such as when some data violates the new constraint. A complete solution would need to account for these scenarios.

The following code creates a domain with a constraint that allows the following currencies: USD, GBP, and EUR.

CREATE DOMAIN currency AS (
  amount AS NUMBER,
  iso_currency_code AS CHAR ( 3 CHAR )
 ) CONSTRAINT supported_currencies_c 
    CHECK ( iso_currency_code in ( 'USD', 'GBP', 'EUR' ) );

Suppose that the following tables have columns associated with the currency domain. 

CREATE TABLE order_items (
  order_id   INTEGER, product_id INTEGER,
  total_paid NUMBER(10, 2), currency_code CHAR ( 3 CHAR ),
  DOMAIN currency ( total_paid, currency_code ),
  PRIMARY KEY ( order_id, product_id )
);
        
CREATE TABLE product_prices (
  product_id    INTEGER,
  unit_price    NUMBER(10, 2), 
  currency_code CHAR( 3 char ),
  DOMAIN currency ( unit_price, currency_code ),
  PRIMARY KEY ( product_id, currency_code )
);

Use the INSERT command to store some values into the product_prices and order_items tables. 

INSERT INTO product_prices
VALUES (1, 9.99, 'USD'),
       (1, 8.99, 'GBP'),
       (1, 8.99, 'EUR');
 
INSERT INTO order_items
VALUES (1, 1, 9.99, 'USD'),
       (2, 1, 8.99, 'GBP'),
       (3, 1, 8.99, 'EUR');
        
COMMIT;

Suppose that your business is expanding and you want to support more currencies. You cannot modify the constraints directly, so you need an alternative approach that includes:

  • Creating a new domain.

  • Altering the product_prices and order_items tables to drop the existing domain from the associated columns while preserving the constraints.

  • Altering the tables to add the new domain.

  • Removing the preserved constraints from the tables.

However, when done online, these are blocking DDL statements. Instead, you can use the dbms_redefinition package to modify the constraints online.

You must create a new domain with the constraint including the newly supported currencies, and associate it with temporary tables. The new domain replaces the original domain.


CREATE DOMAIN currency_d as (
  amount            AS NUMBER,
  iso_currency_code AS CHAR ( 3 CHAR )
 ) CONSTRAINT supported_currencies_d_c 
  CHECK ( iso_currency_code in ( 'USD', 'GBP', 'EUR', 'CAD', 'MXP', 'INR', 'JPY' ) );
   
CREATE TABLE order_items_tmp (
  order_id   INTEGER, product_id INTEGER,
  total_paid NUMBER(10, 2), currency_code CHAR ( 3 CHAR ),
  DOMAIN currency_d ( total_paid, currency_code ) ,
  PRIMARY KEY ( order_id, product_id )
);
        
CREATE TABLE product_prices_tmp (
  product_id    INTEGER,
  unit_price    NUMBER(10, 2), 
  currency_code CHAR (3 CHAR),
  DOMAIN currency_d ( unit_price, currency_code ),
  PRIMARY KEY ( product_id, currency_code )
);

To make the code more reusable, you can create a redefine_table procedure that calls the dbms_redefinition procedures to copy the properties of the temporary table columns to the current table columns, and then swap the current table columns to the new domain. 

 
DECLARE
   
  PROCEDURE redefine_table ( current_table VARCHAR2, staging_table VARCHAR2 ) AS
    num_errors pls_integer;
  BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE(user, current_table);    
    DBMS_REDEFINITION.START_REDEF_TABLE(user, current_table, staging_table);
   
    DBMS_REDEFINITION.copy_table_dependents(
      uname               => user,
      orig_table          => current_table,
      int_table           => staging_table,
      copy_constraints    => false,
      num_errors          => num_errors);
     
    IF num_errors > 0 THEN
      dbms_redefinition.abort_redef_table(user, current_table, staging_table);
      raise_application_error ( -20001, num_errors || ' errors copying dependents from ' || current_table || ' to ' || staging_table );
    ELSE 
      dbms_redefinition.finish_redef_table(user, current_table, staging_table);
    END IF;
  END redefine_table;
 
BEGIN 
   
  FOR tabs IN ( 
    SELECT distinct table_name from user_tab_cols
    WHERE  domain_name = 'CURRENCY'
  ) LOOP
    redefine_table(tabs.table_name, tabs.table_name || '_TMP');
  END LOOP;
 
END;
/

Use DML on the product_prices table to see if the new currencies are now supported. 

-- New currencies now supported
INSERT INTO product_prices
  VALUES (1, 9.99, 'CAD');
 
-- Invalid currencies raise exception
INSERT INTO product_prices
  VALUES (1, 9.99, 'N/A');
 
SELECT * FROM product_prices;

The output is:

PRODUCT_ID UNIT_PRICE CUR
---------- ---------- ---
         1       9.99 USD
         1       8.99 GBP
         1       8.99 EUR
         1       9.99 CAD

Clean up the temporary objects and old domain: currency. 

DROP TABLE order_items_tmp PURGE;
DROP TABLE product_prices_tmp PURGE;
DROP DOMAIN currency;

See Also:

DBMS_REDEFINITION in PL/SQL Packages and Types Reference Guide.

10.1.9 SQL Functions for Usage Domains

Domain functions enable you to work with usage domains more efficiently.

You can use the following SQL functions with usage domains:

  • DOMAIN_DISPLAY returns the domain display expression for the domain that the argument is associated with.

  • DOMAIN_NAME returns the qualified domain name of the domain that the argument is associated with. Note that if there is a public synonym to the domain, it is returned; otherwise domain name is returned in domain_owner.domain_name format.

  • DOMAIN_ORDER returns the domains order expression for the domain that the argument is associated with.

  • DOMAIN_CHECK(domain_name, value1, value2, ...) applies constraint conditions of domain_name (not null or check constraint) to the value expression. It also checks the values for type compatibility. There can be many values; the number of value expressions must match the number of columns in the domain or the statement raises an error.

  • DOMAIN_CHECK_TYPE(domain_name, value1, value2, ...) verifies whether the input expressions are compatible with the domain's data type. There can be many values; the number of value expressions must match the number of columns in the domain or the statement raises an error.

See Also:

Domain Functions in Oracle Database SQL Language Reference for more information about using SQL functions for usage domains

10.1.10 Viewing Domain Information

You can use the dictionary views to get information about the domains. Dictionary views can also help identify columns that have different properties, such as constraint and default expressions when compared to their associated domain.

10.1.10.1 Dictionary Views for Usage Domains

Dictionary views: [USER|DBA|ALL]_DOMAINS and [USER|DBA|ALL]_DOMAIN_COLS represent domains and provide the following information about the domain columns. For flexible domains, the views also include the domain selector expression.

  • Domain name

  • Domain owner

  • Display and ordering expression

  • Default value

  • Data type of the domain

  • Collation

The following dictionary views are available for usage domains:

  • ALL_DOMAINS describes the domains accessible to the current user.

  • DBA_DOMAINS describes all domains in the database.

  • USER_DOMAINS describes the domains owned by the current user.

  • ALL_DOMAIN_COLS describes columns of the domains accessible to the current user.

  • DBA_DOMAIN_COLS describes columns of all domains in the database.

  • USER_DOMAIN_COLS describes columns of the domains owned by the current user.

  • ALL_DOMAIN_CONSTRAINTS describes constraint definitions in the domains accessible to the current user.

  • DBA_DOMAIN_CONSTRAINTS describes constraint definitions in all domains in the database.

  • USER_DOMAIN_CONSTRAINTS describes constraint definitions in the domains owned by the current user.

See Also:

Oracle Database Reference for more information about the following views that are used for usage domains: ALL_DOMAINS, DBA_DOMAINS, USER_DOMAINS, ALL_DOMAIN_COLS, DBA_DOMAIN_COLS, USER_DOMAIN_COLS, ALL_DOMAIN_CONSTRAINTS, DBA_DOMAIN_CONSTRAINTS, USER_DOMAIN_CONSTRAINTS

10.1.11 Built-in Usage Domains

Oracle Database provides some built-in usage domains that you can use directly on table columns, for example, email_d, ssn_d, and credit_card_number_d. Built-in usage domains exist in all PDBs. When a new PDB is added into a CDB, the built-in usage domains automatically get created and added in the PDB.

The following is a list of all built-in usage domains that are supported in Oracle Database:

Table 10-1 Built-in Domains

Name Allowed Values Description

email_d

^([a-zA-Z0-9!#$%&*+=?^_`{|}~-]+(\.[ A-Za-z0-9!#$%&*+=?^_`{|}~-]+)*)@(([ a-zA-Z0-9]([a-zA-Z0-9-]*[a-zA-Z0-9] )?\.)+[a-zA-Z0-9]([a-zA-Z0-9-]*[a-z A-Z0-9])?)$

Email address

Implementation: Regex

day_short_d

'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN'

Day of the week in short format

Domain Order: based on nls_territory value

Implementation: List

day_d

'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'

Day of the week in long format

Domain Order: based on nls_territory value

Implementation: List

month_short_d

'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'

Month in short format

Implementation: List

month_d

'JANUARY', 'FEBRUARY', 'MARCH', 'APRIL', 'MAY', 'JUNE',

'JULY', 'AUGUST', 'SEPTEMBER', 'OCTOBER', 'NOVEMBER', 'DECEMBER'

Month in long format

Implementation: List

year_d

year > 0 and year <= 9999

Year (0-9999)

Implementation: Check

positive_number_d

positive_number > 0

Positive Number : 1,2,3,4,...

Implementation: Check

negative_number_d

negative_number < 0

Negative Number : ...,-3,-2,-1

Implementation: Check

non_positive_number_d

non_positive_number <= 0

Non-Positive Number: ...,-3,-2,-1,0

Implementation: Check

non_negative_number_d

non_negative_number >= 0

Non-Negative Number : 0,1,2,3...

Implementation: Check

mac_address_d

^([a-fA-F0-9]{2}[-]){5}[a-fA-F0-9]{2}$

^([a-fA-F0-9]{2}[:]){5}[a-fA-F0-9]{2}$

MAC address

Implementation: regex

ssn_d

^[0-9]{3}[-][0-9]{2}[-][0-9]{4}$

US SSN

Implementation: regex

credit_card_number_d

^([0-9]{4}[-]){3}[0-9]{4}$

^([0-9]{4}[ ]){3}[0-9]{4}$

^([0-9]{4}){3}[0-9]{4}$

^[0-9]{4}[-][0-9]{6}[-][0-9]{5}$

^[0-9]{4}[ ][0-9]{6}[ ][0-9]{5}$

^[0-9]{15}$

Credit Card Number

Implementation: regex

ipv4_address_d

^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$

IPv4 Address

Implementation: regex

ipv6_address_d

^([0-9a-fA-F]{4}[:]){7}[0-9a-fA-F]{4}$

^([0-9a-fA-F]{4}[:]){6}(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$

IPv6 Address

Implementation: regex

subnet_mask_d

^(((255\.){3}(255|254|252|248|240|224|192|128|0))|((255\.){2}(255|254|252|248|240|224|192|128|0)\.0)|

((255\.)(255|254|252|248|240|224|192|128|0)(\.0+){2})|((255|254|252|248|240|224|192|128|0)(\.0){3}))$

Subnet Mask

Implementation: regex

sha1_d

^[0-9a-fA-F]{40}$

SHA1

Implementation: regex

sha256_d

^[0-9a-fA-F]{64}$

SHA256

Implementation: regex

sha512_d

^[0-9a-fA-F]{128}$

SHA512

Implementation: regex

cidr_d

^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\/([1-9]|[1-2][0-9]|3[0-2])$

CIDR

Implementation: regex

phone_number_d

^[+]{0,1}[0-9]{1,16}$

Phone Number

Implementation: regex

mime_type_d

'application/epub+zip',

'application/gzip',

'application/java-archive',

'application/json',

'application/ld+json',

'application/msword',

'application/msword',

'application/ogg',

'application/pdf',

'application/rtf',

'application/vnd.amazon.ebook',

'application/vnd.apple.installer+xml',

'application/vnd.ms-excel',

'application/vnd.ms-fontobject',

'application/vnd.ms-powerpoint',

'application/vnd.oasis.opendocument.presentation',

'application/vnd.oasis.opendocument.spreadsheet',

'application/vnd.oasis.opendocument.text',

'application/vnd.openxmlformats-officedocument.presentationml.presentation',

'application/vnd.openxmlformats-officedocument.wordprocessingml.document',

'application/vnd.rar',

'application/vnd.visio',

'application/x-abiword',

'application/x-bzip',

'application/x-bzip2',

'application/x-cdf',

'application/x-compressed',

'application/x-csh',

'application/x-freearc',

'application/x-httpd-php',

'application/x-sh',

'application/x-shockwave-flash',

'application/x-tar',

'application/x-zip-compressed',

'application/zip',

'audio/aac',

'audio/midi',

'audio/mpeg',

'audio/ogg',

'audio/opus',

'audio/wav',

'audio/webm',

'audio/x-midi',

'font/otf',

'font/ttf',

'image/avif',

'image/bmp',

'image/gif',

'image/jpeg',

'image/png',

'image/svg+xml',

'image/tiff',

'image/vnd.microsoft.icon',

'text/calendar',

'text/css',

'text/csv',

'text/html',

'text/javascript',

'text/javascript',

'text/plain',

'video/mp2t',

'video/mp4',

'video/mpeg',

'video/ogg',

'video/webm',

'video/x-msvideo'

MIME Types

Implementation: list

10.2 Application Usage Annotations

This section explains how you can use application usage annotations (hereinafter "annotations") for database objects.

For many applications, it is important to maintain additional property metadata for database objects such as tables, views, table columns, indexes, and domains. Annotations enable your applications to store and retrieve additional user-specific metadata about database objects and table columns. Applications can use such metadata to help render effective user interfaces and customize application logic.

Topics:

10.2.1 Overview of Annotations

What are annotations and where can you use them?

Annotations are a lightweight declarative facility for developers to centrally register usage properties for database schema objects. Annotations are stored in dictionary tables and are available to any application looking to standardize behavior across common data in related applications. Annotations are not interpreted by the database in any way. They are custom data properties for database metadata - included for table columns, tables, and indexes, that applications can use as additional property metadata to render user interfaces or customize application logic.

Being a mechanism to define and store application metadata centrally in the database, annotations enable you to share the metadata information across applications, modules and microservices. You can add annotations to schema objects when you create new objects (using the CREATE statements) or modify existing objects (using the ALTER statements).

Annotating the data model with metadata provides additional data integrity, consistency and data model documentation benefits. Your applications can store user-defined metadata for database objects and table columns that other applications or users can retrieve and use. Storing the metadata along with the data guarantees consistency and universal accessibility to any user or application that uses the data.

An individual annotation has a name and an optional value. The name and the optional value are freeform text fields. For example, you can have an annotation with a name and value pair, such as Display_Label ‘Employee Salary’, or you can have a standalone annotation with only a name, such as UI_Hidden, which does not need a value because the name is self-explanatory.

The following are further details about annotations.

  • When an annotation name is specified for a schema object using the CREATE DDL statement, an annotation is automatically created.

  • Annotations are additive, meaning that you can specify multiple annotations for the same schema object.

  • You can add multiple annotations at once to a schema object using a single DDL statement. Similarly, a single DDL statement can drop multiple annotations from a schema object.

  • An annotation is represented as a subordinate element to the database object to which the annotation is added. Annotations do not create a new object type inside the database.

  • You can add annotations to any schema object that supports annotations provided you own or have alter privileges on the schema object. You do not need to schema qualify the annotation name.

  • You can issue SQL queries on dictionary views to obtain all annotations, including their names and values, and their usage with schema objects.

10.2.2 Annotations and Comments

You can also annotate database objects such as tables and table columns using the COMMENT command. Comments that are associated with schema objects are stored in data dictionaries along with the metadata of the objects.

Annotations are simpler to use and have a broader scope than comments. Following are the major differences between comments and annotations:

  • Comments are a commenting mechanism used to add metadata to only certain schema objects, such as tables and columns. Comments are not available for other schema objects, such as indexes, procedures, triggers, and domains.

  • Comments do not have a name, they have only a freeform value.

  • Comments are not additive meaning that you cannot add multiple comments for the same object. Specifying a new comment overwrites the prior comment for the corresponding table or column.

  • You need separate DDL statements for comments whereas you can combine multiple annotations into one DDL statement.

  • A separate set of dictionary views exists for different entities. For instance, there is a view for table comments and another view for column comments. Annotations, on the other hand, are unified across all object types, which makes them simpler to query and use.

See Also:

Comments for more information about using COMMENTS.

10.2.3 Supported Database Objects

Annotations are supported for the following database objects.

  • Tables and table columns

  • Views and view columns

  • Materialized views and materialized view columns

  • Indexes

  • Domains and multi-column domain columns

10.2.4 Privileges Required for Using Annotations

To add or drop annotations, you require the CREATE or ALTER privilege on the schema object for which the annotation is specified in the CREATE or ALTER DDL statements.

You cannot create or remove annotations explicitly. Annotations are automatically created when they are used for the first time. Since annotations are stored as subordinate elements within the database object on which they are defined, adding annotations does not create a new object type inside the database.

10.2.5 DDL Statements for Annotations

This section explains the annotation syntax and provides the DDL statements to define or alter annotations for tables, table columns, views, materialized views, indexes, and domains.

Topics:

10.2.5.1 Annotation Syntax

The following snippet illustrates the annotation syntax that is used in DDL statements to define annotations for tables, table columns, views, materialized views, indexes, and domains.

annotations
         ::= 'ANNOTATIONS' '(' annotations_list ')'

annotations_list
         ::= ( 'ADD' ('IF NOT EXISTS' | 'OR REPLACE' )? | 'DROP' 'IF EXISTS'? | REPLACE)?
 annotation ( ',' ( 'ADD' ('IF NOT EXISTS' | 'OR REPLACE' )? | 'DROP' 'IF EXISTS'? | REPLACE)?
 annotation )*

annotation
         ::= annotation_name annotation_value?

annotation_name
         ::= identifier

annotation_value
         ::= character_string_literal

<annotation_value> is a character string literal that can hold up to 4000 characters.

<annotation_name> is an identifier and has the following requirements:

  • An identifier can have up to 1024 characters.

  • If an identifier is a reserved word, you must provide the annotation name in double quotes.

  • An identifier with double quotes can contain white characters.

  • An identifier that contains only white characters is not allowed.

10.2.5.2 DDL Statements to Annotate a Table

You can use the following DDL statements to annotate a table when creating or altering the table.

Using the CREATE TABLE Statement

The following are examples of the CREATE TABLE statement with annotations.

The following example adds an annotation: Operation with a JSON value, and another annotation: Hidden, which is standalone and has no value. 

CREATE TABLE Table1 (
  T NUMBER)
  ANNOTATIONS(Operations '["Sort", "Group"]', Hidden);

Adding an annotation can be preceded by the ADD keyword. The ADD keyword is considered to be the default operation, if nothing is specified.

The following example uses the optional ADD keyword to add the Hidden annotation (which is also standalone) to Table2. 

CREATE TABLE Table2 (
  T NUMBER)
  ANNOTATIONS (ADD Hidden);

The ADD keyword is an implicit operation when annotations are defined, and can be omitted.

Using the ALTER TABLE Statement

In the following example, the ALTER TABLE command drops all annotation values for the following annotation names: Operations and Hidden. 

ALTER TABLE Table1
  ANNOTATIONS(DROP Operations, DROP Hidden);

The following example has the ALTER TABLE command to add JoinOperations annotation with Join value, and to drop the annotation name: Hidden. When dropping an annotation, you need to only include the annotation name with the DROP command. 

ALTER TABLE Table1
  ANNOTATIONS(ADD JoinOperations 'Join', DROP Hidden);

Multiple ADD and DROP keywords can be specified in one DDL statement.

Trying to re-add an annotation with a different value for the same object (for object-level annotations) or for the same column (for column-level annotations) raises an error. For instance, the following statement fails:
ALTER TABLE Table1
  ANNOTATIONS(ADD JoinOperations 'Join Ops');

The output is:

ORA-11552: Annotation name 'JOINOPERATIONS' already exists.
As an alternative, the annotation syntax allows you to replace an annotation value using the REPLACE keyword. The following statement replaces the value of JoinOperations with 'Join Ops':
ALTER TABLE Table1
  ANNOTATIONS(REPLACE JoinOperations 'Join Ops');

The output is

Table altered.
Alternatively, to avoid an error when an annotation already exists, you can use the IF NOT EXISTS clause. The following statement adds the JoinOperations annotation only if it does not exist. If the annotation exists, the annotation value is unchanged and no error is raised.
ALTER TABLE Table1
  ANNOTATIONS(ADD IF NOT EXISTS JoinOperations 'Join Ops');
Similarly, dropping a non-existent annotation raises an error:
ALTER TABLE Table1 ANNOTATIONS(DROP Title);

The output is:

ORA-11553: Annotation name 'TITLE' does not exist.
To avoid the error, the IF EXISTS clause can be used, as follows:
ALTER TABLE Table1
  ANNOTATIONS(DROP IF EXISTS Title);

The output is:

Table altered.

See Also:

CREATE TABLE and ALTER TABLE in SQL Language Reference for complete clause changes and definitions.

10.2.5.3 DDL Statements to Annotate a Table Column

You can use the following DDL statements to annotate a table column when creating or altering the table.

Using the CREATE TABLE Statement

To add column-level annotations using a CREATE TABLE statement, specify the annotations as a part of the column_definition clause. Annotations are specified at the end, after inline constraints.

The following examples specify annotations for columns at table creation.

CREATE TABLE Table1
  (T NUMBER ANNOTATIONS(Operations 'Sort', Hidden));

CREATE TABLE Table2
  (T NUMBER ANNOTATIONS (Hidden));

The following example specifies table-level and column-level annotations for the Employee table. 

CREATE TABLE Employee (
  Id NUMBER(5) ANNOTATIONS(Identity, Display 'Employee ID', "Group" 'Emp_Info'),
  Ename VARCHAR2(50) ANNOTATIONS(Display 'Employee Name', "Group" 'Emp_Info'),
  Sal NUMBER ANNOTATIONS(Display 'Employee Salary', UI_Hidden)
)
 ANNOTATIONS (Display 'Employee Table');

The Employee table in the previous example has column-level and object-level annotations with Display annotations defined at the column level and object level. You can define a new annotation with the same name as long as it corresponds to a different object, column pair. For instance, you cannot define another annotation with the Display name for the Employee table but you can define a new annotation "Group" for the Sal column. The annotation "Group" needs to be double quoted because it is a reserved word.

Using the ALTER TABLE Statement

To add column-level annotations using an ALTER TABLE statement, specify the annotations as a part of the modify_col_properties clause. Annotations are specified at the end, after inline constraints.

The following example adds a new Identity annotation for the column T of Table1 table. 

ALTER TABLE Table1
  MODIFY T ANNOTATIONS(Identity 'ID');

The following example adds a Label annotation and drops the Identity annotation. 

ALTER TABLE Table1
  MODIFY T ANNOTATIONS(ADD Label, DROP Identity);

See Also:

CREATE TABLE and ALTER TABLE in SQL Language Reference for complete clause changes and definitions.

10.2.5.4 DDL Statements to Annotate Views and Materialized Views

You can use the following DDL statements to annotate a view and a materialized view.

Views and materialized views support annotations at the view level and column level. Column-level annotations are only supported for table views as a part of the column alias definition clause.

Using the CREATE VIEW Statement

The following example shows the view-level and column-level annotations:

CREATE OR REPLACE VIEW HighWageEmp  
(
  Id ANNOTATIONS(Identity, Display 'Employee ID', "Group" 'Emp_Info'),
  Ename ANNOTATIONS (Display 'Employee Name',  "Group" 'Emp_Info'),
  Sal  ANNOTATIONS (Display 'Emp Salary')  
)
ANNOTATIONS (Title 'High Wage Employee View')
AS SELECT * FROM EMPLOYEE WHERE Sal > 100000;

Using the CREATE MATERIALIZED VIEW Statement

The following example adds annotation at the view level in the Materialized View statement: 

CREATE MATERIALIZED VIEW MView1
  ANNOTATIONS (Title 'Tab1 MV1', ADD Snapshot)
  AS SELECT * FROM Table1;

The following example adds annotation at the view level and column level in the Materialized View statement: 

CREATE MATERIALIZED VIEW MView1(
  T ANNOTATIONS (Hidden)) 
 ANNOTATIONS (Title 'Tab1 MV1', ADD Snapshot)
 AS SELECT * FROM Table1;

Using the ALTER VIEW Statement

To provide support for annotations, the ALTER VIEW statement has a new sub-clause added to it that allows altering annotations at the view level. Other sub-clauses that are supported in the ALTER VIEW statement are: modifying view constraints, enabling recompilation, and changing EDITIONABLE property.

Column-level annotations for views cannot be altered. Previously added column-level annotations can be dropped only by dropping the view and recreating a new one.

The following example is an ALTER VIEW statement that drops the Title annotation and adds the Identity annotation at the view level. 

ALTER VIEW HighWageEmp
  ANNOTATIONS(DROP Title, ADD Identity);

Using the ALTER MATERIALIZED VIEW Statement

The ALTER MATERIALIZED VIEW statement has a new sub-clause that is added to alter annotations globally at the materialized view level. Column-level annotations can be dropped only by dropping and recreating the materialized view.

The following ALTER MATERIALIZED VIEW statement drops Snapshot annotation from MView1. 

ALTER MATERIALIZED VIEW MView1
  ANNOTATIONS(DROP Snapshot);

See Also:

CREATE VIEW, ALTER VIEW, CREATE MATERIALIZED VIEW, and ALTER MATERIALIZED VIEW in SQL Language Reference for complete clause changes and definitions.

10.2.5.5 DDL Statements to Annotate Indexes

You can use the following DDL statements to annotate an index.

Using the CREATE INDEX Statement

The following are examples of creating index-level annotations.

CREATE TABLE DEPARTMENT(
  DEPT_ID NUMBER,
  UNIT NUMBER);

CREATE INDEX I_DEPT_ID ON DEPARTMENT(DEPT_ID)
  ANNOTATIONS(Display 'Department Index');

CREATE UNIQUE INDEX UI_UNIT ON DEPARTMENT(UNIT)
  ANNOTATIONS(Display 'Department Unique Index');

Using the ALTER INDEX Statement

The following is an example of altering an index-level annotation.

ALTER INDEX I_DEPT_ID
  ANNOTATIONS(DROP Display, ADD ColumnGroup 'Group1');

See Also:

CREATE INDEX and ALTER INDEX in SQL Language Reference for complete clause changes and definitions.

10.2.5.6 DDL Statements to Annotate Domains

You can use the following DDL statements to annotate a domain.

You can specify annotations for domains at the domain level or at the column level. Annotations defined on domains are inherited to objects that reference the domain. Annotations are allowed only for regular domains and not for flexible domains.

To specify domain-level annotations for single-column domains, the syntax with parentheses (used for multi-column domains) is required to distinguish between column-level and object-level annotations. Otherwise, the annotations for single column domains are considered column level.

Using the CREATE DOMAIN Statement

The following example creates domain annotations by specifying column-level annotations for a single-column domain.

CREATE DOMAIN dept_codes_1 AS NUMBER(3) 
  CONSTRAINT dept_chk_1 CHECK (dept_codes_1 > 99 AND dept_codes_1 != 200) 
  ANNOTATIONS (Title 'Column level annotation');

The following examples specify a domain-level annotation for a single-column domain. This requires the use of the domains syntax for multi-column domains (with parentheses for columns).

CREATE DOMAIN dept_codes_2 AS (
  code AS NUMBER(3) 
  CONSTRAINT dept_chk_2 CHECK (code > 99 AND code != 200)) 
  ANNOTATIONS (Title 'Domain Annotation');

CREATE DOMAIN HourlyWages AS Number(10) 
  DEFAULT ON NULL 15
  CONSTRAINT MinimalWage CHECK (HourlyWages > = 7 and HourlyWages <=1000) ENABLE
  DISPLAY TO_CHAR(HourlyWages, '$999.99')
  ORDER ( -1*HourlyWages )
  ANNOTATIONS (Title 'Column level annotation');

The following example creates a multi-column domain with annotations at the column and domain levels.

CREATE DOMAIN US_City AS (
    name  AS VARCHAR2(50) ANNOTATIONS (Address),
    state AS VARCHAR2(50) ANNOTATIONS (Address),
    zip AS NUMBER ANNOTATIONS (Address)
  )
  CONSTRAINT City_CK CHECK(state in ('CA','AZ','TX') and zip < 100000) 
  DISPLAY name || ', ' || state || ' , ' || TO_CHAR(zip)
  ORDER state || ', ' || TO_CHAR(zip) || ', ' || name
  ANNOTATIONS (Title 'Domain Annotation');

Using the ALTER DOMAIN Statement

Object-level annotations can be modified with ALTER statements. Column-level annotations for domains cannot be altered. Previously added column-level annotations can be dropped only by dropping the domain and recreating a new one.

The following example alters domain-level annotation for the dept_codes_2 domain. 

ALTER DOMAIN dept_codes_2
  ANNOTATIONS(DROP Title, ADD Name 'Domain');

See Also:

CREATE DOMAIN and ALTER DOMAIN in SQL Language Reference for complete clause changes and definitions.

10.2.5.7 Dictionary Table and Views

A dictionary table called ANNOTATIONS_USAGE$ includes all the usage of annotations with schema objects, such as tables and views. When a new annotation name, value, or both are specified to a schema object, a new entry in ANNOTATIONS_USAGE$ table is created. Similarly, when a schema object drops an annotation, the corresponding entry is dropped from the ANNOTATIONS_USAGE$ table.

The following dictionary views track the list of annotations and their usage across all schema objects:

  • {DBA|USER|ALL|CDB}_ANNOTATIONS

  • {DBA|USER|ALL|CDB}_ANNOTATIONS_USAGE

  • {DBA|USER|ALL|CDB}_ANNOTATIONS_VALUES

10.2.5.7.1 Querying Dictionary Views

You can run queries on dictionary views to obtain the list of annotations that are used for specific schema objects.

Here are examples of query statements issued on an 'EMP' table:

To obtain table-level annotations for the ‘EMP’ table:
SELECT * FROM USER_ANNOTATIONS_USAGE
  WHERE Object_Name = 'EMPLOYEE'
   AND Object_Type = 'TABLE'
   AND Column_Name IS NULL;
To obtain column-level annotations for the ‘EMP’ table:
SELECT * FROM USER_ANNOTATIONS_USAGE
  WHERE Object_Name = 'EMPLOYEE'
   AND Object_Type = 'TABLE'
   AND Column_Name IS NOT NULL;
To obtain column-level annotations for the ‘EMP’ table as a single JSON collection per column:
SELECT  U.Column_Name, JSON_ARRAYAGG(JSON_OBJECT(U.Annotation_Name, U.Annotation_Value))
  FROM USER_ANNOTATIONS_USAGE U
  WHERE Object_Name = 'EMPLOYEE' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL
  GROUP BY Column_Name;