How to make enum types in PL/SQL?

Question

For example I want to make my own Boolean type and call it Bool. How do I do that?

Or a type for traffic lights, i.e. that has only Red, Yellow, Green in it (and null of course).

Weakkneed

Answer 1

The closest think I could think of is:

create or replace type lights as object
(
  red varchar2(8),
  yellow varchar2(8),
  green varchar2(8),
constructor function lights return self as result
)

and the body:

create or replace type body lights is
constructor function lights return self as result is
begin
  self.red = 'red';
  self.yellow = 'yellow';
  self.green = 'green';
  return;
end;
end;

Then in the code you can use it:

declare
l lights := new lights;
begin
   dbms_output.put_line(l.red);
end;

Holds

Answer 2

I don't think that solution, provided by A.B.Cade is totally correct. Let's assume procedure like this:

procedure TestEnum(enum_in lights);

What is the value of enum_in? red? yellow? green?

I propose another solution. Here is package example

CREATE OR REPLACE PACKAGE pkg_test_enum IS

  SUBTYPE TLight IS BINARY_INTEGER RANGE 0..2;
  Red CONSTANT TLight := 0;
  Yellow CONSTANT TLight := 1;
  Green CONSTANT TLight := 2;

  --get sting name for my "enum" type
  FUNCTION GetLightValueName(enum_in TLight) RETURN VARCHAR2;

  PROCEDURE EnumTest(enum_in TLight);

END pkg_test_enum;


CREATE OR REPLACE PACKAGE BODY pkg_test_enum IS 
  FUNCTION GetLightValueName(enum_in TLight) 
  RETURN VARCHAR2
  IS
    ResultValue VARCHAR2(6);
  BEGIN
    CASE enum_in 
      WHEN Red THEN ResultValue := 'Red';
      WHEN Green THEN ResultValue := 'Green';
      WHEN Yellow THEN ResultValue := 'Yellow';
      ELSE ResultValue := '';
    END CASE;
    RETURN ResultValue;
  END GetLightValueName;


  PROCEDURE EnumTest(enum_in TLight)
  IS
  BEGIN
    --do stuff
    NULL;
  END EnumTest;
END pkg_test_enum;

I can now use TLight in different packages. I can now test enum_in against predefined values or null.

Here is usage example

begin
  pkg_test_enum.EnumTest(pkg_test_enum.Red);
end;

Besides, you can make this type not nullable.

SUBTYPE TLight IS BINARY_INTEGER RANGE 0..2 NOT NULL;

Flue

Answer 3

This blog describes a way to do it using constant values

In addition to the constants, the blog defines a subtype for valid colors.

SQL> declare
  2  RED constant number(1):=1;
  3  GREEN constant number(1):=2;
  4  BLUE constant number(1):=3;
  5  YELLOW constant number(1):=4;
  6  --
  7  VIOLET constant number(1):=7;
  8  --
  9  subtype colors is binary_integer range 1..4;
 10  --
 11  pv_var colors;
 12  --
 13  function test_a (pv_var1 in colors) return colors
 14  is
 15  begin
 16     if(pv_var1 = YELLOW) then
 17             return(BLUE);
 18     else
 19             return(RED);
 20     end if;
 21  end;
 22  --

Kremenchug

It doesn't - I found a blog and didn't read it as carefully as I should have. Follow the link in my revised answer and you will find how to do it. – Bouffard

Another approach by using a set. SQLPlus-friendly source code is also available for both examples. – Vizor

Thanks Zulu. I've added the crucial parts directly in the answer. – Bouffard

Answer 4

I have previously used the same approach as @mydogtom and @klas-lindbäck. I found this when I was trying to refresh my memory. However, the object approach suggested by @a-b-cade got me thinking. I agree with the problems described by @mydogtom (what is the value?) but it got me thinking is using an object was possible.

What I came up with was an approach that used an object with a single member property for the value of the enum and static functions for each possible value. I couldn't see how to combine with a subtype to get a real restriction on the value field, not to formally make it not-null. However, we can validate it in the constructor. The functional downside, compared to a "proper" enum (e.g. in Java) is that we can't stop someone directly updating the val property to an invalid value. However, as long as people use the constructor and the set_value function, it's safe. I'm not sure the overhead (both run-time in terms of creating an object and in terms of maintaining the objects, etc.) is worth it, so I'll probably keep using the approach described by @mydogtom but I'm not sure.

You could also have name as a property and set in in set_value (kind of like @a-b-cade's version) but that adds another property that could be updated directly and so another set of states where the val and name don't match, so I preferred the approach with name being a function.

An example usage of this could be (using my demo_enum type below):

procedure do_stuff(enum in demo_enum) is
begin
   if enum.eqals(demo_enum.foo()) then
      -- do something
   end if;
end do_stuff;

or

procedure do_stuff(enum1 in demo_enum, enum2 in demo_enum) is
begin
   if enum1.eqals(enum2) then
      -- do something
   end if;
end do_stuff;

What I did was define a base class, with as much as possible there: the actual val field, equals function for static values, set_value and to_string fucntions. Also name function, but this just be overridden (couldn't see how to formally make a member function abstract, so the base version just throws an exception). I'm using name also as the way to check the value is valid, in order to reduce the number of places I need to enumerate the possible values

create or replace type enum_base as object(
    -- member field to store actual value
    val integer,

    -- Essentially abstract name function
    -- Should be overridden to return name based on value
    -- Should throw exception for null or invalid values
    member function name return varchar2,

    --
    -- Used to update the value.  Called by constructor
    --
    member procedure set_value(pvalue in integer),
    
    --
    -- Checks the current value is valid
    -- Since we can't stop someone updating the val property directly, you can supply invalid values
    --
    member function isValid return boolean,
    
    --
    -- Checks for equality with integer value 
    -- E.g. with a static function for a possible value:   enum_var.equals( my_enum_type.someval() )
    --    
    member function equals(other in integer) return boolean,
        
    --
    -- For debugging, prints out name and value (to get just name, use name function)
    --      
    member function to_string return varchar2
    
    
) not instantiable not final;
/

create or replace type body enum_base is

    member function name return varchar2 is
    begin
        -- This function must be overriden in child enum classes.
        -- Can't figure out how to do an abstract function, so just throw an error
        raise invalid_number;
    end;
    
    member procedure set_value(pvalue in integer) is
        vName varchar2(3);
    begin
        self.val := pvalue;
        -- call name() in order to also validate that value is valid
        vName := self.name;                          
    end set_value;
    
            
    member function isValid return boolean is
        vName varchar2(3);
    begin
        begin
            -- call name() in order to also validate that value is valid
            vName := self.name;   
            return true; 
        exception
            when others then
                return false;
        end;            
    end isValid;
    
    
    member function equals(other in integer) return boolean is
    begin
        return self.val = other;
    end equals;
    
    member function to_string return varchar2 is
    begin 
        if self.val is null then
            return 'NULL';
        end if;

        return self.name || ' (' || self.val || ')';
    end to_string;
end;
/

In the actual enum class I have to define a constructor (which just calls set_value) and override the name function to return a name for each possible value. I then define a static function for each possible value that returns the integer index of that value. Finally I define an overload of equals that compares to another enum of the same type. If you wanted to attach other properties to each value then you an do so by defining additional functions.

create or replace type demo_enum under enum_base (
            
    -- Note: the name of the parameter in the constructor MUST be the same as the name of the variable.  
    -- Otherwise a "PLS-00307: too many declarations" error will be thrown when trying to instanciate
    -- the object using this constructor         
    constructor function demo_enum(val in integer) return self as result,
    
    --
    -- Override name function from base to give name for each possible value and throw 
    -- exception for null/invalid values
    --
    overriding member function name return varchar2,
    
    --
    -- Check for equality with another enum object
    --  
    member function equals(other in demo_enum) return boolean,

    
    --
    -- Define a function for each possible value
    --
    static function foo return integer,
    static function bar return integer
        
) instantiable final;
/

create or replace type body demo_enum is
    
    constructor function demo_enum(val in integer) return self as result is        
    begin   
        self.set_value(val);        
        return;     
    end demo_enum;
    
    
    overriding member function name return varchar2 is
    begin
        if self.val is null then
            raise invalid_number;
        end if;
        
        case self.val
            when demo_enum.foo() then
                return 'FOO';
            when demo_enum.bar() then
                return 'BAR';
            else 
                raise case_not_found; 
        end case;
    end;    
        
    member function equals(other in demo_enum) return boolean is
    begin
        return self.val = other.val;
    end equals;
            
    static function foo return integer is
    begin 
        return 0;
    end foo;
    
    static function bar return integer is
    begin 
        return 1;
    end bar;    

end;
/

This can be tested. I defined two sets of tests. one was a manual set of tests for this particular enum, also to illustrate usage:

--
-- Manual tests of the various functions in the enum
--
declare
    foo demo_enum := demo_enum(demo_enum.foo());
    alsoFoo demo_enum := demo_enum(demo_enum.foo());
    bar demo_enum := demo_enum(demo_enum.bar());
    
    vName varchar2(100);    
    
    procedure assertEquals(a in varchar2, b in varchar2) is
    begin
        if a <> b then
            raise invalid_number;
        end if;
    end assertEquals;
    
    procedure assertEquals(a in boolean, b in boolean) is
    begin
        if a <> b then
            raise invalid_number;
        end if;
    end assertEquals;
    
    procedure test(vName in varchar2, enum in demo_enum, expectFoo in boolean) is
    begin
        dbms_output.put_line('Begin Test of ' || vName);
        if enum.equals(demo_enum.foo()) then
            dbms_output.put_line(vName || ' is foo');
            assertEquals(expectFoo, true);
        else
            dbms_output.put_line(vName || ' is not foo');
            assertEquals(expectFoo, false);
        end if;
        
        if enum.equals(demo_enum.bar()) then
            dbms_output.put_line(vName || ' is bar');
            assertEquals(expectFoo, false);
        else
            dbms_output.put_line(vName || ' is not bar');
            assertEquals(expectFoo, true);
        end if;
                
        if enum.equals(foo) then
            dbms_output.put_line(vName || '.equals(vFoo)');
            assertEquals(expectFoo, true);
        else
            assertEquals(expectFoo, false);
        end if;
        
        if expectFoo then
            assertEquals(enum.name, 'FOO');
        else
            assertEquals(enum.name, 'BAR');
        end if;
        
        assertEquals(enum.isValid, true);
        
        case enum.val
            when demo_enum.foo() then
                dbms_output.put_line(vName || ' matches case foo');
            when demo_enum.bar() then
                dbms_output.put_line(vName || ' matches case bar');
            else
                dbms_output.put_line(vName || ' matches no case!!!');
        end case;
        
        dbms_output.put_line(vName || ': ' || enum.to_string());
        dbms_output.put_line('--------------------------------------------------');
        dbms_output.put_line('');
    end test;
begin    
    test('foo', foo, true);
    test('bar', bar, false);
    test('alsoFoo', alsoFoo, true);
        
    
    foo.val := -1;
    assertEquals(foo.isValid, false);    
    begin
        vName := foo.name;    
    exception
        when case_not_found then
            dbms_output.put_line('Correct exception for fetching name when invalid value: ' || sqlerrm);
    end;
    
    foo.val := null;
    assertEquals(foo.isValid, false);    
    begin
        vName := foo.name;    
    exception
        when invalid_number then
            dbms_output.put_line('Correct exception for fetching name when null value: ' || sqlerrm);
    end;
end;

The other was slightly more automated, and could be used for any enum that inherits enum_base (as long as it doesn't add other functions - couldn't see a way to find only static functions, if anyone knows let me know). This checks that you haven't defined the same integer value to multiple possible value static functions by mistake:

--
-- generated test that no two values are equal 
--
declare
    vSql varchar2(4000) := '';
    
    typename constant varchar2(20) := 'demo_enum';
    
    cursor posvals is   
        select procedure_name
        from user_procedures
        where object_name = upper(typename)
          and procedure_name not in (upper(typename), 'EQUALS', 'NAME');

    cursor posvals2 is   
        select procedure_name
        from user_procedures
        where object_name = upper(typename)
          and procedure_name not in (upper(typename), 'EQUALS', 'NAME');


    procedure addline(line in varchar2) is
    begin
        vSql := vSql || line || chr(10);
    end;
begin
    addline('declare');
    addline('   enum ' || typename || ';');
    addline('begin');
        
    for posval in posvals loop    
        addline('   enum := ' || typename || '(' || typename || '.' || posval.procedure_name || '());');    
        for otherval in posvals2 loop
            addline('   if enum.equals(' || typename || '.' || otherval.procedure_name || '()) then');
            if otherval.procedure_name = posval.procedure_name then                 
                addline('      dbms_output.put_line(''' || otherval.procedure_name || ' = ' || posval.procedure_name || ''');');
            else
                addline('      raise_application_error(-20000, ''' || otherval.procedure_name || ' = ' || posval.procedure_name || ''');');
            end if;
            addline('   else');
            if otherval.procedure_name = posval.procedure_name then                 
                addline('      raise_application_error(-20000, ''' || otherval.procedure_name || ' != ' || posval.procedure_name || ''');');
            else
                addline('      dbms_output.put_line(''' || otherval.procedure_name || ' != ' || posval.procedure_name || ''');');
            end if;
            addline('   end if;');
        end loop;        
        addline('');
    end loop;
    addline('end;');
    
    execute immediate vSql;
end;

Scoliosis

Answer 5

For example enum whith "yes" and "no" values.

CREATE OR REPLACE TYPE t_yes_no_enum AS OBJECT
( yes_no NUMBER
, CONSTRUCTOR FUNCTION t_yes_no_enum
  ( yes_no NUMBER
  )
  RETURN SELF AS RESULT
, STATIC FUNCTION yes
  RETURN t_yes_no_enum
, STATIC FUNCTION no
  RETURN t_yes_no_enum
, MAP MEMBER FUNCTION yes_no_map
  RETURN NUMBER
) ;
/
CREATE OR REPLACE NONEDITIONABLE TYPE BODY t_yes_no_enum
AS

CONSTRUCTOR FUNCTION t_yes_no_enum
( yes_no NUMBER
)
RETURN SELF AS RESULT
IS
BEGIN
  self.yes_no := yes_no ;

  IF  utl_call_stack.dynamic_depth > 1
  AND utl_call_stack.unit_type(2) = 'TYPE BODY'
  AND utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2)) LIKE 'T_YES_NO_ENUM.%'
  AND utl_call_stack.owner ( 2 ) = utl_call_stack.owner ( 1 )
  THEN
    NULL ;
  ELSE
    raise_application_error ( -20184 , 'Constructor calling prohibited. For object type creation use it''s static factory methods.' ) ;
  END IF ;

  RETURN ;
END ;

STATIC FUNCTION yes
RETURN t_yes_no_enum
IS
BEGIN
  RETURN NEW t_yes_no_enum ( 1 ) ;
END ;

STATIC FUNCTION no
RETURN t_yes_no_enum
IS
BEGIN
  RETURN NEW t_yes_no_enum ( 2 ) ;
END ;

MAP MEMBER FUNCTION yes_no_map
RETURN NUMBER
IS
BEGIN
  IF coalesce ( self.yes_no , -999 ) NOT IN ( 1 , 2 ) THEN
    raise_application_error ( -20185 , 'Changing object type attribute value prohibited.' ) ;
  END IF ;
  RETURN self.yes_no ;
END ;

END ;

Behold