📋Steps in this guide1/16

Domains in Oracle Database 23ai/26ai

This article demonstrates the use of Domains in Oracle database 23ai/26ai.

oracle 23configurationintermediate

by OracleDba

32 views

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 our 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 to the test user. We then connect to this test user to run the examples.

Code/Command (click line numbers to comment):

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 four basic domain types. - Single Column Domain - Multi Column Domain - Flexible Domain - Enumeration 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, flexible domains and enumeration 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 , which specifies the data type of the domain, and adds a check constraint to make sure the contents follow a basic email pattern. We could add and references, to explain how we want the data to be displayed and ordered by default, and also add annotations. If we were using extended data types ( ) we could add collation for case insensitive searches. We'll use this domain going forward. We can now reference that domain in a table column definition in one of several ways. When we describe the table we see the data type definition, along with the referenced domain. We can insert data into to the column provided it doesn't violate the associated check constraint.

Code/Command (click line numbers to comment):

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

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;

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

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

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

SQL>

-- Insert correctly.
insert into t1 values (1, '[email protected]');
insert into t1 values (2, '[email protected]');

-- 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. 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. We describe the table, and we can see the domain is associated with the columns. Let's clean up those objects.

Code/Command (click line numbers to comment):

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;

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

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>

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. 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 or expression. In this example we use a expression. We recreate the addresses table using the flexible domain. We specify the discriminant column with the keyword. 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. 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. Let's clean up those objects.

Code/Command (click line numbers to comment):

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

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;

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

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>

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>

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;

Enumeration Domain

An enumeration domain is a list of names with associated values. The value can be enumerated automatically or manually. In the following example we create an enumeration domain with the values enumerated automatically. Notice they are values from 1-n. Alternatively we can manually enumerate them. We can also manually enumerate them using string values. An enumeration domain can be used to define a table column. Be careful when evolving an automatically enumerated domain, as a the values are assigned in order, so a change in the item order could logically corrupt our table. In the following example we recreate the domain in a different order and we can see our table data has been logically corrupted. The lane has changed from to . As a result it might make sense to avoid automatic enumeration for domains used as part of table definitions.

Code/Command (click line numbers to comment):

create domain kanban_lanes_domain as
enum (
  backlog,
  doing,
  review,
  done
);


select * from kanban_lanes_domain;

ENUM_NA ENUM_VALUE
------- ----------
BACKLOG          1
DOING            2
REVIEW           3
DONE             4

SQL>

drop domain if exists kanban_lanes_domain;

create domain kanban_lanes_domain as
enum (
  backlog = 10,
  doing = 20,
  review = 30,
  done = 40
);


select * from kanban_lanes_domain;

ENUM_NA ENUM_VALUE
------- ----------
BACKLOG         10
DOING           20
REVIEW          30
DONE            40

SQL>

drop domain if exists kanban_lanes_domain;

create domain kanban_lanes_domain as
enum (
  backlog = 'B',
  doing = 'D',
  review = 'R',
  done = 'D2'
);


select * from kanban_lanes_domain;

ENUM_NA EN
------- --
BACKLOG B
DOING   D
REVIEW  R
DONE    D2

SQL>

drop domain if exists kanban_lanes_domain;

create domain kanban_lanes_domain as
enum (
  backlog,
  doing,
  review,
  done
);


create table kanban_board (
  id    number generated always as identity,
  story clob,
  lane  kanban_lanes_domain
);

insert into kanban_board (story, lane) values ('Do something', kanban_lanes_domain.doing);

column story format A20
select id, story, domain_display(lane) as lane from kanban_board;

        ID STORY                LANE
---------- -------------------- -------
         1 Do something         DOING

SQL>

drop domain if exists kanban_lanes_domain force;

create domain kanban_lanes_domain as
enum (
  new_lane,
  backlog,
  doing,
  review,
  done
);

alter table kanban_board modify lane domain kanban_lanes_domain;

column story format A20
select id, story, domain_display(lane) as lane from kanban_board;

        ID STORY                LANE
---------- -------------------- --------
         1 Do something         BACKLOG

SQL>

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". The function allows us to use the 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 clause. The function allows us to use the characteristics. Notice the data is presented in mixed case, but the ordering is based on lower case comparisons. The function allows us to return the domain associated with a column. The domain can be used as part of a cast operation. The function allows us to check some data against the domain check constraint. The behaviour of this function changed between Oracle 23.2 and 23.3. Here we will just discuss the 23.3 functionality. The function checks the value can be converted to the correct data type for the domain, but doesn't check the constraints associated with the domain. In the example below, both expressions can be converted to the domain type, so they return true, even though the second example would fail on the constraint if we tried a call.

Code/Command (click line numbers to comment):

select email from t1 order by email;

EMAIL
--------------------------------------------------------------------------------
[email protected]
[email protected]

SQL>

select domain_display(email) from t1 order by email;

DOMAIN_DISPLAY(EMAIL)
--------------------------------------------------------------------------------
[email protected]
[email protected]

SQL>

select email from t1 order by domain_order(email);

EMAIL
--------------------------------------------------------------------------------
[email protected]
[email protected]

SQL>

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

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

SQL>

select cast('[email protected]' as domain email_dom);

CAST('[email protected]'ASDOMAINEMAIL_DOM)
----------------------------------------------------------------------------------------------------
[email protected]

SQL>

select domain_check(email_dom, '[email protected]');

DOMAIN_CHEC
-----------
TRUE

SQL>


select domain_check(email_dom, 'grape');

DOMAIN_CHEC
-----------
FALSE

SQL>

select domain_check_type(email_dom, '[email protected]');

DOMAIN_CHEC
-----------
TRUE

SQL>


select domain_check_type(email_dom, 'grape');

DOMAIN_CHEC
-----------
TRUE

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.

Code/Command (click line numbers to comment):

-- 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. We can use the 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. We drop the domain with the option, and we can see the constraint has gone, and the domain is no longer shown when the table is described.. The option preserves the defaults and constraints on the dependent table columns when it drops the domain.

Code/Command (click line numbers to comment):

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

SQL>

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>

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>

-- 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. Let's assume our requirement changes and we need to extend the string length to 200. We use the view to list the table columns associated with the domain. We drop the domain with the option. We recreate the domain, extending the column size to 200. If we try to re-apply the domain, hoping this would extend the column, we will be dissapointed. 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.

Code/Command (click line numbers to comment):

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

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>

drop domain email_dom force preserve;

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

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>

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 view. The and column have been added to the view. The , and views give information about the domain definition.

Code/Command (click line numbers to comment):

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>

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>

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. We can't define a variable based on a domain directly. We declare a variable based on the column type, but we can see the constraint is not checked during a variable assignment. Maybe this functionality will be included in a later release.

Code/Command (click line numbers to comment):

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

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>

declare
  l_email t1.email%type;
begin
  l_email := '[email protected]';
  l_email := 'banana';
end;
/

PL/SQL procedure successfully completed.

SQL>

Strict Domains

In the previous examples we didn't include the 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. 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.

Code/Command (click line numbers to comment):

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>

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 our 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: - CREATE DOMAIN - ALTER DOMAIN - DROP DOMAIN - Domain Functions Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!