When I reviewed the new Oracle Database 23 features, I also came across the new feature Domain (aka Data Use Case Domains). Let’s start with a short definition: A domain is a dictionary object belonging to a schema that encapsulates a set of optional properties and constraints for common values. Domains provide constraints, display, ordering, and annotation attributes. After you define a domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain’s optional properties and constraints to those columns. So domains provide additional information to a stored column (JSON or relational) and therefore, are used to define and validate data.
Note: Oracle’s domain implementation provides a wider range of functions than the one specified in the SQL standard (see Part 2 Foundation (SQL Foundation)).
You can create, alter, and drop a domain in your schema. To work with domains, you require the privilege CREATE DOMAIN.
Please keep in mind: The RESOURCE and the new developer role DB_DEVELOPER_ROLE roles already include the CREATE DOMAIN privilege.
There are 3 different types of domains: single-column, multi-column, and flexible domains. In addition, there are also built-in domains that you may use.
A lot of information about syntax, data dictionary views, new domain functions, and many examples can be found in the following documentation:
(These two references were “my best friends” when getting to know the new functionality. :))
To give you an easy start, I put together a few examples around a table PERSON covering the following topics:
Create and drop a single column domain
To create a Domain, have a look at SQL Language Reference. This is an excerpt of the CREATE DOMAIN syntax:
CREATE DOMAIN [IF NOT EXISTS] DomainName AS <Type> [STRICT]
[ DEFAULT [ON NULL..] <expression>]
[ [NOT] NULL]
[ CONSTRAINT [Name] CHECK (<expression>) [ ENABLE | DISABLE] ..]*
[ VALIDATE USING <json_schema_string>]
[ COLLATE collation ]
[ DISPLAY <expression> ]
[ ORDER <expression> ]
You can also drop domains with …
DROP DOMAIN [IF EXISTS] domain_name [FORCE [PRESERVE]]
Please keep in mind: Using FORCE in contrast to PRESERVE disassociates the domain from all its dependent columns. This includes dropping all the constraints on columns that were inherited from the domain. Defaults inherited from the domain are also dropped unless these defaults were set specifically on columns.
You may consult the documentation to get a detailed description of the different parts of domain syntax. To get an idea how to use it, let’s create a simple example – an email domain and use it in a person table.
create domain if not exists myemail_domain AS VARCHAR2(100) default on null 'XXXX' || '@missingmail.com' constraint email_c CHECK (regexp_like (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$')) display substr(myemail_domain, instr(myemail_domain, '@') + 1);
The check constraint EMAIL_C examines if the column stores a valid email, DISPLAY specifies how to convert the domain column for display purposes. You may use the SQL function DOMAIN_DISPLAY on the given column to display it. Now let’s use it in the table person.
drop table if exists person; create table person ( p_id number(5), p_name varchar2(50), p_sal number, p_email varchar2(100) domain myemail_domain ) annotations (display 'person_table');
As you can see, you may also use annotations in combination with domains. If you want to read more information on annotations, you may find examples etc in our posting “Annotations – The new metadata“.
Now let’s add rows with valid data. In 23ai you may use multi-value INSERTs.
insert into person values (1,'Bold',3000,null), (2,'Schulte',1000, 'mschulte@gmx.net'), (3,'Walter',1000,'twalter@t_online.de'), (4,'Schwinn',1000, 'Ulrike.Schwinn@oracle.com'), (5,'King',1000, 'aking@aol.com'); commit;
Let’s try to insert invalid data. You will get the following error message:
SQL> insert into person values (1,'Schulte',3000, 'mschulte%gmx.net');
insert into person values (1,'Schulte',3000, 'mschulte%gmx.net')
* ERROR at line 1: ORA-11534: check constraint (SCOTT.SYS_C008254) due to domain constraint SCOTT.EMAIL_C of domain SCOTT.MYEMAIL_DOMAIN violated
Now let’s query the table PERSON …
SQL> select * from person; P_ID P_NAME P_SAL P_EMAIL ---------- ---------------------------------- ---------- -------------------------- 1 Bold 3000 XXXX@missingmail.com 2 Schulte 1000 mschulte@gmx.net 3 Walter 1000 twalter@t_online.de 4 Schwinn 1000 Ulrike.Schwinn@oracle.com 5 King 1000 aking@aol.com
Monitoring domains
There are different possibilities to monitor domains. For example using SQL*Plus DESCRIBE already displays columns and associated domain and NULL constraints.
SQL> desc person Name Null? Type ----------------------------------- -------- ------------------------------------ P_ID NUMBER(5) P_NAME VARCHAR2(50) P_SAL NUMBER P_EMAIL NOT NULL VARCHAR2(100) SCOTT.MYEMAIL_DOMAIN
As already mentioned, there are new domain functions you can use in conjunction with the table columns to get more information about the domain properties. DOMAIN_NAME for example returns the qualified domain name of the domain that the argument is associated with, DOMAIN_DISPLAY returns the domain display expression for the domain that the argument is associated with. More information can be found in the documentation.
SQL> col p_name format a25 SQL> col DISPLAY format a25 SQL> select p_name, domain_display(p_email) DISPLAY from person; P_NAME DISPLAY ------------------------- ------------------------- Bold missingmail.com Schulte gmx.net Walter t_online.de Schwinn oracle.com King aol.com
Another way to monitor domains are data dictionary views like USER_DOMAINS, USER_DOMAIN_COLS, and USER_DOMAIN_CONSTRAINTS (also with ALL/DBA).
Here are some examples:
SQL> col owner format a15 SQL> col name format a20 SQL> select owner, name, data_display from user_domains; OWNER NAME --------------- -------------------- DATA_DISPLAY -------------------------------------------------------------------------------- SCOTT MYEMAIL_DOMAIN substr(myemail_domain, instr(myemail_domain, '@') + 1) SQL> col domain_owner format a15 SQl> col domain_name format a15 SQL> col name format a15 SQL> select * from user_domain_constraints where domain_name='MYEMAIL_DOMAIN'; NAME DOMAIN_OWNER DOMAIN_NAME C -------------------- --------------- --------------- - SEARCH_CONDITION -------------------------------------------------------------------------------- STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY INVALID -------- -------------- --------- ------------- -------------- --- ---- ------- ORIGIN_CON_ID ------------- EMAIL_C SCOTT MYEMAIL_DOMAIN C regexp_like (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$') ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 3 SYS_DOMAIN_C0039 SCOTT MYEMAIL_DOMAIN C "MYEMAIL_DOMAIN" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME
But what about the “good old” package DBMS_METADATA to get the DDL command? Let’s try GET_DDL and use SQL_DOMAIN as an object_type argument. And et voila you will get the result.
SQL> set long 1000
SQL> set longc 10000
SQL> select dbms_metadata.get_ddl('SQL_DOMAIN', 'MYEMAIL_DOMAIN');
DBMS_METADATA.GET_DDL('SQL_DOMAIN','MYEMAIL_DOMAIN')
----------------------------------------------------------------------------------------------------
CREATE DOMAIN "SCOTT"."MYEMAIL_DOMAIN" AS VARCHAR2(100) DEFAULT ON NULL 'XXXX' || '@missingmail.com'
CONSTRAINT "EMAIL_C" CHECK (REGEXP_LIKE (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$')) ENABLE
DISPLAY SUBSTR(myemail_domain, INSTR(myemail_domain, '@') + 1)
Use built-in domains
In addition to make it easier for you to start with, Oracle provides built-in domains you can use directly on table columns, for example, email, ssn, and credit_card. You find a list of them with names, allowed values and description in the documentation.
Another way to get this information is to query ALL_DOMAINS and filter on owner SYS. Then you will receive the built-in domains …
SQL> select name from all_domains where owner='SYS'; NAME ------------------------- PHONE_NUMBER_D EMAIL_D DAY_SHORT_D DAY_D MONTH_SHORT_D MONTH_D YEAR_D POSITIVE_NUMBER_D ...
Let’s investigate the domain EMAIL_D for email entries. Query the data dictionary views or use the package DBMS_METADATA toget the definition.
SQL> set long 1000
SQL> set longc 10000
SQL> select dbms_metadata.get_ddl('SQL_DOMAIN', 'EMAIL_D','SYS') domain_ddl;
DOMAIN_DDL
----------------------------------------------------------------------------------------------------
CREATE DOMAIN "SYS"."EMAIL_D" AS VARCHAR2(4000) CHECK (REGEXP_LIKE (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-zA-Z0-9])?)$')) ENABLE
Now let’s re-create our table PERSON. Please keep in mind that we need to adjust the length of the column P_EMAIL to 4000 – otherwise you will receive the following error:
-- you may drop the table first SQL> create table person ( p_id number(5), p_name varchar2(50), p_sal number, p_email varchar2(2000) domain EMAIL_D ) annotations (display 'person_table'); create table person * ERROR at line 1: ORA-11517: the column data type does not match the domain column
But this one will work because we adjusted the length of the column P_EMAIL.
create table person ( p_id number(5), p_name varchar2(50), p_sal number, p_email varchar2(4000) domain EMAIL_D ) annotations (display 'person_table');
In the next step let’s insert some data.
insert into person values (1,'Bold',3000,null); 1 row created. insert into person values (1,'Schulte',1000, 'mschulte@gmx.net'); 1 row created. insert into person values (1,'Walter',1000,'twalter@t_online.de') * ERROR at line 1: ORA-11534: check constraint (SCOTT.SYS_C008255) due to domain constraint SYS.SYS_DOMAIN_C002 of domain SYS.EMAIL_D violated
The email with the sign ‘_’ is not a valid entry, so we need to change it to ‘-‘.
SQL> insert into person values (1,'Walter',1000,'twalter@t-online.de'); 1 row created.
Validate JSON
Oracle Database 23 supports not only the JSON datatype but also JSON schema validation. A JSON schema similar to XML schema defines the rules that allow you to annotate and validate JSON documents. The schema specifies the permitted keywords, data types for their values, and the structure in which they can be nested. You can define the key-value pairs as mandatory or optional. You can validate a JSON document using a JSON schema with the IS JSON check constraint clause VALIDATE or use a shorthand syntax without the check constraint instead.
The following example shows how to use an inline schema definition in a CREATE TABLE command. This is the shorthand syntax without a check constraint.
create table person (id NUMBER, p_record JSON VALIDATE '<json-schema>');
Domains also support JSON validation. The difference between the inline schema definition and the domain is that a domain stores a reference to the domain (call by reference instead of call by value). If the domain changes, so does the validation logic. If the domain is dropped, validation does not happen and an error to this effect is raised.
Let’s give an example of a domain with the JSON validation clause [ VALIDATE USING <json_schema_string>]. The JSON schema we use here describes JSON documents with information about persons.
create domain p_recorddomain AS JSON VALIDATE USING '{ "type": "object",
"properties": {
"first_name": { "type": "string" },
"last_name": { "type": "string" },
"birthday": { "type": "string", "format": "date" },
"address": {
"type": "object",
"properties": {
"street_address": { "type": "string" },
"city": { "type": "string" },
"state": { "type": "string" },
"country": { "type" : "string" }
}
}
}
}' ;
Automatically a check constraint to validate the schema is created. Query USER_DOMAIN_CONSTRAINTS to verify this.
SQL> set long 1000
SQL> col name format a20
SQL> select name, generated, constraint_type, search_condition
from user_domain_constraints
where domain_name like 'P_RECORD%';
NAME GENERATED C
-------------------- -------------- -
SEARCH_CONDITION
---------------------------------------------------------------------
SYS_DOMAIN_C0035 GENERATED NAME C
"P_RECORDDOMAIN" IS JSON VALIDATE USING '{
"type": "object",
"properties": {
"first_name": { "type": "string" },
"last_name": { "type": "string" },
"birthday": { "type": "string", "format": "date" },
"address": {
"type": "object",
"properties": {
"street_address": { "type": "string" },
"city": { "type": "string" },
"state": { "type": "string" },
"country": { "type" : "string" } } } } }'
Now let’s use the domain in the table PERSON.
create table person ( id NUMBER, p_record JSON DOMAIN p_recorddomain);
Insert valid data.
insert into person values
(1,
'{ "first_name": "George",
"last_name": "Washington",
"birthday": "1732-02-22",
"address": {
"street_address": "3200 Mount Vernon Memorial Highway",
"city": "Mount Vernon",
"state": "Virginia",
"country": "United States"
}
}'
);
The next record is not a valid entry.
SQL> insert into person values (2, '{
"name": "George Washington",
"birthday": "February 22, 1732",
"address": "Mount Vernon, Virginia, United States"
}');
insert into person values (2, '{
*
ERROR at line 1:
ORA-40875: JSON schema validation error
Summary
The domain functionality is a very powerful new “tool” for application development in Oracle Database 23. In general domains allow users to declare the intended usage for columns with a separate data dictionary objects so that abstract domain specific knowledge can be easily reused. You can use them to provide additional information to a stored column and you can define and validate data with them. Not only relational columns can be validated but also JSON data with the new VALIDATE clause. Like other database objects it can be created, dropped and altered. With this, you have the freedom to change the validation logic or information without changing the table definition. If you want to learn more about SQL domains, check out the documentation. There, you will find more examples also about multi-column and flexible domains which are not covered in this posting.