Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Public Domains not showing in ALL_DOMAINS

Mike KutzMar 3 2024

When I grant execute any domain on schema xxx to Public, the domains in xxx do not show up in all_domains.

I cannot apply them to a Table (as expected).

But, I can still use them in PL/SQL & SQL via domain_check() … including compiled code ( packages, etc. )

I would like a view that shows all available domains for use in PL/SQL & SQL (or, a column to flag such usage)

database: 23.3 FREE (VirtualBox)

setup (run as DBA)

create user test_user identified by Change0nInstall account unlock;

grant connect to test_user;

create user MyDomains no AUTHENTICATION
default tablespace users
temporary tablespace temp
quota 1M on users
account unlock;

grant create domain to MyDomains;
grant execute any domain on schema MyDomains to public;

create domain MyDomains.id_d as int check ( value > 5 );

test (connect and run as test_user )

select * from all_domains where owner = 'MYDOMAINS';
-- no rows selected

with data( val ) as (
	select 1 union all
  select 5 union all
  select 6
)
select d.val, domain_check( MyDomains.id_d, d.val ) is_valid
from data d;
-- results are as expected

clean up (run as DBA)

drop user test_user;
drop user MyDomains cascade;
This post has been answered by Chris Saxon-Oracle on Mar 4 2024
Jump to Answer
Comments
Post Details
Added on Mar 3 2024
1 comment
119 views