How to Use Enumeration Domains in Oracle

Columns with a fixed list of values, such as cabins, statuses, or priorities, are usually written as a VARCHAR2 with a CHECK constraint, and the meaning of each code lives in documentation. An enumeration domain, new in Oracle AI Database 26ai, is a proper enumerated type: a list of names with values that columns can use, SQL can refer to by name, and queries can read as a table.

Code for This Guide

The main examples are in the examples/schema-objects folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create domain domain as enum (name [= value] [, ...])

domain.name         -- refers to a value in SQL
select * from domain  -- lists the names and values

Without explicit values, the names are numbered from 1: enum (low, medium, high) gives 1, 2, and 3.

Define and Query an Enumeration

Example:

create domain cabin_d as enum (business = 'B', premium = 'P', economy = 'E');

select * from cabin_d;
select cabin_d.business as code from dual;

Output:

Domain CABIN_D created.

ENUM_NAME    ENUM_VALUE
____________ _____________
BUSINESS     B
PREMIUM      P
ECONOMY      E

CODE
_______
B

The domain is itself queryable, returning ENUM_NAME and ENUM_VALUE, and cabin_d.business returns the code B. The example drops the domain afterward.

Use It for a Column

A column declared with the enumeration accepts only its values, given by name or directly.

Example:

create table seat_offers (offer_id number, cabin cabin2_d);
insert into seat_offers values (1, cabin2_d.business), (2, 'E');
select offer_id, cabin from seat_offers order by offer_id;
insert into seat_offers values (3, 'X');

Output:

Table SEAT_OFFERS created.

2 rows inserted.

   OFFER_ID CABIN
___________ ________
          1 B
          2 E

Error starting at line : 4
In command -
insert into seat_offers values (3, 'X')
Error report -
ORA-11534: check constraint (NIMBUS.SYS_C0018076) involving column CABIN due to
domain constraint NIMBUS.SYS_DOMAIN_C0060 of domain NIMBUS.CABIN2_D violated

cabin2_d.business stores B, and 'E' is accepted as a valid value, but 'X' fails with ORA-11534. The example creates and drops its own domain and table.

Things to Know

• The column stores the value, not the name, so it is as compact as a code column.
• Join the domain like a table to show names next to stored values.
• Adding a value changes the domain for every column that uses it.

Related Guides

• How to Create Domains in Oracle

Conclusion

Enumeration domains define a named list of values that columns accept, SQL refers to as domain.name, and queries read as a table. They replace code columns with CHECK constraints and keep the meaning of each code in the database.