Apparently , a flexible domain can only use multi-column domains
IMO - this limitation should be clearly stated in the syntax description portion of the document, not implied in the example
These are multicolumn domains with check constraints to ensure the domain columns store appropriate values for the expense type.
documentation
attempted code:
prompt cleanup
drop domain if exists date_convertable;
drop domain if exists date_oracle;
drop domain if exists date_oracle_24;
drop domain if exists date_iso;
drop domain if exists date_iso_24;
drop domain if exists date_eu;
drop domain if exists date_eu_24;
drop domain if exists date_us;
drop domain if exists date_us_24;
drop domain if exists date_us_12;
drop domain if exists date_ms_24;
prompt The purpose of these domains are to validate that a string can be converted to a `date` based on a preset format.
prompt these are to be used as part of an ETL process
create domain date_oracle as varchar2(32767) check ( date_oracle is null or to_date( date_oracle default null on conversion error, 'dd-mon-yyyy' ) is not null );
create domain date_oracle_24 as varchar2(32767) check ( date_oracle_24 is null or to_date( date_oracle_24 default null on conversion error, 'dd-mon-yyyy hh24:mi:ss' ) is not null );
create domain date_eu as varchar2(32767) check ( date_eu is null or to_date( date_eu default null on conversion error, 'dd/mm/yyyy' ) is not null );
create domain date_eu_24 as varchar2(32767) check ( date_eu_24 is null or to_date( date_eu_24 default null on conversion error, 'dd/mm/yyyy hh24:mi:ss' ) is not null );
create domain date_us as varchar2(32767) check ( date_us is null or to_date( date_us default null on conversion error, 'mm/dd/yyyy' ) is not null );
create domain date_us_12 as varchar2(32767) check ( date_us_12 is null or to_date( date_us_12 default null on conversion error, 'mm/dd/yyyy hh12:mi:ss am' ) is not null );
create domain date_us_24 as varchar2(32767) check ( date_us_24 is null or to_date( date_us_24 default null on conversion error, 'mm/dd/yyyy hh24:mi:ss' ) is not null );
create domain date_iso as varchar2(32767) check ( date_iso is null or to_date( date_iso default null on conversion error, 'yyyy-mm-dd' ) is not null );
create domain date_iso_24 as varchar2(32767) check ( date_iso_24 is null or to_date( date_iso_24 default null on conversion error, 'yyyy-mm-dd hh24:mi:ss' ) is not null );
create domain date_ms_24 as varchar2(32767) check ( date_ms_24 is null or to_date( date_ms_24 default null on conversion error, 'yyyy-mm-dd"T"hh24:mi:ss' ) is not null );
prompt single point of testing
create flexible domain if not exists date_convertable ( date_str )
choose domain using ( expected_format varchar2(32767) )
from case lower(expected_format)
when 'oracle' then date_oracle( date_str )
when 'oracle 24' then date_oracle_24( date_str )
when 'iso' then date_iso( date_str )
when 'iso 24' then date_iso_24( date_str )
when 'eu' then date_eu( date_str )
when 'eu 24' then date_eu_24( date_str )
when 'us' then date_us( date_str )
when 'us 24' then date_us_24( date_str )
when 'us 12' then date_us_12( date_str )
else date_ms_24( date_str )
end;
If I create the regular domains as a multi-column domain, everything works.
prompt cleanup
drop domain if exists date_convertable;
drop domain if exists date_oracle;
drop domain if exists date_oracle_24;
drop domain if exists date_iso;
drop domain if exists date_iso_24;
drop domain if exists date_eu;
drop domain if exists date_eu_24;
drop domain if exists date_us;
drop domain if exists date_us_24;
drop domain if exists date_us_12;
drop domain if exists date_ms_24;
prompt enhanced working version
create domain if not exists date_oracle as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'dd-mon-yyyy' )),0) > 1800 );
create domain if not exists date_oracle_24 as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'dd-mon-yyyy hh24:mi:ss' )),0) > 1800 );
create domain if not exists date_eu as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'dd/mm/yyyy' )),0) > 1800 );
create domain if not exists date_eu_24 as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'dd/mm/yyyy hh24:mi:ss' )),0) > 1800 );
create domain if not exists date_us as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'mm/dd/yyyy' )),0) > 1800 );
create domain if not exists date_us_12 as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'mm/dd/yyyy hh12:mi:ss am' )),0) > 1800 );
create domain if not exists date_us_24 as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'mm/dd/yyyy hh24:mi:ss' )),0) > 1800 );
create domain if not exists date_iso as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'yyyy-mm-dd' )),0) > 1800 );
create domain if not exists date_iso_24 as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'yyyy-mm-dd hh24:mi:ss' )),0) > 1800 );
create domain if not exists date_ms_24 as ( date_str as varchar2(32767) ) check ( date_str is null or nvl(extract( year from to_date( date_str default null on conversion error, 'yyyy-mm-dd"T"hh24:mi:ss' )),0) > 1800 );
create flexible domain if not exists date_convertable ( date_str )
choose domain using ( expected_format varchar2(32767) )
from case lower(expected_format)
when 'oracle' then date_oracle( date_str )
when 'oracle 24' then date_oracle_24( date_str )
when 'iso' then date_iso( date_str )
when 'iso 24' then date_iso_24( date_str )
when 'eu' then date_eu( date_str )
when 'eu 24' then date_eu_24( date_str )
when 'us' then date_us( date_str )
when 'us 24' then date_us_24( date_str )
when 'us 12' then date_us_12( date_str )
else date_ms_24( date_str )
end;
prompt quick unit test
with data (date_data,date_fmt,expected) as (
select '12-jan-2024', 'oracle', 1 union all
select '12-jan-24', 'oracle', 0 union all
select '11/20/23', 'us', 0 union all
select '11/20/2023', 'us', 1 union all
select '11/20/2023', 'eu', 0 union all
select '1/2/2023', 'us', 1 union all
select '11/11/11', 'us', 0 union all
select '12-jan-24', 'us', 0
)
select a.*, domain_check( date_convertable, a.date_data, a.date_fmt ) actual
from data a;