6 maart 2024

We can establish the rules by implementing check constraints on the column of the table. If you want to register multiple addresses or multiple email addresses, you have to implement a check constraint for every individual instance of the address. This goes against the Single Point Of Definition (SPOD) principle, but you had no choice, until Oracle Database 23c. Also, you cannot apply a check constraint on multiple columns (some exceptions apply).

In Oracle Database you now have the possibility to define a domain where you can establish these rules once and apply them to multiple columns. You can also add display and ordering rules. It is even possible to define a constraint on multiple values.

Built-in Domains
Oracle Database 23c already comes with a couple of built-in domains. 

PHONE_NUMBER_D, EMAIL_D, DAY_SHORT_D, DAY_D, MONTH_SHORT_D, MONTH_D, YEAR_D, POSITIVE_NUMBER_D, NEGATIVE_NUMBER_D, NON_POSITIVE_NUMBER_D, NON_NEGATIVE_NUMBER_D, MAC_ADDRESS_D, SSN_D, CREDIT_CARD_NUMBER_D, IPV4_ADDRESS_D, IPV6_ADDRESS_D, SUBNET_MASK_D, SHA1_D, SHA256_D, SHA512_D, CIDR_D, MIME_TYPE_D
 

Note that these are oriented on the English speaking part of the world, especially the 

PHONE_NUMBER_D, SSN_D, DAY(_SHORT)_D, and MONTH(_SHORT)_D and they do NOT follow any NLS settings.

Using this query, you can find out the specification for the constraint definition (search_condition) for the (built-in) domains:

select adc.domain_owner

     , adc.domain_name

     , to_char( adc.search_condition ) search_condition

from  all_domain_constraints adc

where adc.domain_name in ( 'PHONE_NUMBER_D'

                          , 'DAY_SHORT_D'

                          , 'DAY_D'

                          , 'MONTH_SHORT_D'

                          , 'MONTH_D'

                          , 'SSN_D'

                          )

/

Single Column Domain
Let’s create a domain to validate a Dutch zip code.

create      domain zipcode_d as varchar2( 7 char )
constraint  zipcode_c check ( regexp_like ( zipcode_d, '^[1-9][[:alnum:]]{3}[ ]{0,1}[[:alpha:]]{2}$' ) )
deferrable  initially deferred
display     upper( replace( zipcode_d, ' ', '' ) )
order       lower( replace( zipcode_d, ' ', '' ) )
annotations ( Description 'Domain for Dutch zip codes' )
/

The domain consists of the datatype of the column, in this case varchar2( 7 char ), a default value (not used here), and a check constraint, which can be deferrable (or not), but be aware, the constraint that is created on the table, has a different, generated (SYS_Cxxxxxx) name. So, you will have to write some smart PL/SQL code to set the constraint (immediate or deferred). But I think the constraints should always be either immediate or deferred, not depending on when it is used. You can define multiple check constraints on a domain.

If you select the column, using the domain_display() function, the value is returned using the formula in the display clause. In this case, any spaces between the numbers and the letters are removed. You can even make the display clause dependant on for example the current user using case statements. You cannot call PL/SQL functions in this clause.

If you order your query using domain_order() your results will be ordered using the rules in the order clause. In this example it uses the lowercase value of all the zip codes.
Like all (or most) objects, you can also add annotations to the domain.

Annotations are a new feature of Oracle Database 23c, see Annotations

A domain cannot use the create or replace syntax. If you want to alter your domain, you first have to drop it and then recreate it. If you have used the domain in a table, you have to use the force option. This removes the check constraint from all columns that use this domain. You can add the domain again, by altering the table and modifying the column. You can alter the display, order and annotation attributes of the domain, without having to drop and recreate the domain. If you want the domain display to be dependant on for instance the currently connected user, you can alter the display clause using this statement:

alter   domain zipcode_d
modify
display     case sys_context( 'userenv', 'session_user' )
             when 'PATRICK' then replace( zipcode_d, ' ', '' )
             else                upper( replace( zipcode_d, ' ', '' ) )
           end
/
You can modify the order and the annotations in the same manner.

A domain can be defined as strict which means the datatype of the column must be defined exactly the same as the datatype of the domain. If you create a simple domain like this:

Create domain vc10_d as varchar2( 10 )
/

Then the columns that use this domain can be of any length.
If, however, you define the domain as strict, like this:

Create domain vc10_sd as varchar2( 10 ) strict
/
Then the columns that use this domain must be of length 10, as specified in the domain.

Multi Column Domain
You can create a domain that spans multiple columns, for example a currency value, including the currency code.

create domain currency_d as
( amount            as number
, iso_currency_code as char( 3 char )
)
display iso_currency_code || to_char( amount, '999,999,990.00' )
order   to_char( amount, '999,999,990.00' ) || iso_currency_code
/
In this case we only use the domain for the display and order functionality. To create a table that uses this domain, you have to create all the separate columns and assign them to the domain. The names of the columns do not have to be the same as the ones in the domain and the datatype can be a little different. You cannot define a Multi Column Domains as strict.

create table if not exists amounts
( amount   number
, currency char( 3 char )
, domain currency_d( amount, currency )
)
/
insert into amounts( amount, currency )
values ( 100, 'EUR' )
    , ( 200, 'USD' )
    , ( 50,  'GBP' )
/
commit
/
When using the domain_display and/or the domain_order functions, you don’t have to specify which domain is to be used, but you do have to specify the columns to be used in the order in which they are defined in the domain.

select amount
    , currency
    , domain_display( amount, currency ) as display
from   amounts
order  by domain_order( amount, currency )
/
Using this Multi Column Domain you could build a zip code check that performs a different check for the different types of zip codes in the world. For various countries there are various rules. I came up with a multi column domain that will check both Dutch and US zipcodes.

create      domain zipcode_d as
           ( zipcode as varchar2( 7 char ) annotations ( zipcode_d )
            , country as varchar2( 2 char ) annotations ( zipcode_d )
            )
constraint   zipcode_c check
            (    ( ( country = 'NL' ) and ( regexp_like ( zipcode, '^[1-9][[:alnum:]]{3}[ ]{0,1}[[:alpha:]]{2}$' ) ) )
              or ( ( country = 'US' ) and ( regexp_like ( zipcode, '^[[:alnum:]]{5}$' ) ) )
            )
deferrable   initially immediate
display      case
              when country = 'NL' then upper( replace( zipcode, ' ', '' ) )
              when country = 'US' then zipcode
            end
order        case
              when country = 'NL' then lower( replace( zipcode, ' ', '' ) )
              when country = 'US' then zipcode
            end
annotations ( Description 'Domain for Dutch and US zip codes' )
/

This is one way to do it, but it might not be the clearest way to build and maintain a domain. A better and much cleaner way to do this is building a flexible usage domain.
 

Flexible Usage Domain
You can create a flexible usage domain referencing other, non-flexible, domains. Both single- and multi column domains can be used. If you would like to build a flexible domain for the zip code check you can do this in a procedural fashion. Build a domain for every different kind of zip code you want to check and then build a flexible usage domain that references these domains.

create domain zipcode_nl_d as varchar2( 7 char )
constraint  zipcode_nl_c check ( regexp_like ( zipcode_nl_d, '^[1-9][[:alnum:]]{3}[ ]{0,1}[[:alpha:]]{2}$' ) )
deferrable  initially deferred
display     upper( replace( zipcode_nl_d, ' ', '' ) )
order       lower( replace( zipcode_nl_d, ' ', '' ) )
annotations ( Description 'Domain for Dutch zip codes' )
/
create domain zipcode_us_d as varchar2( 7 char )
constraint  zipcode_us_c check ( regexp_like ( zipcode_us_d, '^[[:alnum:]]{5}$' ) )
deferrable  initially deferred
annotations ( Description 'Domain for US zip codes' )
/
create domain zipcode_es_d as varchar2( 7 char )
constraint  zipcode_es_c check ( regexp_like ( zipcode_es_d, '^[0-5][[:alnum:]]{4}$' ) )
deferrable  initially deferred
annotations ( Description 'Domain for Spanish zip codes' )
/
After these ‘simple’ domains have been created you can create the flexible usage domain:

create flexible domain zipcode_d (
 zipcode
)
choose domain using ( country char( 2 ) )
from ( case country
        when 'NL' then zipcode_nl_d ( zipcode )
        when 'US' then zipcode_us_d ( zipcode )
        when 'ES' then zipcode_es_d ( zipcode )
      end
    )
/

If you want to create a table using this flexible domain, its syntax is pretty much like the syntax used in the multicolumn domain:
 

create table if not exists domaintest
( zipcode     varchar2( 7 )
, countrycode char    ( 2 )
, domain zipcode_d ( zipcode )
   using ( countrycode )
)
/

If you insert a row with a country code that is know in the flexible usage domain then the appropriate simple domain will be used. If you supply a country code, that is not in the flexible usage domain, the row will be added without any checks.

If a constraint is violated, for instance when you supply a US zip code that is not a valid zip code in Spain, you will see a SYS_CXXXXX constraint being violated with a reference to the flexible usage domain, but you cannot easily see which ‘subdomain’ is violated. Hopefully this will change in a future release.

The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/domains.sql

Also read my previous blogs about Oracle Database 23:

• Introduction blog series Oracle Database 23
• Developer Role
• Group by Alias
• If [Not] Exists 
• Table values constructor
• The Boolean data type
• New PL/SQL iterator constructs
• Annotations
• SQL Macros 
• Immutable tables

And my next blog:

• Lock-Free reservation

Auteur: Patrick Barel

Terug naar overzicht

Deel

Facebook Twitter LinkedIn E-mail Whatsapp