Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

enum value: What is its %type?

Mike KutzMay 7 2024 — edited May 7 2024

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)

This post has been answered by Chris Saxon-Oracle on May 7 2024
Jump to Answer
Comments
Post Details
Added on May 7 2024
1 comment
362 views