8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Domains in Oracle Database 23c
This article demonstrates the use of Domains in Oracle database 23c.
- Why Domains?
- Setup
- Domain Types
- Create Domain
- Domain Functions
- Alter Domain
- Drop Domain
- Evolve a Domain
- PL/SQL Support
- Views
- Strict Domains
- Considerations
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.
- Single Column Domain
- Multi Column Domain
- Flexible Domain
These are made up of several domain-specific expressions and conditions.
- Simple Domain Expression : This can be a string, number, sequence.CURRVAL, sequence.NEXTVAL, NULL, or schema.domain.
- Datetime Domain Expression : This is a normal datetime expression, but it only references other domain expressions.
- Interval Domain Expression : This is a normal interval expression, but it only references other domain expressions.
- Compound Domain Expression : This is just a more complicated domain expression, typically with additional operations.
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.
- The lack of ability to alter the table column definition easily is a concern as your schema evolves over time.
- The lack of PL/SQL support means the domain is not really a single point of definition.
- It feels like unfinished functionality.
- There is a lot of different functionality, but I don't really see myself using most of it. Perhaps my perspective will change over time as the functionality matures and it becomes available from PL/SQL.
- The documentation needs more real-world use cases, with a full implementation showing domain evolution, rather than just a standalone domain creation.
For more information see:
Hope this helps. Regards Tim...