How Do I Create a List of Values (LOV) in Oracle APEX

Introduction

Creating a List of Values (LOV) in Oracle APEX is a fundamental technique for building interactive and user-friendly applications. LOVs provide users with predefined options to choose from, making data entry faster, more accurate, and consistent. Whether you need a simple static list or a dynamic list sourced from your database, Oracle APEX offers flexible methods to create and manage LOVs. This blog will guide you through the process of creating LOVs and using them effectively in your applications.

 Introduction to List of Values (LOV) in Oracle APEX

A List of Values (LOV) in Oracle APEX is a set of predefined options—either static or dynamic—that populate components such as select lists, radio groups, checkbox groups, popup LOVs, and more. LOVs simplify user input by allowing users to choose from a controlled list of values, enhancing both usability and data accuracy.

Types of LOVs in Oracle APEX
Oracle APEX offers several types of LOVs, including:

• Static LOV: Values are manually specified within APEX.

• Dynamic LOV: Values are retrieved dynamically through database queries.

• Shared LOV: LOVs that are created once and can be reused across multiple components throughout the application.

To create both static and dynamic Lists of Values (LOVs) in Oracle APEX, follow this step-by-step guide. We’ll use a practical example based on a table named USCG_DATA, which stores earthquake event information. Our goal is to let users select an event using a dropdown list that shows the event title but stores its corresponding ID in the database.

Step 1: Create a Dynamic LOV in Oracle APEX
Dynamic LOVs retrieve their values directly from a SQL query. This is useful when you want the list to reflect up-to-date data from your tables.

1. Open your application in Oracle APEX.

2. Go to Shared Components > List of Values.

3. Click Create > From Scratch.

4. Enter the LOV name:
   LOV_Earthquake_Events

5. For Type, select Dynamic.

6. In Source Type, choose SQL Query and enter the following SQL:

SELECT PROPERTIES_TITLE AS display_value, ID AS return_value  
FROM USCG_DATA  
ORDER BY PROPERTIES_TITLE;

• PROPERTIES_TITLE is what the user will see in the dropdown.

• ID is the value stored in the database.

7. Click Create LOV to save.

Step 2: Use the LOV in a Page Item
Now, attach the LOV to a page item, such as a select list on a form.

1. Open a form page in your application.

2. In the Page Designer, add a new page item or select an existing one.

3. Set Type to Select List, Popup LOV, or another LOV-supported item type.

4. Set the Name to something like P1_EVENT_ID.

5. Under the List of Values section, select LOV_Earthquake_Events.

6. Optionally, enable Display Extra Values if the selected value might not always be present in the LOV source.

7. Save and run the page.
   Now, the dropdown will show earthquake titles but store their corresponding IDs in the database.

Step 3: Create a Static LOV
Static LOVs are useful when your list contains a fixed set of values that won’t change often.

1. Go to Shared Components > List of Values.

2. Click Create > Static Values.

3. Enter the LOV name (e.g., LOV_Magnitude_Levels).

4. Add your static entries:

   • Label: Low Magnitude, Value: LOW

   • Label: Medium Magnitude, Value: MEDIUM

   • Label: High Magnitude, Value: HIGH

5. Click Create LOV to save.

To use this static LOV:

1. Go to a form page in Page Designer.

2. Add a new page item or select an existing one.

3. Choose Select List, Radio Group, or Checkbox Group as the item type.

4. Set the List of Values to the newly created static LOV (LOV_Magnitude_Levels).

5. Save and run the page.

Both types of LOVs help streamline user input, reduce entry errors, and provide a consistent interface for selecting data. Static LOVs are simple and fast to configure, while dynamic LOVs offer flexibility and automatic updates as your data changes.

Conclusion
By creating and using Lists of Values in Oracle APEX, you enhance the user experience and ensure data consistency throughout your application. Whether static or dynamic, LOVs simplify user input and reduce errors by limiting choices to valid options. Mastering LOV creation empowers you to build more intuitive, efficient, and professional Oracle APEX applications.