To list the days of the week with Oracle SQL, you could write a query to

This looks like:

You can use a similar SQL statement creating twelve rows to list the months of the year.

Oracle AI Database 26ai introduces a new way to create lists of values: enumeration (enum) domains.

Oracle AI Database 26ai replaces Oracle Database 23ai and is now generally available. Learn more about the SQL improvements in Oracle AI Database 26ai.

These enable you to:

For an overview, grab the enum cheat sheet at the end of this post.

Create name-value pair lists of values with enumeration domains

To create an enumeration domain, use create [usecase] domain <domain_name> as enum and provide a list of names. This assigns the values to each name in the order listed, starting with one. Each name has a value one higher than the previous.

DDL to create an enumeration domain for the months of the year

This creates a use case domain listing the months in the year. It starts with January having the value one through to December which has the value twelve:

The names follow the rules for database identifiers. By default, they are case insensitive. If you want case sensitive names, as with table names place them in double quotes.

To save you effort, Oracle AI Database 26ai has built-in domains for months of the year and days of the week: month_enum_d and day_enum_d. These return the months Jan-Dec and days Mon-Sun with the values 1-12 and 1-7 respectively.

But what if you want to use different values for each day or month?

You can change the starting number by setting the first item’s value equal to a specific number. The values after this increase by one for each item.

For example, to create an enum for the days of the week from Monday to Sunday with the values zero to six, use:

Instead of numeric values for the days of the week, you may want to use the two letter abbreviations (MO, TU, WE, etc.) as the value. You might also want to associate the three letter shortening (mon, tue, etc.) with these.

To do this provide a list of names separated by equals, followed by the value they’re equal to. For example:

You can use strings, dates or non-consecutive numbers for the values. For example, this creates an enum for the months in a fiscal year running from June 2023-May 2024, with the last date in each month as its value:

SQL to create an enum for the days of the week and query to see its values

 

Select from a list of values in Oracle SQL with enums

To view the names and values for an enum domain, you can query it like a regular table:

This returns a row for each name in the enum and its associated value in the columns enum_name and enum_value respectively. As it acts like a table, you can join enums to other tables on either their name or value.

This “acts as a table” property for enums only applies to queries. You can’t insert, update or delete the entries in an enumeration domain:

Changing the names or values in an enum is unsupported. If you want to modify these, you must drop and recreate the domain.

Limit columns to a list of values with enums

Like other data use case domains, you can associate enums with table columns. With enums in place, you can use it as the data type for a column when creating tables. The column has the same data type as the values in the domain.

For example, this creates a table storing school schedules for classes:

The scheduled_day column uses the days_of_week domain to say which day the class runs on. The start_month uses months_of_year to store when the class begins.

As with other use case domains, you can also remove and apply an enum to existing columns with alter table:

Enums have an implicit check constraint. The database applies this to the column when you associate the domain. This ensures you can only store the enum’s values in the column.

For example, the class_schedules table above uses the days_of_week and months_of_year domains. This means you can only store the two-letter day abbreviation in scheduled_day and the numbers 1-12 in the start_month:

Enums help give meaning to basic values. You can take advantage of this by using enum names instead of literal values in SQL statements.

Use named constants in Oracle SQL with enumeration domains

You can use an enum’s names in SQL statements. This returns the value associated with the key. This means you can insert a name and the database stores its linked value.

Examples of using enum names to act like constants in SQL

For example, these statements are all equivalent:

Similarly, you can use the enum names in update statements:

You can use enums in select statements too. For example, this finds the classes starting in May and classifies whether they’re held on a weekday (Mon-Fri) or the weekend (Sat, Sun):

This improves the readability of your SQL statements. When querying enum columns in a table, you only see their values which can make them hard to interpret. Luckily, you can get the name corresponding to these using domain_display.

View enum names for stored values

To convert enum values stored in a table to their name, pass the column to domain_display:

You can also do this for literal values. To do this you must first cast the values to the domain type:

To see if a value is in an enum, you can pass it to domain_check:

Enumeration domains cheat sheet

Enumeration domains in Oracle AI Database 26ai are a fantastic way to create lists of values in the database. These enable you to query the list, access the values like constants, and limit values in a table. This helps make your SQL easier to understand and manage while keeping your code simple.

Here’s a how-to cheat sheet for using enums in your database:

How to cheat sheet for enumeration domains

Download Oracle AI Database 26ai

Download Oracle AI Database 26ai Free and try this out for yourself.

UPDATE 20 Oct 2025: Oracle AI Database 26ai replaces Oracle Database 23ai