One of the top new features of Oracle Database 23c is Domains – or SQL Domains or Application Domains or Usage Domains.
In short what ever the name – the emphasis is that domains are meant to help the in the development of applications – ensuring data quality and data consistency.
A schema may have many tables with columns that hold email addresses, such as billing emails, invoice emails, and customer contact emails.
The challenge was how to ensure consistency of such columns across the entire application.
Let us look at some of the main points related to an Oracle 23 Database Domain.
- 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.
- With SQL domains, we can define centrally how we intend to use data. They make it easier to ensure values are handled consistently across applications and thereby improve data quality.
- Domains enable us to enforce a common set of rules – like a credit card should have 12 numbers – or start with a 3 – and there should be no dashes or special characters.
- SQL domain is a data dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints for common values, such as credit card numbers, registration plates or email addresses.
- SQL Domain can be used in place of a data type – or use a domain along with data type to enforce other rules like sort order or case sensitivity.
- There are 3 different types of domains: single column, multi column and flexible domains. In addition there are also built-in domains available for use.
Note the LAST_UPDATE_DATE column in an Oracle 19c database. Even though the column is updated by the value of SYSDATE, because the data type is different in each of the 3 tables (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE) the column values are different.
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 23 23:14:42 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Oct 17 2023 21:18:33 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table mydate_1
2 (last_update_date date);
Table created.
SQL> create table mydate_2
2 (last_update_date timestamp);
Table created.
SQL> create table mydate_3
(last_update_date TIMESTAMP WITH TIME ZONE);
Table created.
SQL> insert into mydate_1
2 values
3 (sysdate);
1 row created.
SQL> insert into mydate_2
2 values
3 (sysdate);
1 row created.
SQL> insert into mydate_3
2 values
3 (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mydate_1;
LAST_UPDATE_DATE
-----------------
23-OCT-23
SQL> select * from mydate_2;
LAST_UPDATE_DATE
---------------------------------------------------------------------------
23-OCT-23 11.24.34.000000 PM
SQL> select * from mydate_3;
LAST_UPDATE_DATE
---------------------------------------------------------------------------
23-OCT-23 11.24.44.000000 PM -04:00
Let us see an example of how in Oracle Database 23c, a Domain can be created and then assigned to table columns- applications and developers can know these store the same values. This makes it easier to ensure they’re handled consistently across tables & applications.
Create the INSERT_TIMESTAMP domain and assign the domain to the LAST_UPDATE_DATE column.
Note: we do not assign a data type to the column – but assign the domain!
The LAST_UPDATE_DATE column inherits both the NOT NULL constraint as well as the TIMESTAMP data type which was assigned to the domain.
[oracle@db23c ~]$ sqlplus demo/Oracle_4U@freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Wed Oct 25 23:22:16 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Wed Oct 25 2023 23:21:36 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> create domain insert_timestamp as timestamp default systimestamp not null ; Domain created. SQL> create table mydate_1 (last_update_date domain insert_timestamp); Table created. SQL> create table mydate_2 (last_update_date domain insert_timestamp); Table created. SQL> create table mydate_3 (last_update_date domain insert_timestamp); Table created. SQL> desc mydate_1 Name Null? Type ----------------------------------------- -------- ---------------------------- LAST_UPDATE_DATE NOT NULL TIMESTAMP(6) DEMO.INSERT_TIM ESTAMP
Rows are inserted into the 3 different tables with the LAST_UPDATE_DATE column.
Note that the data in the column using the domain is consistent in all 3 tables.
[oracle@db23c ~]$ sqlplus demo/Oracle_4U@freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Wed Oct 25 23:26:49 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Wed Oct 25 2023 23:22:16 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> insert into mydate_1 2 values 3 (sysdate); 1 row created. SQL> insert into mydate_2 2 values 3 (sysdate); 1 row created. SQL> insert into mydate_3 2 values 3 (sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from mydate_1; LAST_UPDATE_DATE --------------------------------------------------------------------------- 25-OCT-23 11.30.45.000000 PM SQL> select * from mydate_2 ; LAST_UPDATE_DATE --------------------------------------------------------------------------- 25-OCT-23 11.31.43.000000 PM SQL> select * from mydate_3 ; LAST_UPDATE_DATE --------------------------------------------------------------------------- 25-OCT-23 11.32.08.000000 PM
Note some of the data dictionary views which can be used to get more information about domains used in the database.
SQL> select domain_name(last_update_date) from mydate_1 where rownum =1; DOMAIN_NAME(LAST_UPDATE_DATE) ---------------------------------------- DEMO.INSERT_TIMESTAMP SQL> col name format a20 SQL> set long 50000 SQL> select name, SEARCH_CONDITION from all_domain_constraints where domain_name='INSERT_TIMESTAMP'; NAME -------------------- SEARCH_CONDITION -------------------------------------------------------------------------------- SYS_DOMAIN_C0023 "INSERT_TIMESTAMP" IS NOT NULL SQL> select object_name from user_objects where object_type='DOMAIN'; OBJECT_NAME -------------------------------------------------------------------------------- INSERT_TIMESTAMP SQL> select column_name, domain_owner, domain_name from user_tab_columns where table_name = 'MYDATE_1'; COLUMN_NAME DOMAIN_OWNER DOMAIN_NAME -------------------- -------------------- -------------------- LAST_UPDATE_DATE DEMO INSERT_TIMESTAMP SQL> select name,cols,builtin from user_domains; NAME COLS BUILTIN ---------------------------------------- ---------- ----------- INSERT_TIMESTAMP 1 FALSE
