I would like to declare the data type of a variable to be the same as the enum_value
of an enum domain in PL/SQL.
This would come in handy for case
statements:
case var
when days_of_week.monday then ..
when days_of_week.tuesday then ..
..
end case;
since you can select * from days_of_week
, I thought one could declare var days_of_week.enum_value%type;
but that didn't work.
Another important thing is correctly transforming dirty input data into a valid value as part of an ETL workflow
function txt2days_of_week( txt in varchar2 ) return days_of_week.enum_value%type
as
return_value days_of_week.enum_value%type;
begin
if txt is null then return null; end if; -- short circuit
case substr(trim(upper(txt)), 1, 2)
when days_of_week.monday then return_value := days_of_week.monday;
when days_of_week.tuesday then return_value := days_of_week.tuesday;
when days_of_week.wednesday then return_value := days_of_week.wednesday;
when days_of_week.thursday then return_value := days_of_week.thursday;
when days_of_week.friday then return_value := days_of_week.friday;
when days_of_week.saturday then return_value := days_of_week.saturday;
when days_of_week.sunday then return_value := days_of_week.sunday;
else
dbms_output.put_line( 'Unknown day of week "' || txt || '"' );
return null;
end case;
return return_value;
end;
/
QUESTION
How does one set a variable data type to be identical to the enum's data type in PL/SQL?
(PS - I still think we need SQL Subtypes more than we need Domains)