I was trying out the Flexible domains but can’t seem to get the display functionality to work.. i have attached a sample script but whatever I try I always get NULL when using domain_display …. I’ve tried it on 23 Always Free and a local docker 23 Free.
drop table domaintest purge;
drop domain zipcode_d;
drop domain zipcode_nl_d;
drop domain zipcode_us_d;
drop domain zipcode_es_d;
purge recyclebin;
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' )
/
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
)
/
create table if not exists domaintest
( zipcode varchar2( 7 )
, countrycode char ( 2 )
, domain zipcode_d ( zipcode )
using ( countrycode )
)
/
insert into domaintest values
('4901TD', 'NL'),
('3317 GP', 'NL'),
('33417', 'US')
/
commit;
select zipcode
,countrycode
,domain_display (zipcode)
from domaintest
/