Wednesday, May 25, 2005

ENUM in Oracle

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.

Comments:
later without having to find all of the "where x in ( ..... )" checks.

ops$tkyte@ORA10G> create or replace package my_pkg
2 as
3 type days is table of varchar2(30);
4 set_of_days days := days( 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun' );
5 end;
6 /

Package created.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create or replace procedure foo( p_day in varchar2 )
2 as
3 begin
4 if p_day MEMBER OF my_pkg.set_of_days
5 then
6 dbms_output.put_line( 'Tis OK' );
7 elsif NOT p_day MEMBER OF my_pkg.set_of_days
8 then
9 dbms_output.put_line( 'NOT ok' );
10 else
11 dbms_output.put_line( 'We are not sure here...' );
12 end if;
13 end;
14 /

Procedure created.

ops$tkyte@ORA10G> exec foo('Fri');
Tis OK

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> exec foo('xxx');
NOT ok

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> exec foo(null);
We are not sure here...

PL/SQL procedure successfully completed.
 
I personally think it would be better to call the procedure from a before insert trigger to remove the insertion of the datas
wat say ??
 
somthing new about ENUMs in PL/SQL by Pete Finnigan: http://www.petefinnigan.com/weblog/archives/00001246.htm
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?