How to use enums in Oracle?

Reading a bit about the MySQL enum, I'm guessing the closest equivalent would be a simple check constraint

CREATE TABLE sizes (
  name VARCHAR2(10) CHECK( name IN ('small','medium','large') )
);

but that doesn't allow you to reference the value by the index. A more complicated foreign key relationship would also be possible

CREATE TABLE valid_names (
  name_id   NUMBER PRIMARY KEY,
  name_str  VARCHAR2(10)
);

INSERT INTO valid_sizes VALUES( 1, 'small' );
INSERT INTO valid_sizes VALUES( 2, 'medium' );
INSERT INTO valid_sizes VALUES( 3, 'large' );

CREATE TABLE sizes (
  name_id NUMBER REFERENCES valid_names( name_id )
);

CREATE VIEW vw_sizes
  AS 
  SELECT a.name_id name, <<other columns from the sizes table>>
    FROM valid_sizes a,
         sizes       b
   WHERE a.name_id = b.name_id

As long as you operate through the view, it would seem that your could replicate the functionality reasonably well.

Now, if you admit PL/SQL solutions, you can create custom object types that could include logic to limit the set of values they can hold and to have methods to get the IDs and to get the values, etc.

Why not use a constraint for the column? It will do the same thing:

ALTER TABLE x ADD CONSTRAINT size_constraint check (x_size in ('small', 'medium', 'large'))

At this link you can find an alternative solution/workaround for Oracle, inspired by C language enums: http://www.petefinnigan.com/weblog/archives/00001246.htm

Shortly put, Pete suggests to define some integer constants and to use a SUBTYPE to constrait them:

RED constant number(1):=1;
GREEN constant number(1):=2;
BLUE constant number(1):=3;
YELLOW constant number(1):=4;

subtype COLORS is binary_integer range 1..4;

After that you can declare variables, pass parameters and return values from functions and so on, with type COLORS.