Oracle 23ai: Enumeration Domains – List of Values in the Database

Share on:

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);

ai

  • 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'
);

ai2

 

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

ai3

 

3. Insert rows into 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 

ai4

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

ai5

ai6

 

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

ai7

 

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

ai8

ai9

ai10

Share on:

More from this Author

Oracle 23ai JSON Schema Support

Oracle 23ai: JSON Schema Support

Overview: Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data in Oracle 12.2 but without the option to ... Read More

Oracle 23ai Quick Overview

Oracle 23ai: Quick Overview

Oracle Database 23ai Oracle Database 23ai is the next long-term support release of Oracle Database It brings AI to your data with the addition of AI ... Read More

Back to Top