A much needed domain seems to be missing from the included pre-configured domains ( all_domains ).
Temporal Validity was introduced in 12c. Yet, there was no way to automatically validate the start/end dates used in the PERIOD FOR clause.
23c DOMAIN features gives us the ability to automatically define some constraints to assert that the values used for PERIOD FOR are usable. But, those domains are not included in the initial install of 23c FREE. I think some basic ones should be included and the building of such domains should be examples in the Documentation.
DOMAIN ENHANCEMENT
Additionally, there may be a need to combine DOMAINS.
eg date_range_full combined with trunc_day on both start_date,end_date (see “suggested enhancement”)
RECOMMENDED ADDITIONAL DOMAINS
set define off
-- ensure a DATE represents a DAY, MONTH, or YEAR
create domain if not exists trunc_day as date check (trunc_day = trunc(trunc_day, 'day') );
create domain if not exists trunc_month as date check (trunc_month = trunc(trunc_month, 'month') );
create domain if not exists trunc_year as date check (trunc_year = trunc(trunc_year, 'year') );
-- ensure 2x DATEs can be safely used in a PERIOD FOR clause (ie start_date < end_date )
create domain if not exists date_range_basic
as (
start_date as date,
end_date as date
)
constraint check (start_date < end_date)
annotations ( Purpose 'Ensures a date range is valid and can be used for PERIOD FOR' );
create domain if not exists date_range_start
as (
start_date as date not null default sysdate,
end_date as date
)
constraint check (start_date < end_date)
annotations ( Purpose 'Ensures a date range contains a start_date and is valid and can be used for PERIOD FOR' );
create domain if not exists date_range_both
as (
start_date as date not null default sysdate,
end_date as date not null default sysdate + 1
)
constraint check (start_date < end_date)
annotations ( Purpose 'Ensures a date range contains both start_date & end_date and is valid and can be used for PERIOD FOR' );
SUGGESTED ENHANCEMENT
In some cases, it would make sense if domains could be combined.
-- example only - feature not implemented
create domain custom_range as (
start_date as domain trunc_day,
end_date as domain trunc_day
)
domain date_range_both(start_date, end_date)
annotations ( Purpose 'Ensure date range is valid and dates represent DAYs (no time component)' );