Oracle 23ai: Enumeration domains - List of Values in the Database

 

- Overview:

• Oracle Database 23ai introduces a new way to create lists of values: enumeration (enum) domains.
• Benefits:
• Create lists of name-value pairs in the database.
• Query the enum as a list of values.
• Limit column values to those in the enum list.
• Use the enum names as constants in SQL statements.
• Display the name of enum values.
• To create an enumeration domain, use create domain as enum command 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.
• By default, they are case insensitive. If you want case sensitive names, as with table names place them in double quotes.
• 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.

In this blog, I'll demonstrate the steps to create error message severity name-value pair lists of enums and associate enums with incidents table severity column.   

Prerequisites:

• Oracle Database 23ai.

Demo 

1. Create an enumeration domain using below command.

     create domain <Domain Name> as enum (< comma separated list of values >);

    - In this demo, I'll create two an enumeration domains.

    - The first domain will provide a list of error messages severity. It starts with "Emergency" having the value of 1 to "Debug" which has the value of 8. Domain will provide a list of numbers values.

create domain err_msg_severity as enum (

Emergency, Alert, Critical, Error,

Warning, Notice, Informational, Debug);

    - The second domain will provide the same list of error messages severity. It starts with "Emergency" having the value "emerg" to "Debug" which has the value of "debug". Domain will provide a list of characters values.

create domain err_msg_severity_2 as enum (

Emergency = 'emerg', 

Alert = 'alert', 

Critical = 'crit', 

Error = 'error',

Warning = 'warn', 

Notice = 'notice', 

Informational = 'info', 

Debug = 'debug'

);

2. Create incidents table where severity column uses the first domain (list of numbers values).

     Notice that severity column's data type is NUMBER.

3. Insert rows into incidents table. 

    - Remember that severity column value should be between 1 and 8. Assigning a value not between 1 and 8 will raise ORA-11534.

ORA-11534: check constraint (HR.SYS_C0013233) involving column SEVERITY due to domain constraint 

     - You can use <DOMAIN_NAME>.<ENUM_NAME> when providing a value to severity column.

4. Drop and recreate incidents table where severity column uses the second domain (list of characters values).

     Notice that severity column's data type is VARCHAR2.

5. Insert rows into incidents table. 

    - Remember you need to use <DOMAIN_NAME>.<ENUM_VALUE> when assigning a value to severity column or insert/update command will raise ORA-11534.