Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Knut.

Asked: February 26, 2025 - 10:39 am UTC

Last updated: December 09, 2025 - 3:17 am UTC

Version: 23ai

Viewed 1000+ times

You Asked

hi folks,

I am very happy about the new enum domains in PL/SQL. They are very useful and can make the code much more maintainable.
But I struggled over a problem. When I create an enum domain like
create domain MY_TEST_ENUM as enum (
  text1 = 'text1',
  text2 = 'text2',
  text3 = 'text3'    
)

and I would like to use it in e.g. a procedure like this
create procedure superDuper is
  lTestString varchar2(100);
begin
  select MY_TEST_ENUM.text1 into lTestString from dual; -- first try
  lTestString := domain_display(MY_TEST_ENUM.text2);    -- second try
  lTestString := MY_TEST_ENUM.text3;                    -- third try
end;
/

the compiler feels well and accepts my code in the the first try.
But for the second try which is some sort of easier to read and understand it shouts out a "PLS-00201: identifier 'DOMAIN_DISPLAY' must be declared".
For the third try which is the best readable way it gives me a "PLS-00905: object 'MY_TEST_ENUM' is invalid". Btw the object is not invalid when asking the ALL_OBJECT table.
Is there a way to assign the value of an enum domain without using a select statement?

Cheers
Knut

and Chris said...

PL/SQL has little knowledge of domains in general. So yes, you currently need to use a select statement to assign an enum value to a variable from its name. Though you no longer need the FROM dual clause :)

create procedure superDuper is
  lTestString varchar2(100);
begin
  select MY_TEST_ENUM.text1 into lTestString;
end;


Also note: for enums, DOMAIN_DISPLAY returns the name associated with the value. So domain_display(MY_TEST_ENUM.text2) finds the value for text2, then returns its name. Which is text2.

i.e. you're essentially getting back what you passed in!

This is clearer if the enum names and values are different:

drop domain MY_TEST_ENUM ;
create domain MY_TEST_ENUM as enum (
  name1 = 'val1',
  name2 = 'val2',
  name3 = 'val3'    
)
/

select 
  MY_TEST_ENUM.name1,
  domain_display ( cast ( MY_TEST_ENUM.name1 as MY_TEST_ENUM ) );

NAME DOMAI
---- -----
val1 NAME1


I doubt there's many cases where this is useful. DOMAIN_DISPLAY is relevant when you have stored enum values and want to convert them back to names.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Knut Göttling, February 26, 2025 - 3:29 pm UTC

Thank you for your response, Chris. Let's hope that PL/SQL will get better knowledge of the domains in the near future ;-).

A simple solution

Akis, December 01, 2025 - 1:40 pm UTC

I use this simple function:

function fromDomain(pDomain in varchar2, pName in varchar2) return varchar2 is
fResult varchar2(50);
begin
execute immediate 'select enum_value from ' || pDomain || ' where enum_name = ''' || pName || '''' into fResult;
return fResult;
exception
when others then
return null;
end;

Chris Saxon
December 03, 2025 - 6:44 pm UTC

Please don't do that! Building SQL from user input is a recipe for SQL injection.

Why exactly have you built this function?

dreaded when others then null strikes again.

Rajeshwaran Jeyabal, December 08, 2025 - 5:30 am UTC

Even today, having an WHEN OTHERS THEN RETURN NULL; in the code would probably make Tom very concerned.

https://asktom.oracle.com/Misc/dreaded-others-then-null-strikes-again.html
Connor McDonald
December 09, 2025 - 3:17 am UTC

yup

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library