How Do I Create The STATES LOV

Creating a well-designed List of Values (LOV) in Oracle APEX is essential for improving user input accuracy and consistency, especially when dealing with standardized data like US states. In this blog, we will explore how to create a States LOV step-by-step, covering both static and dynamic approaches. By following these detailed instructions, you will learn how to implement a reusable dropdown list that enhances your application’s usability and data integrity.

To create a States LOV (List of Values) in Oracle APEX, follow these detailed steps:

1. Understand what a LOV is
   A LOV is a dropdown or popup list that lets users select a value from predefined options. For states, it typically shows the state name but stores a short code (like state abbreviation).

2. Decide on LOV type: Static or Dynamic

• Static LOV: hard-coded list inside APEX. Good for small, fixed lists like US states.

• Dynamic LOV: SQL query pulling data from a database table. Useful if you have or want a states table.

3. Create the US States table (if needed)
   If you want a dynamic LOV, create a table to hold states:

CREATE TABLE US_States (
  State_Abbreviation VARCHAR2(2) PRIMARY KEY,
  State_Name VARCHAR2(100)
);

INSERT INTO US_States (State_Abbreviation, State_Name) VALUES ('AL', 'Alabama');
INSERT INTO US_States (State_Abbreviation, State_Name) VALUES ('AK', 'Alaska');
-- Add remaining states here
COMMIT;

4. Create a Static LOV

• Go to Shared Components > List of Values

• Click Create > From Scratch

• Enter a name, for example: LOV_STATES

• Select Static as LOV Type

• Enter values in this format (one per line):

  Alabama;AL
  Alaska;AK
  Arizona;AZ
  Arkansas;AR
  California;CA
  

• Save the LOV.

5. Create a Dynamic LOV (Using SQL Query)

• Go to Shared Components > List of Values

• Click Create > From Scratch

• Name the LOV, for example: LOV_STATES

• Choose SQL Query as LOV Type

• Enter this query:

  SELECT State_Name AS display_value, State_Abbreviation AS return_value
  FROM US_States
  ORDER BY State_Name
  

• Save the LOV.

6. Use the LOV in your application

• In your page, create or edit an item (like a select list)

• Under List of Values, select your created LOV (LOV_STATES)

• Set the Return Value type to match your LOV return (usually VARCHAR2(2) for state abbreviation)

• Optionally, set a Null Display Value such as -- Select State -- to prompt users to pick one

7. Test your LOV
   Run the page and check the dropdown to ensure all states appear correctly, and selecting a state stores the correct abbreviation.

This is the standard approach in Oracle APEX to create a robust, reusable States LOV, improving user experience by ensuring consistent state selection across your application.

Example:

A List of Values (LOV) in Oracle APEX can be utilized by both page items (such as select lists, checkboxes, or radio groups) and report fields, controlling the displayed options and restricting user selections. LOVs can be defined as either static or dynamic:

• Static LOVs: Based on predefined pairs of display and return values entered by the developer. 

• Dynamic LOVs: Retrieve data from various data sources, including:

• Local Database

• REST Enabled SQL

• REST Data Source

Dynamic LOVs automatically reflect changes in the underlying data, ensuring that users have access to the most current information. 

When selecting a type of list of values, you have two primary options:

Dynamic List of Values: This type of list is generated based on a data source, which can be:

• Local Database

• REST Enabled SQL

• REST Data Source

Dynamic lists automatically update to reflect changes in the underlying data, ensuring that the information remains current without manual intervention.

Static List of Values: A static list consists of predefined display and return values. Once established, these lists remain unchanged unless manually updated. They are useful when the set of values is fixed and not subject to frequent changes. 

docs.oracle.com

Choosing between dynamic and static lists depends on whether you require real-time data updates or a fixed set of values.

When configuring a new List of Values (LOV) in Oracle Application Express (APEX), you can select from three primary data source types:

1. Local Database:

• Description: Retrieves data directly from tables or views within the local Oracle database associated with your APEX application.

• Usage: Ideal for scenarios where the required data resides within the same database environment as your application.

2. REST Enabled SQL Service:

• Description: Accesses data from a remote Oracle database using REST-enabled SQL services, allowing SQL queries to be executed over HTTP or HTTPS.

• Setup: To utilize this option, define REST Enabled SQL references by navigating to Shared Components > REST Enabled SQL in your APEX application.

• Usage: Suitable for integrating data from external Oracle databases without direct database links, leveraging RESTful web services for data retrieval.

3. REST Data Source:

• Description: Fetches data from external RESTful web services, enabling integration with various web APIs and services.

• Setup: Define REST Data Sources within your application by going to Shared Components > REST Data Sources.

• Usage: Appropriate for incorporating data from third-party APIs or services that expose RESTful endpoints.

Selecting the appropriate data source type for your LOV depends on the location and nature of the data you intend to present, ensuring efficient and effective data retrieval within your APEX application.

In Oracle APEX, when configuring a List of Values (LOV) that utilizes a Local Database as its data source, you can base it on the following:

• Table: Directly reference a specific database table to populate the LOV.

• SQL Query: Use a custom SQL query to define the LOV's data set.

• PL/SQL Function Returning SQL Query: Employ a PL/SQL function that returns a SQL query, offering dynamic data retrieval capabilities.

• Property Graph: (Available in Oracle Database 23c and later) Leverage a property graph to define the LOV, enabling graph-based data representations.

These options provide flexibility in sourcing data for your LOVs within APEX applications.

Specify the column that will serve as the return value for your List of Values. 

Specify the column to be used as the display value for your List of Values (LOV). After creating the LOV, you can define additional display columns for item types that support multiple columns, such as the Popup LOV. 

In conclusion, creating a States LOV in Oracle APEX is a straightforward process that can greatly enhance your application’s user experience. Whether you choose a static list for simplicity or a dynamic SQL-based LOV for flexibility, understanding the creation and application of LOVs is a valuable skill. By integrating this feature, you ensure accurate data entry and provide users with an intuitive way to select states throughout your application.