What Is Enum Data Type in Oracle?

What Is Enum Data Type in Oracle?

In Oracle, the ENUM data type is not natively supported. However, you can simulate an enumeration-like behavior using a combination of check constraints and user-defined types. This allows you to define a predefined set of values that a column can store.

Creating an Enum-Like Data Type

To create an enum-like data type in Oracle, you need to follow these steps:

1. Create a new object type using the CREATE TYPE statement.
2. Add the desired values as attributes to the object type.
3. Create a table with a column of the newly created object type.
4. Add a check constraint to enforce the predefined values for the column.

Let’s walk through an example to understand this better:

Step 1: Create Object Type

To create an object type, you can use the following syntax:

CREATE TYPE enum_type AS OBJECT (
   value VARCHAR2(20)
);

The above code creates an object type called enum_type. It has a single attribute called value, which will store the allowed values for our enum-like data type.

Step 2: Add Values as Attributes

In this step, we will add our desired values as attributes to the object type. For example, let’s say we want our enum-like data type to have three possible values: ‘ACTIVE’, ‘INACTIVE’, and ‘PENDING’.

ALTER TYPE enum_type ADD ATTRIBUTE (
   ACTIVE VARCHAR2(20),
   INACTIVE VARCHAR2(20),
   PENDING VARCHAR2(20)
);

The above code adds the attributes ACTIVE, INACTIVE, and PENDING to the enum_type.

Step 3: Create Table with Enum-Like Column

In this step, we will create a table that has a column of our newly created enum-like data type.

CREATE TABLE my_table (
   status enum_type
);

The above code creates a table called my_table. It has a single column called status, which is of type enum_type.

Step 4: Add Check Constraint for Valid Values

To enforce the predefined values for the column, we need to add a check constraint.

ALTER TABLE my_table
ADD CONSTRAINT valid_status_values
CHECK (status.value IN ('ACTIVE', 'INACTIVE', 'PENDING'));