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

Answer 1

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;

Answer 2

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

Answer 3

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;