8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

Domains in Oracle Database 23c

This article demonstrates the use of Domains in Oracle database 23c.

Why Domains?

Domains are a way of promoting Single Point Of Definition (SPOD), giving us consistency throughout an application. We define the properties and constraints associated with a column once, and use that definition across the whole of our application.

You may see domains described as "application usage domains". This simply emphasizes that domains are meant to help the in the development of your application. In some of the earlier resources, domains were described as SQL domains, as they are part of the SQL standard.

Setup

We connect to a privileged user and create a new test user. Notice we grant CREATE DOMAIN to the test user. We then connect to this test user to run the examples.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;
grant create domain to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

Domain Types

There are three basic domain types.

These are made up of several domain-specific expressions and conditions.

The syntax can be quite complicated, depending on the type of domain, so we are not going to try and cover it all in this article. You can get the full details in the CREATE DOMAIN documentation.

Create Domain

Most of the focus will be on single column domains, but there are examples of multi-column and flexible domains.

Single Column Domain

Let's assume we want to make the definition of email addresses consistent throughout our application. We create a single column domain called EMAIL_DOM, which specifies the data type of the domain, and adds a check constraint to make sure the contents follow a basic email pattern.

drop domain if exists email_dom;

create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

We could add DISPLAY and ORDER references, to explain how we want the data to be displayed and ordered by default, and also add annotations.

drop domain if exists email_dom;

create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'))
display lower(email_dom)
order   lower(email_dom)
annotations (Description 'Domain for Emails');

If we were using extended data types (max_string_size=extended) we could add collation for case insensitive searches.

drop domain if exists email_dom;

create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'))
collate binary_ci;

We'll use this domain going forward.

drop domain if exists email_dom;

create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'))
display lower(email_dom)
order   lower(email_dom)
annotations (Description 'Domain for Emails');

We can now reference that domain in a table column definition in one of several ways.

-- Domain reference.
drop table if exists t1 purge;

create table t1 (
  id     number,
  email  email_dom
);


-- Domain reference with domain keyword.
drop table if exists t1 purge;

create table t1 (
  id     number,
  email  domain email_dom
);


-- Data type and domain reference.
drop table if exists t1 purge;

create table t1 (
  id     number,
  email  varchar2(100) email_dom
);


-- Data type and domain reference with domain keyword.
drop table if exists t1 purge;

create table t1 (
  id     number,
  email  varchar2(100) domain email_dom
);

When we describe the table we see the data type definition, along with the referenced domain.

SQL> desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------------
 ID                                                 NUMBER
 EMAIL                                              VARCHAR2(100) TESTUSER1.EMAIL_DOM

SQL>

We can insert data into to the column provided it doesn't violate the associated check constraint.

-- Insert correctly.
insert into t1 values (1, 'Banana@fruit.com');
insert into t1 values (2, 'apple@fruit.com');

-- Doesn't pass check constraint.
insert into t1 values (3, 'banana');
*
ERROR at line 1:
ORA-11534: check constraint (TESTUSER1.SYS_C0012510) due to domain constraint TESTUSER1.EMAIL_CHK of
domain TESTUSER1.EMAIL_DOM violated

SQL>

Multi Column Domain

We create a multi-column domain to represent an address.

drop domain if exists address_dom;

create domain address_dom as (
  address_line_1  as varchar2(50),
  address_line_2  as varchar2(50),
  city            as varchar2(50),
  country_code    as varchar2(5),
  postcode        as varchar2(10)
)
constraint address_chk check (address_line_1 is not null and
                              city is not null and
                              country_code is not null and
                              postcode is not null)
display address_line_1||','||address_line_2||','||city||','||country_code||','||postcode;

We create a table called addresses and reference the columns to associate them with the domain. In this case we can't use the domain to define the column data types and precisions directly, so we must include column specifications.

drop table if exists addresses purge;

create table addresses (
  id              number,
  address_line_1  varchar2(50),
  address_line_2  varchar2(50),
  city            varchar2(50),
  country_code    varchar2(5),
  postcode        varchar2(10),  
  domain address_dom(address_line_1, address_line_2, city, country_code, postcode)
);

We describe the table, and we can see the domain is associated with the columns.

SQL> desc addresses
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 ADDRESS_LINE_1                                     VARCHAR2(100) TESTUSER1.ADDRESS_DOM
 ADDRESS_LINE_2                                     VARCHAR2(50) TESTUSER1.ADDRESS_DOM
 CITY                                               VARCHAR2(50) TESTUSER1.ADDRESS_DOM
 COUNTRY_CODE                                       VARCHAR2(5) TESTUSER1.ADDRESS_DOM
 POSTCODE                                           VARCHAR2(10) TESTUSER1.ADDRESS_DOM

SQL>

Let's clean up those objects.

drop table if exists addresses purge;
drop domain if exists address_dom;

Flexible Domain

A flexible domain allows us to apply one of several domains depending on the context of the data. We create three address domains representing UK, US and "other" addresses. The UK address requires a postcode of 6 to 8 characters. The US address requires a postcode/zipcode of 5 or 9 characters. The default address doesn't check the length of the postcode. These length checks are added into the associated check constraints in the domains.

-- UK address.
create domain address_uk_dom as (
  address_line_1  as varchar2(50),
  address_line_2  as varchar2(50),
  city            as varchar2(50),
  postcode        as varchar2(10)
)
constraint address_uk_chk check (address_line_1 is not null and
                                 city is not null and
                                 postcode is not null and
                                 length(postcode) between 6 and 8);


-- US address.
create domain address_us_dom as (
  address_line_1  as varchar2(50),
  address_line_2  as varchar2(50),
  city            as varchar2(50),
  postcode        as varchar2(10)
)
constraint address_us_chk check (address_line_1 is not null and
                                 city is not null and
                                 postcode is not null and
                                 (length(postcode) = 5 or length(postcode) = 9));

-- Default address.
create domain address_dom as (
  address_line_1  as varchar2(50),
  address_line_2  as varchar2(50),
  city            as varchar2(50),
  postcode        as varchar2(10)
)
constraint address_chk check (address_line_1 is not null and
                              city is not null and
                              postcode is not null);

We create a flexible domain which applies these domains based on the context of the row. We have to nominate a column to base the decision on. That is known as the discriminant column. We can then decide which domain to apply using a DECODE or CASE expression. In this example we use a CASE expression.

create flexible domain address_flex_dom (address_line_1, address_line_2, city, postcode)
choose domain using (country_code varchar2(5))
from case
       when country_code in ('GB','GBR') then address_uk_dom(address_line_1, address_line_2, city, postcode)
       when country_code in ('US','USA') then address_us_dom(address_line_1, address_line_2, city, postcode)
       else address_dom(address_line_1, address_line_2, city, postcode)
     end;

We recreate the addresses table using the flexible domain. We specify the discriminant column with the USING keyword.

create table addresses (
  id              number,
  address_line_1  varchar2(50),
  address_line_2  varchar2(50),
  city            varchar2(50),
  country_code    varchar2(5),
  postcode        varchar2(10),  
  domain address_flex_dom(address_line_1, address_line_2, city, postcode) using (country_code)
);

We can now test the flexible domain by inserting some data. The following rows are inserted correctly, as they all obey the domain associated with their country code.

insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (1, '1 my street', null, 'birmingham', 'GB', 'A12 BCD');

1 row created.

SQL>

insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (2, '2 my street', null, 'boston', 'US', '12345');

1 row created.

SQL>

insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (3, '3 my street', null, 'dublin', 'IRE', '1234567890');

1 row created.

SQL>

In these inserts we try to use a US zipcode in a UK address, and a UK postcode in a US address. As expected, both fail, but notice they fail for different domains and check constraints.

insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (4, '4 my street', null, 'birmingham', 'GB', '12345');
*
ERROR at line 1:
ORA-11534: check constraint (TESTUSER1.SYS_C0012512) due to domain constraint
TESTUSER1.SYS_DOMAIN_C0063 of domain TESTUSER1.ADDRESS_FLEX_DOM violated

SQL>


insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (5, '5 my street', null, 'boston', 'US', 'A12 BCD');
*
ERROR at line 1:
ORA-11534: check constraint (TESTUSER1.SYS_C0012513) due to domain constraint
TESTUSER1.SYS_DOMAIN_C0062 of domain TESTUSER1.ADDRESS_FLEX_DOM violated

SQL>

Let's clean up those objects.

drop table if exists addresses purge;
drop domain if exists address_flex_dom;
drop domain if exists address_dom;
drop domain if exists address_uk_dom;
drop domain if exists address_us_dom;

Domain Functions

Some domain functions are available to allow us to make use of the domain features. If we query the data we can see it is displayed and ordered in the normal way. The difference in case is shown, and the ASCII ordering puts "B" ahead of "a".

select email from t1 order by email;

EMAIL
--------------------------------------------------------------------------------
Banana@fruit.com
apple@fruit.com

SQL>

The DOMAIN_DISPLAY function allows us to use the DISPLAY characteristics. The data is now displayed in lower case, as defined in the domain, but notice this doesn't affect the ordering if we reference the column by name. It would if we used the column position or an alias in the ORDER BY clause.

select domain_display(email) from t1 order by email;

DOMAIN_DISPLAY(EMAIL)
--------------------------------------------------------------------------------
banana@fruit.com
apple@fruit.com

SQL>

The DOMAIN_ORDER function allows us to use the ORDER characteristics. Notice the data is presented in mixed case, but the ordering is based on lower case comparisons.

select email from t1 order by domain_order(email);

EMAIL
--------------------------------------------------------------------------------
apple@fruit.com
Banana@fruit.com

SQL>

The DOMAIN_NAME function allows us to return the domain associated with a column.

select domain_name(email) from t1 where rownum = 1;

DOMAIN_NAME(EMAIL)
----------------------------------------
TESTUSER1.EMAIL_DOM

SQL>

The domain can be used as part of a cast operation.

select cast('grape@fruit.com' as domain email_dom);

CAST('GRAPE@FRUIT.COM'ASDOMAINEMAIL_DOM)
----------------------------------------------------------------------------------------------------
grape@fruit.com

SQL>

The DOMAIN_CHECK function allows us to check some data against the domain check constraint. It doesn't seem to work with a string as a parameter, so instead we need to cast the data to a suitable type for the check. Thanks to Chris Saxon for pointing this out.

-- Using strings.
select domain_check('email_dom', 'grape@fruit.com');

DOMAIN_CHECK('EMAIL_DOM','GRAPE@FRUIT.COM')
-------------------------------------------
                                          0

SQL>


select domain_check('email_dom', 'grape');

DOMAIN_CHECK('EMAIL_DOM','GRAPE')
---------------------------------
                                0

SQL>


-- Using cast.
select domain_check('email_dom', cast('grape@fruit.com' as varchar2(100)));

DOMAIN_CHECK('EMAIL_DOM',CAST('GRAPE@FRUIT.COM'ASVARCHAR2(100)))
----------------------------------------------------------------
                                                               1

SQL>


select domain_check('email_dom', cast('grape' as varchar2(100)));

DOMAIN_CHECK('EMAIL_DOM',CAST('GRAPE'ASVARCHAR2(100)))
------------------------------------------------------
                                                     0

SQL>

Alter Domain

At the time of writing, the data type definition and check constraint can't be altered for domains. That could represent a problem for those domains that are used as table columns, as extension of a column size is not possible.

The display, order and annotation attributes can be altered.

-- Display
alter domain email_dom
add display lower(email_dom);

alter domain email_dom
modify display upper(email_dom);

alter domain email_dom
drop display;


-- Order
alter domain email_dom
add order lower(email_dom);

alter domain email_dom
modify order upper(email_dom);

alter domain email_dom
drop order;


-- Annotation
alter domain email_dom
annotations (Usage 'Use this for email columns.');

alter domain email_dom
annotations (drop Usage);

Drop Domain

Our domain has dependencies, so attempting to drop it results in an error.

drop domain email_dom;
*
ERROR at line 1:
ORA-11502: The domain to be dropped has dependent objects.

SQL>

We can use the FORCE keyword to dissociate the domain from the column, and drop the domain, but we lose the constraints on the dependent columns. We check the constraints on the table.

column constraint_name format a20
column search_condition format a50

select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'T1';

CONSTRAINT_NAME      C SEARCH_CONDITION
-------------------- - --------------------------------------------------
SYS_C0012349         C  REGEXP_LIKE ("EMAIL",'^(\S+)\@(\S+)\.(\S+)$')

SQL>

We drop the domain with the FORCE option, and we can see the constraint has gone, and the domain is no longer shown when the table is described..

drop domain email_dom force;

Domain dropped.

SQL>


select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'T1';

no rows selected

SQL>


desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 EMAIL                                              VARCHAR2(100)

SQL>

The FORCE PRESERVE option preserves the defaults and constraints on the dependent table columns when it drops the domain.

-- Recreate the test domain and table.
drop table t1 purge;

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
);


-- Check the constraints.
select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'T1';

CONSTRAINT_NAME      C SEARCH_CONDITION
-------------------- - --------------------------------------------------
SYS_C0012351         C  REGEXP_LIKE ("EMAIL",'^(\S+)\@(\S+)\.(\S+)$')

SQL>


-- Drop the domain with FORCE PRESERVE option.
drop domain email_dom force preserve;


-- Check the constraints.
select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'T1';

CONSTRAINT_NAME      C SEARCH_CONDITION
-------------------- - --------------------------------------------------
SYS_C0012351         C  REGEXP_LIKE ("EMAIL",'^(\S+)\@(\S+)\.(\S+)$')

SQL>

Evolve a Domain

Our column definitions may change over the lifetime of our application. Let's see how to manage the evolution of the domain.

First we recreate the domain and table to represent our starting point.

drop table if exists t1 purge;
drop domain if exists email_dom;

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
);

Let's assume our requirement changes and we need to extend the string length to 200. We use the USER_TAB_COLUMNS view to list the table columns associated with the domain.

column table_name format a30
column column_name format a30

select table_name, column_name
from   user_tab_columns
where  domain_name = 'EMAIL_DOM';

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

SQL>

We drop the domain with the FORCE PRESERVE option.

drop domain email_dom force preserve;

We recreate the domain, extending the column size to 200.

create domain email_dom as varchar2(200)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

If we try to re-apply the domain, hoping this would extend the column, we will be dissapointed.

alter table t1 modify email domain email_dom;
                        *
ERROR at line 1:
ORA-11517: the column data type does not match the domain column

SQL>

To reassociate the domain, we have to make the base column definition match the domain, then apply the domain. We need to repeat this for any column that was originally referencing the domain.

alter table t1 modify email varchar2(200);
alter table t1 modify email domain email_dom;

desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------------
 ID                                                 NUMBER
 EMAIL                                              VARCHAR2(200) TESTUSER1.EMAIL_DOM

SQL>

Views

We see both the domain and the table are listed in the USER_OBJECTS view.

column object_name format A20
column object_type format A20

select object_name, object_type
from   user_objects
order by 1;

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
EMAIL_DOM            DOMAIN
T1                   TABLE

SQL>

The DOMAIN_OWNER and DOMAIN_NAME column have been added to the USER_TAB_COLUMNS view.

column column_name format A20
column domain_owner format A20
column domain_name format A20

select column_id,
       column_name,
       domain_owner,
       domain_name
from   user_tab_columns
where  table_name = 'T1'
order by 1;

 COLUMN_ID COLUMN_NAME          DOMAIN_OWNER         DOMAIN_NAME
---------- -------------------- -------------------- --------------------
         1 ID
         2 EMAIL                TESTUSER1            EMAIL_DOM

SQL>

The USER_DOMAINS, USER_DOMAIN_COLS and USER_DOMAIN_CONSTRAINTS views give information about the domain definition.

SQL> desc user_domains
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(128)
 NAME                                                  NOT NULL VARCHAR2(128)
 COLS                                                  NOT NULL NUMBER
 DISCRIMINANTCOLS                                               NUMBER
 FLAGS                                                          NUMBER
 BUILTIN                                                        VARCHAR2(3)
 DATA_DISPLAY                                                   CLOB
 DISPLAY_LENGTH                                                 NUMBER
 DATA_ORDER                                                     CLOB
 ORDER_LENGTH                                                   NUMBER
 SELECTOR                                                       CLOB
 SELECTOR_LENGTH                                                NUMBER

SQL>


SQL> desc user_domain_cols;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(128)
 DOMAIN_NAME                                           NOT NULL VARCHAR2(128)
 COLUMN_NAME                                           NOT NULL VARCHAR2(128)
 COLUMN_ID                                             NOT NULL NUMBER
 DATA_TYPE_ID                                          NOT NULL NUMBER
 DATA_TYPE                                                      VARCHAR2(106)
 DATA_LENGTH                                                    NUMBER
 DATA_PRECISION                                                 NUMBER
 DATA_SCALE                                                     NUMBER
 NULLABLE                                                       VARCHAR2(1)
 DATA_DEFAULT                                                   CLOB
 DEFAULT_LENGTH                                                 NUMBER
 DEFAULT_ON_NULL                                                VARCHAR2(3)
 DEFAULT_ON_NULL_UPD                                            VARCHAR2(3)
 CHARACTER_SET_NAME                                             VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                                           NUMBER
 COLLATION                                                      VARCHAR2(100)
 EXACT                                                          VARCHAR2(3)
 CHAR_LENGTH                                                    NUMBER
 DISCRIMINANT                                                   VARCHAR2(3)

SQL>


SQL> desc user_domain_constraints
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NAME                                                  NOT NULL VARCHAR2(128)
 DOMAIN_OWNER                                          NOT NULL VARCHAR2(128)
 DOMAIN_NAME                                           NOT NULL VARCHAR2(128)
 CONSTRAINT_TYPE                                                VARCHAR2(1)
 SEARCH_CONDITION                                               CLOB
 STATUS                                                         VARCHAR2(8)
 DEFERRABLE                                                     VARCHAR2(14)
 DEFERRED                                                       VARCHAR2(9)
 VALIDATED                                                      VARCHAR2(13)
 GENERATED                                                      VARCHAR2(14)
 BAD                                                            VARCHAR2(3)
 RELY                                                           VARCHAR2(4)
 INVALID                                                        VARCHAR2(7)
 ORIGIN_CON_ID                                                  NUMBER

SQL>

PL/SQL Support

At the time of writing there doesn't appear to be any support for domains built in PL/SQL.

Recreate the domain and test table.

drop table t1 purge;
drop domain email_dom;

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
);

We can't define a variable based on a domain directly.

declare
  l_email domain email_dom;
begin
  null;
end;
/
  l_email domain email_dom;
                 *
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00103: Encountered the symbol "EMAIL_DOM" when expecting one of the following:
:= . ( @ % ; not null range default character
The symbol ":=" was substituted for "EMAIL_DOM" to continue.


SQL>

We declare a variable based on the column type, but we can see the constraint is not checked during a variable assignment.

declare
  l_email t1.email%type;
begin
  l_email := 'banana@fruit.com';
  l_email := 'banana';
end;
/

PL/SQL procedure successfully completed.

SQL>

Maybe this functionality will be included in a later release.

Strict Domains

In the previous examples we didn't include the STRICT keyword in our domain definitions. As a result the table column can have a difference column precision to the domain definition.

In this example we create table with three columns of different sizes. We attempt to apply the domain to each of the columns. Providing the size of the column is greater than or equal to the domain definition the domain association works. If the column is smaller than the domain definition, the domain can't be applied to it.

drop table if exists t1 purge;
drop domain if exists email_dom;

create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));

create table t1 (
  col1 varchar2(50),
  col2 varchar2(100),
  col3 varchar2(200)
);


alter table t1 modify col1 domain email_dom;
                        *
ERROR at line 1:
ORA-11517: the column data type does not match the domain column

SQL>


alter table t1 modify col2 domain email_dom;

Table altered.

SQL>


alter table t1 modify col3 domain email_dom;

Table altered.

SQL>

This time we repeat the previous example, but make the column definition strict. Now the domain can only be associated with a column that has the same precision.

drop table if exists t1 purge;
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+)$'));

create table t1 (
  col1 varchar2(50),
  col2 varchar2(100),
  col3 varchar2(200)
);


alter table t1 modify col1 domain email_dom;
                        *
ERROR at line 1:
ORA-11517: the column data type does not match the domain column

SQL>


alter table t1 modify col2 domain email_dom;

Table altered.

SQL>


alter table t1 modify col3 domain email_dom;
                        *
ERROR at line 1:
ORA-11517: the column data type does not match the domain column

SQL>

Considerations

Here are some thoughts about this functionality.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.