Oracle APEX Date Range Parser

Overview

Oracle APEX Radio Group items often return compound values when configured with date ranges, such as 2024-01-01:2024-12-31. While it may be tempting to parse these values at the UI level using JavaScript or within individual page processes, this approach leads to code duplication, maintenance overhead, and inconsistent behavior across applications.

The get_date_range_parts procedure solves this problem by providing a centralized, reusable PL/SQL utility that extracts the FROM and TO date components at the database level. This ensures consistent parsing logic, improves maintainability, and adheres to Oracle best practices by keeping data manipulation close to the database layer.

Project Structure

This project provides a standalone SQL file containing a single PL/SQL procedure. The procedure is created once at the schema level and can be reused by any Oracle APEX application or SQL query within that schema.

apex-date-range-parser/
├── get_date_range_parts.sql    # PL/SQL procedure definition
├── README.md                    # Project documentation
├── LICENSE                      # Apache License 2.0
└── .gitignore                   # Git ignore rules

The PL/SQL Procedure

The get_date_range_parts procedure is a database-level object that lives in your Oracle schema. It is delivered via the get_date_range_parts.sql file and is installed once using SQL Workshop, SQL Developer, or SQL*Plus.

This procedure is NOT created inside individual APEX pages or reports. Instead, it is a shared utility that can be called from any APEX process, computation, or SQL query.

What This Procedure Does

The get_date_range_parts procedure accepts a single VARCHAR2 parameter containing a date range in the format FROM:TO and extracts the individual components:

• Input: A VARCHAR2 string representing a date range (e.g., 2024-01-01:2024-12-31)
• Output: Two OUT parameters containing the FROM and TO values
• NULL Handling: Returns NULL for both outputs if the input is NULL
• Flexible Parsing: Uses regular expressions to handle multiple delimiters if needed

The procedure uses Oracle regular expression functions (REGEXP_SUBSTR and REGEXP_COUNT) to reliably extract the date components without hardcoding assumptions about date formats.

Why This Matters in Oracle APEX

Oracle APEX Radio Group items are commonly used to provide users with predefined date range options such as "Last 30 Days", "This Quarter", or "Custom Range". When configured with return values like 2024-01-01:2024-12-31, the selected value is stored as a single compound string.

Parsing these values at the UI level using JavaScript or duplicating parsing logic across multiple page processes is bad practice because:

• Code Duplication: The same parsing logic must be repeated in every report or process
• Maintenance Overhead: Changes to the date format require updates in multiple locations
• Inconsistency: Different developers may implement parsing differently
• Performance: Database-level parsing is more efficient for SQL queries

By centralizing the parsing logic in a PL/SQL procedure, you gain:

• Reusability: Call the procedure from any APEX process or SQL query
• Consistency: All date range parsing follows the same logic
• Maintainability: Update the logic in one place
• Separation of Concerns: Keep data manipulation at the database layer

Installation

1. Open SQL Workshop in Oracle APEX, SQL Developer, or connect via SQL*Plus
2. Run the get_date_range_parts.sql file
3. Verify the procedure exists by querying USER_PROCEDURES or testing a simple call

-- Verify installation
SELECT object_name, object_type, status
FROM user_objects
WHERE object_name = 'GET_DATE_RANGE_PARTS';

Installation is a one-time operation per schema. Once installed, the procedure is available to all APEX applications in that schema.

Oracle APEX Use Case

Radio Group Configuration

Configure your Radio Group item with a List of Values that returns date ranges in the format YYYY-MM-DD:YYYY-MM-DD:

Display Value          | Return Value
-----------------------|---------------------------
Last 30 Days           | 2024-12-06:2025-01-05
This Month             | 2025-01-01:2025-01-31
This Quarter           | 2025-01-01:2025-03-31
This Year              | 2025-01-01:2025-12-31

Calling the Procedure in APEX

Use the procedure in a Before Header or After Submit process to extract the date components and store them in page items or bind variables:

DECLARE
    v_date_from VARCHAR2(50);
    v_date_to   VARCHAR2(50);
BEGIN
    get_date_range_parts(
        p_date_string => :P1_DATE_RANGE,
        p_date_from   => v_date_from,
        p_date_to     => v_date_to
    );
    
    :P1_DATE_FROM := v_date_from;
    :P1_DATE_TO   := v_date_to;
END;

Using in SQL Reports

Apply the extracted date range to filter Interactive Reports or Classic Reports:

SELECT employee_id,
       employee_name,
       hire_date,
       department
FROM employees
WHERE hire_date BETWEEN TO_DATE(:P1_DATE_FROM, 'YYYY-MM-DD')
                    AND TO_DATE(:P1_DATE_TO, 'YYYY-MM-DD')
ORDER BY hire_date DESC;

Example Usage

Complete APEX Integration Example

-- Step 1: User selects "This Quarter" from Radio Group P1_DATE_RANGE
-- Return value: 2025-01-01:2025-03-31

-- Step 2: Before Header process extracts the components
DECLARE
    v_from VARCHAR2(50);
    v_to   VARCHAR2(50);
BEGIN
    get_date_range_parts(:P1_DATE_RANGE, v_from, v_to);
    :P1_START_DATE := v_from;
    :P1_END_DATE   := v_to;
END;

-- Step 3: Interactive Report uses the extracted values
SELECT order_id,
       order_date,
       customer_name,
       total_amount
FROM orders
WHERE order_date BETWEEN TO_DATE(:P1_START_DATE, 'YYYY-MM-DD')
                     AND TO_DATE(:P1_END_DATE, 'YYYY-MM-DD');

Design Principles

This utility is built on the following principles:

• Reusability: Write once, use everywhere across all APEX applications
• Separation of Concerns: Keep data parsing logic at the database layer
• Readability: Clear parameter names and straightforward logic
• Maintainability: Centralized logic that is easy to update
• Production Safety: NULL handling and robust regular expression parsing

When to Use This Approach

This procedure is ideal for:

• Interactive Reports: Filter data based on user-selected date ranges
• Classic Reports: Apply date range filters to SQL queries
• Dashboards: Provide consistent date filtering across multiple regions
• Any Compound-Value UI Filter: Extend the pattern to other delimited values (e.g., MIN:MAX, START:END)

Compatibility

• Oracle Database: Version-agnostic (uses standard PL/SQL and regular expressions available in Oracle 10g+)
• Oracle APEX: Version-agnostic (works with all modern APEX versions)

License

This project is licensed under the Apache License 2.0. It is open source and free to use, modify, and distribute in both personal and commercial projects.

See the LICENSE file for full details.