Debugging in Oracle APEX

Debugging in Oracle APEX often begins with identifying why a report region returns no data, a form loads with missing values, or a dynamic action doesn’t behave as expected. Many of these issues can be traced back to the underlying SQL query. When queries depend on page items, dynamic filters, or conditional logic, it's important to understand exactly what values are being passed and how the database interprets them. A powerful technique is to rewrite the query to make it more “debuggable”—by exposing bind values, adding logic checks, and validating assumptions directly in the SELECT clause. This blog post shows how to build more detailed queries to simplify debugging and speed up development.

How to Build a More Detailed Query for Debugging in APEX

Start by identifying the part of the query that depends on page items. These might include :P1_ID, :P6_STATUS, or any filter used inside WHERE, HAVING, or even CASE expressions.

1. Expose Bind Variables in the SELECT Clause
   This shows what the database sees:

   SELECT 
     employee_id,
     department_id,
     :P6_DEPT_ID AS p6_dept_value,
     CASE 
       WHEN department_id = :P6_DEPT_ID THEN 'MATCH'
       ELSE 'NO MATCH'
     END AS match_check
   FROM employees
   

   Adding the bind value and conditional logic directly into the output helps you confirm that the filter is working.

2. Use CASE Statements to Validate Logic
   Validate logic that might be hidden inside the WHERE clause:

   SELECT 
     hire_date,
     :P6_START_DATE AS start_date,
     :P6_END_DATE AS end_date,
     CASE 
       WHEN hire_date BETWEEN :P6_START_DATE AND :P6_END_DATE 
       THEN 'IN RANGE'
       ELSE 'OUT OF RANGE'
     END AS hire_range_check
   FROM employees
   

3. Temporarily Remove the WHERE Clause
   Start without filters so you can observe what values are being compared. This helps you spot issues like nulls or mismatched data types.

4. Check Bind Variable Values in SQL Workshop
   If you want to simulate how the query runs in SQL Workshop, hard-code the values temporarily:

   SELECT ...
   FROM ...
   WHERE department_id = 10 -- Instead of :P6_DEPT_ID
   

5. Use APEX Debug Logs
   For more insight:

   • Turn on debug mode from the Developer Toolbar.

   • Open “View Debug” after submitting the page.

   • Look for page item values, query execution details, and rendered SQL statements.

6. Log Messages from PL/SQL
   Inside PL/SQL blocks:

   apex_debug.log_message('P6_STATUS = ' || :P6_STATUS);
   

Best Practices for Debug Queries

• Always display page item values in your query results to verify input.

• Use CASE statements to test matching conditions or ranges.

• Don’t rely on hidden filters during debugging—make everything visible.

• Combine query changes with apex_debug logs for full traceability.

• Remove or comment out detailed logic once the issue is fixed to keep your queries clean.

To better understand which row or data is causing the error, you can first query the raw data from the JSON fields to see if there are any issues with the data:

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS longitude,

    JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS latitude

FROM USCG_DATA

WHERE NOT REGEXP_LIKE(JSON_VALUE(GEOMETRY, '$.coordinates[0]'), '^-?\d+(\.\d+)?$')

   OR NOT REGEXP_LIKE(JSON_VALUE(GEOMETRY, '$.coordinates[1]'), '^-?\d+(\.\d+)?$');

This query will return rows where the longitude or latitude values are not valid numbers, helping you identify the problematic data.

Official Oracle APEX Documentation

• APEX_DEBUG API

• SQL Workshop Guide

• Understanding Bind Variables

 Conclusion

Debugging queries in Oracle APEX doesn’t need to be time-consuming. By adding clarity to your SQL with bind value displays and logical tests, you can make your data behavior fully visible. This approach turns confusing results into understandable outputs and transforms trial-and-error into a structured debugging method. Combined with the APEX debug log and SQL Workshop, these techniques help you identify problems faster and develop more confidently.