Web Application Development

Web Application Development

Date Range Validation in Web Applications

Problem

Applications requiring date range inputs need validation to ensure dates are valid, properly formatted, and logically consistent. Users may enter invalid dates, dates in wrong formats, or dates that create logical conflicts (like an end date before a start date). Without validation, corrupted data enters the database causing reporting errors and operational problems.

Solution

Implement both client-side and server-side validation. Client-side validation provides immediate feedback using dynamic actions that check date format and logical relationships as users type. Server-side validation using PL/SQL expressions ensures data integrity even if client-side checks are bypassed. Use APEX's built-in date picker components with format masks, minimum/maximum date constraints, and validation error messages that clearly explain requirements.

Implementation

Server-Side Validation:

-- PL/SQL Expression Validation
:P_END_DATE >= :P_START_DATE

Client-Side Dynamic Action: Set minimum date on end date picker when start date changes using JavaScript expression and Set Value action.

Admin Override for Restricted Operations

Problem

Systems typically restrict certain operations when capacity is reached or deadlines pass. However, administrators occasionally need to perform these restricted operations for legitimate exceptions—late entries, special accommodations, or administrative corrections. Without override capability, administrators must manually manipulate database records, creating security risks and data integrity issues.

Solution

Implement role-based authorization that allows administrators to bypass operational restrictions while maintaining audit trails. Create conditional logic that checks both operation status and user role before blocking actions. Use APEX authorization schemes to show admin-only buttons or form fields. Add database triggers or application logic to log all override actions with administrator ID and timestamp for accountability.

Implementation

Authorization Scheme:

-- Create authorization scheme checking user role
SELECT COUNT(*)
FROM user_roles
WHERE username = :APP_USER
  AND role_name = 'ADMIN'

Conditional Operation Logic:

-- Allow operation if status open OR user is admin
IF (record_status = 'OPEN' AND current_count < max_capacity)
   OR is_admin(:APP_USER) THEN
    INSERT INTO operations...

    -- Log override if admin bypassed restrictions
    IF record_status = 'CLOSED' THEN
        INSERT INTO admin_override_log...
    END IF;
END IF;

UI Element Authorization: Apply authorization scheme to admin-only buttons and form regions using Server-side Condition type "Authorization Scheme".

Duplicate Entry Prevention

Problem

Without duplicate detection, users can submit the same record multiple times, either accidentally through multiple form submissions or intentionally. This creates inaccurate counts, wastes limited capacity, complicates tracking, and generates data inconsistencies in reporting. Manual cleanup of duplicate entries is time-consuming and error-prone.

Solution

Implement database-level unique constraints combined with application-level validation checks. Create a composite unique constraint on the table using relevant ID columns. Add pre-insert validation in APEX that queries existing records before allowing submission. Provide clear error messaging when duplicates are detected. Use database triggers as an additional enforcement layer to prevent duplicates even if application logic is bypassed.

Implementation

Database Unique Constraint:

ALTER TABLE user_operations
ADD CONSTRAINT uk_user_operation
UNIQUE (user_id, operation_id);

APEX Validation:

-- PL/SQL Function Body returning Boolean
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM user_operations
    WHERE user_id = :P_USER_ID
      AND operation_id = :P_OPERATION_ID;

    RETURN v_count = 0;
END;

Status Tracking with Checkbox Column

Problem

Administrators need a simple method to record status changes for tracked records. Manual status tracking through separate forms or spreadsheets is time-consuming and creates data synchronization issues. Without integrated tracking, generating status reports requires manual data reconciliation, and historical records may be incomplete or inaccurate.

Solution

Add a status boolean column to the tracking table. Implement an interactive grid or report in APEX that displays all records with an editable checkbox column for status. Use APEX's built-in row-level DML operations to update status records directly from the grid. Restrict checkbox editing to administrators using authorization schemes. Include timestamp and user tracking columns to maintain an audit trail of status modifications.

Implementation

ALTER TABLE operations
ADD (
    completed VARCHAR2(1) DEFAULT 'N',
    completed_by VARCHAR2(100),
    completed_date TIMESTAMP
);

ALTER TABLE operations
ADD CONSTRAINT chk_completed CHECK (completed IN ('Y', 'N'));

Audit Trail Trigger:

CREATE OR REPLACE TRIGGER trg_status_audit
BEFORE UPDATE OF completed ON operations
FOR EACH ROW
BEGIN
    IF :NEW.completed = 'Y' AND :OLD.completed = 'N' THEN
        :NEW.completed_by := V('APP_USER');
        :NEW.completed_date := SYSTIMESTAMP;
    END IF;
END;

Modal Dialog Implementation with Date Validation

Problem

Data entry workflows often require multiple input fields that clutter the main page interface. Presenting all creation fields inline reduces usability and makes the interface overwhelming. Additionally, users need immediate feedback when entering date ranges to prevent invalid data submission. Without modal dialogs, form validation errors require full page refreshes, disrupting the user workflow.

Solution

Implement APEX modal dialog pages for data entry that open in an overlay window without navigating away from the main page. Configure the modal to include date pickers with built-in validation. Use dynamic actions to enforce date logic client-side while the modal is open. Handle modal closure events to refresh the parent page's data list.

Implementation

Dynamic Action on Start Date Change:

-- Event: Change
-- Selection Type: Item
-- Item: P_START_DATE
-- Action: Set Value
-- Set Type: JavaScript Expression
-- JavaScript: $v('P_START_DATE')
-- Affected Element: P_END_DATE
-- Attribute: Minimum Value

Dialog Closed Event (Parent Page):

-- Event: Dialog Closed
-- Action: Refresh region containing data list

Dynamic Actions to Prevent End Date Before Start Date

Problem

Date range inputs require real-time validation to prevent logical errors. Users may select an end date that precedes the start date either through manual entry or date picker selection. Without immediate client-side feedback, users only discover the error after form submission. Additionally, client-side validation alone is insufficient—malicious users can bypass browser controls.

Solution

Implement layered validation using APEX dynamic actions for immediate client-side feedback combined with server-side PL/SQL validation for security.

Implementation

Dynamic Action 1: Update End Date Minimum

Event: Change [P_START_DATE]
True Action: Set Value
  Set Type: JavaScript Expression
  JavaScript: $v('P_START_DATE')
  Affected Element: P_END_DATE
  Attribute to Set: Minimum Value

Dynamic Action 2: Validate End Date

Event: Change [P_END_DATE]
Condition: JavaScript Expression
  JavaScript: new Date($v('P_END_DATE')) < new Date($v('P_START_DATE'))
True Action: Show error message
  Message: "End date cannot be before start date"
False Action: Clear error message

Server-Side Validation (Required):

-- Validation Type: PL/SQL Expression
-- Expression: :P_END_DATE >= :P_START_DATE
-- Error Message: "End date must be on or after start date"

Lookup Table Pattern Instead of Hardcoded CASE Statements

Problem

Hardcoding mappings in application logic using CASE statements creates maintenance overhead and requires code deployments for simple data changes. This tight coupling between data and code slows operational agility, increases deployment risk, and prevents administrators from managing mappings independently.

Solution

Create a dedicated lookup table that stores mappings as data rather than code. Replace CASE statement logic with simple SELECT queries against this table. Build an administrative interface in APEX that allows authorized users to add, modify, or remove mappings without code changes.

Implementation

CREATE TABLE value_mappings (
    source_value VARCHAR2(100) PRIMARY KEY,
    mapped_value VARCHAR2(100) NOT NULL,
    active VARCHAR2(1) DEFAULT 'Y' CHECK (active IN ('Y','N')),
    created_date TIMESTAMP DEFAULT SYSTIMESTAMP,
    created_by VARCHAR2(100),
    modified_date TIMESTAMP,
    modified_by VARCHAR2(100)
);

Replace CASE Statement Logic:

-- Old approach
v_result := CASE :P_INPUT_VALUE
    WHEN 'Value A' THEN 'RESULT_A'
    WHEN 'Value B' THEN 'RESULT_B'
    ELSE NULL
END;

-- New approach
BEGIN
    SELECT mapped_value INTO v_result
    FROM value_mappings
    WHERE source_value = :P_INPUT_VALUE
      AND active = 'Y';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'No mapping found for input value');
END;

Audit Trigger:

CREATE OR REPLACE TRIGGER trg_mapping_audit
BEFORE INSERT OR UPDATE ON value_mappings
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.created_by := V('APP_USER');
        :NEW.created_date := SYSTIMESTAMP;
    ELSIF UPDATING THEN
        :NEW.modified_by := V('APP_USER');
        :NEW.modified_date := SYSTIMESTAMP;
    END IF;
END;