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
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.