How Do I Add a Dropdown List in Oracle APEX

 

Introduction

Dropdown lists are a fundamental part of building user-friendly web applications in Oracle APEX. They allow users to select from a predefined set of options, ensuring data consistency and simplifying input. Whether you’re working with static values or pulling data from a table, adding a dropdown list (also called a select list) is quick and highly customizable. In this guide, we'll walk through how to create a dropdown list step-by-step using both static and dynamic data sources.

Purpose

A dropdown list (also known as a select list) allows users to choose a single value from a list of options. This is commonly used for selecting items like departments, statuses, or categories.

Step-by-Step Guide: Adding a Dropdown (Select List)

Step 1: Go to Page Designer

1. Open your Oracle APEX application.

2. In the App Builder, click on the page where you want to add the dropdown.

3. Click Page Designer to edit the layout.

Step 2: Create the Dropdown Item

1. In the Layout pane, select the region (or create a new one) where you want the dropdown to appear.

2. Right-click the region, and choose Create Page Item.

3. In the Type dropdown, select Select List.

4. Enter a name, such as P1_STATUS or P1_DEPT_ID.

5. Set a Label (e.g., "Select Status").

Step 3: Define the List of Values (LOV)

You must now define the list of choices that appear in the dropdown.

Option A: Static Values

1. In the Settings section for the Select List, go to List of Values.

2. Choose Type: Static Values.

3. Click Edit Static Values.

4. Add entries like this:

   Display Value	Return Value
   Active	A
   Inactive	I
   Pending	P

5. Click OK to save.

Option B: Dynamic SQL Query

1. Set Type: SQL Query.

2. Enter a SQL statement, such as:

   SELECT department_name d, department_id r
   FROM departments
   ORDER BY department_name
   

   • d is the display value (shown in dropdown)

   • r is the return value (stored in the item)

3. Optionally check Display Null Value if you want an empty default option.

Step 4: Set Default Value (Optional)

• Under the Source section, set a Default value if needed.

• You can use a static value, an SQL query, or PL/SQL.

Step 5: Save and Run

1. Click Save (or press Ctrl + S).

2. Click Run to preview your page.

3. The dropdown should now be visible and working.

Notes

• The dropdown item’s value can be referenced using :P1_STATUS or :P1_DEPT_ID in SQL or PL/SQL.

• You can use it as a filter in reports or as an input for a form.

• Dropdown items can be made dependent using Cascading LOVs if needed.

Summary

To add a dropdown list in Oracle APEX:

1. Open Page Designer.

2. Create a new Select List page item.

3. Define the List of Values using static entries or a SQL query.

4. Optionally set a default value.

5. Save and run the application to test it.

Certainly. Below is an introduction and conclusion paragraph written in plain text, using Arial font (if supported by the display environment), for a blog post titled:

Conclusion

Adding a dropdown list in Oracle APEX is a straightforward yet powerful way to enhance user interaction and control input values. Whether you're filtering reports, capturing form inputs, or driving conditional logic, the select list component integrates easily with the rest of your application. By understanding how to configure it properly, you can build more dynamic, efficient, and user-friendly pages with minimal effort.

Example

• Add a “Select One” dropdown box.

• Select the List of Values to SQL Query

• Add the SQL code

In this case we’re using the DEPT table from APEX

The query is as follows:

select 

    DEPT.DEPTNO || ' ' || DEPT.LOC as LOC   , DEPT.DNAME as DNAME

 from DEPT DEPT;

The dropdown displays as follows: