OracleBlog

What is ENUM?

ENUM is short for enumeration. Its a useful programming type that contains an ordered list of string values. The programmer can define the valid values depending on their application.

Some good examples of ENUMs would be days and months, or something like directions ('North', 'South', 'East', 'West').

Is there an Oracle 'ENUM' type?

No, not really. But there are other ways of accomplishing the same thing.

For tables, just set it to a string and add a constraint that it is within a certain set.

CREATE TABLE atable (
col1 varchar2(10),
CONSTRAINT cons_atable_col1 CHECK (col1 IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday',
'Friday', 'Saturday', 'Sunday'))
);

SQL> INSERT INTO atable (col1) VALUES ('Monday');

1 row created.

SQL> INSERT INTO atable (col1) VALUES ('Blingday');
insert into atable (col1) values ('Blingday')
*
ERROR at line 1:
ORA-02290: check constraint (ROBERT.CONS_ATABLE_COL1) violated

What happens if you use this type in a procedure? Will the constraint be checked? No.

CREATE OR REPLACE PROCEDURE MyProc (in_col IN atable.col1%TYPE)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(in_col);
END;

SET SERVEROUTPUT ON;

EXEC MyProc('Monday');
EXEC MyProc('Blingday');

So can you create a package subtype for this? That would be more elegant anyway.

But according to Oracle PL/SQL Programming by Steven Feuerstein Chapter 4, I don't think you can (check comments for any refutations to this).

http://www.amazon.com/exec/obidos/ASIN/0596003811/
qid=1117039808/sr=2-1/ref=pd_bbs_b_2_1/102-9543590-3979349

I think the best thing to do in this case is to create a procedure to validate your input.

CREATE OR REPLACE PROCEDURE MyCheck (in_col IN atable.col1%TYPE)
AS
BEGIN
IF (in_col NOT IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')) THEN
-- Throw Exception here, be sure to catch it in MyProc!!
NULL;
END IF;
END;

This approach is consistent with Steven Feuerstein's approach to programming. He suggests separating these things into separate procedures. Then when a future release of Oracle supports a concept, or when you figure out how to do it, you can make the change in a single place.

So what is a guy to do?
1. If you want to use enum in a table, use a check constraint.
2. If you want to use enum in a stored procedure, write a separate procedure to validate the input.