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 the 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 the incidents table severity column.   

Prerequisites:

  • Oracle Database 23ai

 

Demo 

1. Create an enumeration domain using the below command

create domain <Domain Name> as enum (< comma separated list of values >);
  • In this demo, I’ll create two enumeration domains
  • The first domain will provide a list of error message severity. It starts with “Emergency” having a value of 1 to “Debug” which has a value of 8 Domain will provide a list of number 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 message severity. It starts with “Emergency” having the value “emerg” to “Debug” which has the value of “debug“. The domain will provide a list of character 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 an incidents table where the severity column uses the first domain (list of numbers values)

Notice that the severity column’s data type is NUMBER

3. Insert rows into the incidents table

  • Remember that the 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 the severity column

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

Notice that the severity column’s data type is VARCHAR2

5. Insert rows into the incidents table

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