Conditionally send email ONLY if query returns no data at all [Oracle PUBLISHER - Reports]

Summary

The issue arises when attempting to conditionally send an email only if a query returns no data in Oracle BI Publisher Reports. The query was intentionally designed to return no results by including AND 1 = 0, but the email was not triggered as expected. The root cause lies in how BI Publisher evaluates the query and the bursting logic.

Root Cause

The query intentionally returns no data due to the condition AND 1 = 0. However, BI Publisher does not recognize this as a valid scenario to trigger the email. The bursting logic fails to interpret the absence of data as a condition for sending the email.

Why This Happens in Real Systems

BI Publisher Limitations: BI Publisher does not inherently support conditional email triggers based on empty query results.
Bursting Logic: The bursting mechanism relies on the query returning at least one row, even if it’s a dummy row, to process the email.
Query Design: The AND 1 = 0 condition ensures no data is returned, but it does not signal BI Publisher to execute the email logic.

Real-World Impact

Failed Notifications: Critical alerts are not sent when expected, leading to potential oversight.
Process Inconsistency: Reliance on manual checks instead of automated notifications.
User Confusion: Misunderstanding of BI Publisher’s behavior with empty result sets.

Example or Code (if necessary and relevant)

-- Original Query (Intentionally returns no data)
SELECT b.descriptive_flex_context_code, c.column_seq_num, c.form_left_prompt, c.application_column_name
FROM fnd_descriptive_flexs a, fnd_descr_flex_contexts b, fnd_descr_flex_col_usage_vl c
WHERE a.application_id = b.application_id
  AND a.descriptive_flexfield_name = b.descriptive_flexfield_name
  AND b.descriptive_flex_context_code = c.descriptive_flex_context_code
  AND b.application_id = c.application_id
  AND b.descriptive_flexfield_name = c.descriptive_flexfield_name
  AND 1 = 0
ORDER BY b.descriptive_flex_context_code, c.column_seq_num;

-- Bursting Logic (Dummy Row)
SELECT 'MAIL' AS KEY, 'EMAIL' AS DEL_CHANNEL, 'dummyemail@dummy.com' AS PARAMETER1, 
       'Aviso automatico' AS PARAMETER6, 'No data found. Mail sent successfully.' AS PARAMETER7
FROM dual;

How Senior Engineers Fix It

Use a Dummy Row: Modify the query to return a dummy row when no data is found.
Leverage PL/SQL: Wrap the query in a PL/SQL block to conditionally insert a dummy row if the result set is empty.
External Scripting: Use an external script to check for empty results and trigger the email via a separate process.

Key Fix: Add a UNION ALL with a dummy row if the main query returns no data.

SELECT b.descriptive_flex_context_code, c.column_seq_num, c.form_left_prompt, c.application_column_name
FROM fnd_descriptive_flexs a, fnd_descr_flex_contexts b, fnd_descr_flex_col_usage_vl c
WHERE a.application_id = b.application_id
  AND a.descriptive_flexfield_name = b.descriptive_flexfield_name
  AND b.descriptive_flex_context_code = c.descriptive_flex_context_code
  AND b.application_id = c.application_id
  AND b.descriptive_flexfield_name = c.descriptive_flexfield_name
UNION ALL
SELECT NULL, NULL, NULL, NULL
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM fnd_descriptive_flexs a, fnd_descr_flex_contexts b, fnd_descr_flex_col_usage_vl c
                   WHERE a.application_id = b.application_id
                     AND a.descriptive_flexfield_name = b.descriptive_flexfield_name
                     AND b.descriptive_flex_context_code = c.descriptive_flex_context_code
                     AND b.application_id = c.application_id
                     AND b.descriptive_flexfield_name = c.descriptive_flexfield_name);

Why Juniors Miss It

Lack of Understanding: Juniors may not grasp BI Publisher’s limitations with empty result sets.
Overreliance on Query: Assuming the query alone can trigger the email without additional logic.
Insufficient Testing: Not testing edge cases, such as empty results, during development.

Key Takeaway: Always test edge cases and understand the tool’s limitations to avoid unexpected behavior.

Conditionally send email ONLY if query returns no data at all [Oracle PUBLISHER – Reports]