Cascading LOVs in Oracle APEX - Country → State → City

✍️ Utkarsh Ranjan · Oracle APEX Certified Pro (1Z0-771) · ⏱ 8 min read · Beginner → Intermediate

Cascading dropdowns are one of the most common requirements in enterprise forms. When a user selects a Country, the State dropdown should update automatically. When they select a State, the City dropdown should update.

Most developers think this requires JavaScript. In Oracle APEX — it doesn't.

APEX has a built-in Cascading LOV feature that handles this automatically using:

• SQL-based List of Values

• Native "Cascading LOV Parent Item" setting

• Zero JavaScript required

In this blog I'll show you how to build a complete Country → State → City cascading dropdown from scratch.

What We Are Building

┌─────────────────────────────────┐
│  Country  [ India          ▼ ]  │
│  State    [ Uttar Pradesh  ▼ ]  │
│  City     [ Kanpur         ▼ ]  │
└─────────────────────────────────┘

When Country changes → State list refreshes automatically When State changes → City list refreshes automatically

No JavaScript. No Dynamic Actions. Just APEX configuration.

Step 1 - Create the Tables

First create the reference tables for our LOVs:

Step 2 - Insert Sample Data

Step 3 - Create the Page in APEX

1. App Builder → your app → Create Page

2. Select Blank Page

3. Name: Location Selector

4. Click Create Page

Step 4 - Add a Form Region

1. Page Designer → right-click Body → Create Region

2. Title: Select Your Location

3. Type: Static Content

4. Click Save 💾

Step 5 - Create the Country Dropdown

1. Right-click your region → Create Page Item

2. Fill in:

1. Right panel → List of Values section:

SQL Query for Country LOV:

SELECT country_name AS display_value,
       country_id   AS return_value
FROM   countries
ORDER BY country_name

1. Click Save 💾

Step 6 - Create the State Dropdown

This is where the magic happens — the Cascading LOV Parent Item setting:

1. Right-click region → Create Page Item

2. Fill in:

1. Right panel → List of Values section:

SQL Query for State LOV:

SELECT state_name AS display_value,
       state_id   AS return_value
FROM   states
WHERE  country_id = :P2_COUNTRY
ORDER BY state_name

💡 The :P2_COUNTRY bind variable is what links State to Country. When Country changes, APEX automatically re-runs this query with the new Country value.

1. Click Save 💾

   

Step 7 - Create the City Dropdown

Same pattern - cascades from State:

1. Right-click region → Create Page Item

2. Fill in:

1. Right panel → List of Values section:

SQL Query for City LOV:

SELECT city_name AS display_value,
       city_id   AS return_value
FROM   cities
WHERE  state_id = :P2_STATE
ORDER BY city_name

1. Click Save 💾

Step 8 - Run and Test

1. Click Save 💾

2. Click Run ▶️

3. Select India from Country dropdown

4. State dropdown automatically shows UP, Maharashtra, Delhi, Karnataka

5. Select Uttar Pradesh

6. City dropdown automatically shows Kanpur, Lucknow, Agra, Varanasi

No JavaScript. No Dynamic Actions. It just works! ✅

How It Works Behind the Scenes

When you set Cascading LOV Parent Item = P2_COUNTRY on the State item:

User selects Country = India (value = 1)
         ↓
APEX detects P2_COUNTRY changed
         ↓
APEX re-runs State LOV SQL with :P2_COUNTRY = 1
         ↓
SELECT state_name, state_id FROM states
WHERE country_id = 1  ← automatically substituted
         ↓
State dropdown refreshes with India's states ✅

APEX handles the AJAX call, the query execution, and the dropdown refresh all natively.

Bonus - Save Selected Values to Database

Add a Submit button and process to save the selection:

-- Create a table to store selections
CREATE TABLE user_locations (
    location_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country_id  NUMBER,
    state_id    NUMBER,
    city_id     NUMBER,
    created_by  VARCHAR2(100),
    created_on  DATE DEFAULT SYSDATE
);

Page Process on Submit:

Bonus - Show Selected Values as Text

To display the selected names (not IDs) in a confirmation area:

Common Issues and Fixes

Also:

1. You can chain more than 3 levels Country → State → City → Area → Pincode — just keep adding Cascading LOV Parent Item at each level.

2. Use Shared Components for LOVs If the same Country/State/City dropdowns are used on multiple pages, create them as Shared LOVs in Shared Components → List of Values. Reuse them across pages.

3. Clear child items when parent changes By default APEX clears child items when parent changes. But if you want to explicitly control this, add a Dynamic Action on P2_COUNTRY Change → Set Value → P2_STATE = null, P2_CITY = null.

4. Pre-select a default value Set Default → Type → Static Value on the Country item to pre-select a country when the page loads:

Default Value: 1  (India's country_id)

5. Use APEX_ITEM for tabular cascading For cascading dropdowns inside an Interactive Grid or tabular form, use APEX_ITEM.SELECT_LIST_FROM_QUERY with the cascading parameter.

Wrapping Up

Cascading LOVs in Oracle APEX are one of those features that make you appreciate how well-designed the framework is. A feature that would take hours of JavaScript in other frameworks takes 5 minutes of configuration in APEX.

The key things to remember:

• ✅ Use SQL Query type for your LOVs

• ✅ Always use bind variable :PARENT_ITEM in WHERE clause

• ✅ Set Cascading LOV Parent Item(s) on child items

• ✅ Set Null Display Value for better UX

• ✅ No JavaScript needed — ever

Follow me for more Oracle APEX, PL/SQL and Low-Code content.

OracleAPEX CascadingLOV SelectList LowCode OracleDatabase Tutorial PLSQL

Utkarsh Ranjan · Oracle APEX Certified Pro (1Z0-771) ·