Debugging Oracle EBS Concurrent Programs Like a Pro

Logs, Output, Common Failures, SQL Tracing + Must-Know Queries

When working with Oracle E-Business Suite, concurrent programs are at the heart of most business processes. And sooner or later, every developer runs into the same challenge:

“Why is my concurrent request failing… and where do I even start?”

This post is both a debugging guide and a SQL cheat sheet I personally rely on when troubleshooting issues in real environments.

Understanding Log vs Output Files
📄 Log File (The Real Debugging Tool)

The log file is where everything important happens.

It contains:

  • PL/SQL errors
  • Debug messages
  • Execution steps
  • SQL errors (ORA-xxxx)

Example:

ORA-01403: no data found

This is always your first place to check

📊 Output File (User-Facing Result)

The output file is what the user sees.

It contains:

  • Report data
  • XML / PDF output
  • Formatted results

If output is empty but request is “Normal”, the issue is often in logic, not execution.

Using FND_FILE for Debugging

The FND_FILE package is your best friend.

Writing to Log
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start of program');
Writing to Output
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Report Line');
Real Debug Example
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before fetching employee');

SELECT employee_name
INTO   l_name
FROM   employees
WHERE  employee_id = p_emp_id;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee: ' || l_name);

Always log:

  • Input parameters
  • Key variable values
  • Execution checkpoints

Additionally, I usually like to wrap the log and output functions in a separate procedures inside my PLSQL package. I usually name them “log” and “outp” accepting only one parameter of VARCHAR type.

Common Errors and How to Read Them

ORA Errors (Database Errors)

Example:

ORA-01403: no data found

Meaning:

Your SELECT INTO returned no rows

Fix:

BEGIN

SELECT …
INTO …
FROM …
WHERE …;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
  FND_FILE.PUT_LINE(FND_FILE.LOG, 'No data found');
END;
APP-FND Errors (Application Layer)
Example:

APP-FND-01564: ORACLE error 1403 in…

Meaning:

Wrapper around ORA error
Often tied to concurrent processing

Always scroll further down in log to find actual ORA error.

Tracing SQL from Concurrent Programs

When logs are not enough, enable SQL trace.

Option 1: Enable Trace for Request

Set profile:

Concurrent: Allow Debugging = Yes

Then enable trace at request level.

Option 2: Add in Code
EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);

Trace file will be generated on DB server.

Option 3: Use FND_TRACE (Advanced)
FND_TRACE.SET_TRACE_ON;

Top SQL Queries Every EBS Developer Should Know

These are extremely useful when debugging.

🔍 Find Concurrent Request Details
SELECT
    request_id,
    phase_code,
    status_code,
    argument_text,
    logfile_name,
    outfile_name
FROM
    fnd_concurrent_requests
WHERE
    request_id = :request_id;
🔍 Get Full Request + Program Info
SELECT
    r.request_id,
    r.phase_code,
    r.status_code,
    p.concurrent_program_name,
    p.user_concurrent_program_name
FROM
    fnd_concurrent_requests r,
    fnd_concurrent_programs_tl p
WHERE
    r.concurrent_program_id = p.concurrent_program_id
AND p.language = 'US'
AND r.request_id = :request_id;
🔍 Find Log and Output File Names
SELECT
    logfile_name,
    outfile_name
FROM
    fnd_concurrent_requests
WHERE
    request_id = :request_id;
🔍 Find Responsibility and Menu
SELECT
    fr.responsibility_name,
    fm.menu_name
FROM
    fnd_responsibility_vl fr,
    fnd_menus fm
WHERE
    fr.menu_id = fm.menu_id;
🔍 Trace User Access (Which Responsibility User Has)
SELECT
    fu.user_name,
    fr.responsibility_name
FROM
    fnd_user fu,
    fnd_user_resp_groups_direct furg,
    fnd_responsibility_vl fr
WHERE
    fu.user_id = furg.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fu.user_name = :user_name;
🔍 Find Concurrent Requests by User
SELECT
    request_id,
    phase_code,
    status_code,
    requested_start_date
FROM
    fnd_concurrent_requests
WHERE
    requested_by = (
        SELECT user_id
        FROM fnd_user
        WHERE user_name = :user_name
    )
ORDER BY request_id DESC;
🔍 Check Parameters Passed to Program
SELECT
    argument_text
FROM
    fnd_concurrent_requests
WHERE
    request_id = :request_id;

Real Debugging Workflow (What I Actually Do)

  1. Open concurrent request
  2. Check log file first
  3. Search for:
    • ORA-
    • ERROR
  4. Add FND_FILE logs if needed
  5. Run SQL queries to inspect data
  6. Enable trace if issue is unclear
Common Mistakes to Avoid

❌ Not checking log file first
❌ Printing debug info to OUTPUT instead of LOG
❌ Ignoring parameter values
❌ Not handling exceptions
❌ Assuming data exists

Final Thoughts

Debugging concurrent programs is a skill that improves fast with practice.

Once you combine:

  • Log file reading
  • Proper FND_FILE usage
  • SQL tracing
  • Handy queries

You move from guessing → to systematic debugging

And that’s when things become much easier.