Debugging PL/SQL in IFS: Tools and Techniques

Debugging PL/SQL in IFS can feel like navigating a labyrinth—especially when you're staring at cryptic error codes and stack traces. But with the right tools and techniques, you can systematically isolate issues, understand what's happening under the hood, and fix problems faster than you'd think possible.

This guide walks you through the complete debugging arsenal available to IFS developers: from quick-and-dirty DBMS_OUTPUT hacks to IFS's powerful Server Trace framework, SQL Developer's breakpoint debugger, and methodical error log analysis. By the end, you'll have a mental map of when to use each tool and how to chain them together for maximum efficiency.

Why Debugging Matters in IFS

IFS applications often run custom PL/SQL packages that interact with the database layer, API calls, and complex business logic. When something breaks, you need to know:

• What happened? (the actual error)
• Where did it happen? (which procedure, which line)
• Why did it happen? (what values caused the problem)
• How do I fix it? (which code needs to change)

Poor debugging practices increase diagnosis time and make regressions harder to reproduce. A disciplined approach shortens that cycle and produces evidence the next developer can reuse. Let's start with the essentials.

---

1. DBMS_OUTPUT: The First Line of Defense

DBMS_OUTPUT is the old reliable friend. It's simple, immediate, and requires no special setup. If you're new to IFS debugging, start here.

Basic DBMS_OUTPUT Usage

Why DBMS_OUTPUT Works

• No framework setup needed – add it to an anonymous block, or compile it into customer code temporarily
• Output is returned when control comes back to the client in SQL*Plus/SQLcl or SQL Developer; it is not a live streaming console
• Useful for developer-owned sessions; a background/deferred session does not automatically return its DBMS_OUTPUT buffer to your worksheet
• Zero overhead for small debugging

Gotchas and Limitations

1. Buffer limits – client and database versions impose different defaults. On supported Oracle versions, request the maximum buffer with NULL; older clients may still cap or truncate what they display:

2. Hidden output – In IFS Enterprise Explorer or background jobs, PUT_LINE output won't show. Use the Background_Output procedure (see Advanced Techniques) instead.

3. No variable inspection – You see what you explicitly write. Complex objects require manual formatting.

4. Performance – String concatenation (especially with ||) can slow down loops. Use it for debugging, remove it before production.

Pro Tip: The Format Trick

Instead of jumbled concatenations, use IFS-style error message replacement:

---

2. IFS Server Trace: Production-Grade Debugging

For framework-aware tracing, use Log_SYS. It writes to the active IFS server-trace stream when that trace category/level is enabled. Treat it as diagnostic tracing rather than a durable business audit log.

Setting Up Traces

Log Levels Explained

• ERROR (level 1) – Critical failures only
• WARNING (level 2) – Unexpected but recoverable situations
• INFO (level 3) – High-level progress (default)
• TRACE (level 4) – Detailed execution flow
• DEBUG (level 5) – Variable values, SQL statements, everything

Enabling Server Trace in Runtime

Enable the database/application trace through the supported IFS debug/server-trace UI for the relevant Apps 10 or Cloud release, then reproduce the operation with a narrow user/session and time window. Log_SYS.Set_Log_Level_ and Log_SYS.All_Categories are not public Apps 10 APIs to paste into application code.

Categories

IFS trace controls and category labels vary between Apps 10 and Cloud releases. Log_SYS.App_Trace writes in the database application category; the level constants are stable in Apps 10 (error_ 1, warning_ 2, info_ 3, trace_ 4, debug_ 5). Select the smallest relevant category set in the trace UI to avoid collecting sensitive data or overwhelming the session.

Custom Log Categories

The three-argument overload supplies a trace type label that can help filtering. It does not register an arbitrary global category or make a disabled trace collect data.

Viewing Traces

1. In the IFS debug/server-trace tooling – capture the relevant database/application trace for the target release
2. In SQL Developer/SQLcl – run the method in the same authorised session and inspect DBMS_OUTPUT/error stack where appropriate
3. In platform logs – use the supported log viewer/observability route; filesystem paths are deployment- and release-specific

The advantage is correlation with an IFS request/session when tracing is configured correctly. For background jobs, also use Transaction_SYS.Set_Status_Info for concise operator-facing progress, as shown later.

---

3. SQL Developer's PL/SQL Debugger: Step Through Your Code

When DBMS_OUTPUT and Server Trace are not enough, SQL Developer's debugger can set breakpoints, inspect variables, and step through PL/SQL in a customer-controlled development environment. It requires DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE or object-level debug rights as appropriate, and JDWP connectivity from the database to the workstation. Those privileges and network paths are deliberately unavailable in many managed Cloud environments.

Compiling for Debug

Compile only the customer-owned package body you need with debug information in non-production:

The setting remains until the object is recompiled without debug information. Recompilation invalidates dependent cursors and can change optimisation characteristics, so do not casually debug-compile IFS standard packages or busy production code.

Setting Breakpoints

1. Open your package source in SQL Developer
2. Click the line number margin (left side) where you want to pause
3. A red circle marks the breakpoint

Starting a Debug Session

You have two options:

Option 1: Debug from a Test Block

Right-click → Debug (or press Ctrl+Shift+F9). Execution pauses at your first breakpoint.

Option 2: Attach to an application-triggered session

Remote/application-session debugging is possible only when the IFS release, Oracle grants, network ACL, session routing, and environment policy support JDWP attachment. It does not connect automatically merely because an Enterprise Explorer action was triggered. For Cloud managed service issues, reproduce in an authorised development build or use server trace and platform logs.

Inspecting Variables

When paused at a breakpoint:

• Variables panel shows all local variables, their types, and current values
• Watches panel lets you add custom expressions to monitor
• Call stack panel shows how you got here (every procedure that called this one)
• Hover over variable names in the editor to see their values

Stepping Through Code

• Step Over (F10) – Execute the current line, move to next line
• Step Into (F11) – Enter called procedures to debug them
• Step Out (Shift+F11) – Exit the current procedure, return to caller
• Continue (F5) – Resume full execution until next breakpoint

Real-World Example: Debugging a Customer Wrapper

The two Purchase_Order_API methods exist in Apps 10. Confirm their signatures and semantics again in the target release. The wrapper intentionally performs no base-table DML and owns neither COMMIT nor ROLLBACK; that makes it safe to call within a wider IFS transaction.

---

4. Error_SYS and Application_SYS: Understanding IFS Error Handling

Errors in IFS don't just happen randomly—they're raised through a controlled framework. Understanding this framework helps you debug the right way.

Soft vs. Hard Errors

Soft errors are business logic failures (expected, user-facing):

• Customer doesn't exist
• Invoice balance exceeds limit
• Duplicate record

Hard errors are technical failures (unexpected, technical):

• ORA-00001: Unique constraint violated
• ORA-06502: Numeric or value error
• ORA-01400: NOT NULL column missing

Using Error_SYS

IFS provides Error_SYS package with predefined error codes. Always raise errors through it, not directly:

Why? Error_SYS handles:

• Translation of error messages
• Proper error logging
• Client-side error display
• Standardized error codes

Common Error_SYS Methods

The first argument is the logical-unit name, and the error text embeds a translatable message tag before the colon. Overloads vary by release; copy the public signature from the target environment rather than inventing a separate msg_ argument. Framework methods raise IFS-reserved application errors, but business code should not depend on every numeric code remaining the same across versions.

Catching IFS Errors

Error Stack Traces

When an error occurs, IFS sets context variables:

• ERROR_CALL_STACK – Full PL/SQL call stack showing which procedures called which
• ERROR_FORMATTED_KEY – Formatted version of the affected record key
• ERROR_KEY_MESSAGE – IFS message object with key field names and values

In Apps 10 these values are held through Fnd_Context_SYS, not an Oracle context namespace named IFS. They may be null if the framework has not populated them. Use them as supplementary diagnostics:

---

5. Reading IFS Application Logs Like a Pro

IFS exposes several diagnostic streams, but it does not log every database call and parameter by default—and doing so would be unsafe and expensive. Start from the user-visible error, timestamp, user/request correlation, background-job ID, and enabled trace level.

Log Files Location

• Apps 10/on-premises: middleware and IFS Connect logs are found under the installed instance's configured log locations; confirm the topology and log configuration rather than assuming one path or filename.
• IFS Cloud managed service: use the supported administration/observability views and IFS support process. Customers do not normally browse an application-server filesystem.
• Database/background jobs: use the IFS database-task/background-job detail and status output for the relevant release.

Log Levels Explained

From least to most verbose:

1. ERROR – Fatal failures only
2. WARNING – Unexpected but recoverable
3. INFO – High-level progress (typical default)
4. TRACE – Detailed execution (SQL statements, parameters)
5. DEBUG – Everything (very verbose)

Configuring Log Levels

The concrete logging configuration mechanism differs between Apps 10 middleware and IFS Cloud. The following is illustrative of category-level logging, not a universal IFS file to edit:

Use only the supported administration mechanism for that release. Avoid increasing production logging globally; scope it by component/session and revert it after evidence is captured.

Reading a Log Entry

A correlated platform entry may contain fields like:

[2026-04-03 14:23:45.123] [REQID:12699411] [database] [TRACE]
Request failed while validating customer

Breaking it down:

• Timestamp – When it happened
• REQID – Request ID (correlates this entry with others from the same request)
• Category – database, application, framework, etc.
• Level – TRACE, DEBUG, INFO, WARNING, ERROR
• Message – The actual log text

Filtering Logs Effectively

Use grep to find issues:

Request Tracing with REQID

When troubleshooting a user's issue:

1. Ask the user what time the error occurred
2. Search logs for ERROR entries around that time
3. Note the REQID
4. Search for all entries with that REQID to see the full request flow

This can assemble the entries that were actually emitted for that request. SQL text and bind values are not guaranteed to be present, and sensitive values should not be logged merely to make debugging easier.

Timings Category

The timings category logs request performance:

[REQID:12699411] Total time: 1234ms | DB calls: 23 | DB time: 890ms

This tells you whether the bottleneck is database, application logic, or network.

---

6. Advanced Techniques: Practical Debugging Patterns

Pattern 1: Strategic Logging in Loops

Never log every iteration—that's a performance killer. Log strategically:

Pattern 2: Background Job Output (for Scheduled Tasks)

Within an IFS deferred/background session, use Transaction_SYS.Set_Status_Info for concise status or warning text. Do not wrap it in an autonomous transaction: the deferred-session context belongs to the running job, and autonomous logging can survive a rollback and misrepresent what completed.

Set_Status_Info is meaningful when the method is running as an IFS background job; it is not a general persistent logger. Let the framework own transaction boundaries unless the documented task design explicitly requires batch commits.

Pattern 3: Conditional Debugging (Dev vs. Production)

Prefer an explicit, centrally controlled diagnostic setting and IFS trace level. Database-name pattern matching is not a reliable production control:

Or use compiler flags:

PL/SQL conditional compilation uses $$ inquiry directives supplied at compile time. Ensure the build defines the flag deliberately; do not assume a generic prod symbol exists.

Pattern 4: Exception Context Variables

When an error occurs, IFS sets context variables. Capture them:

Pattern 5: Debugging Implicit Cursors

Implicit cursors are a common source of bugs. Replace them with explicit cursors and debug carefully:

---

7. Debugging Checklist: The Systematic Approach

When you hit a problem, follow this checklist in order:

Phase 1: Gather Information (5 minutes)

• What exactly is the error message?
• When did it start happening?
• What changed recently? (code, data, configuration)
• Does it happen consistently or intermittently?
• Does it affect one user or everyone?

Phase 2: Quick Wins (5 minutes)

• Check IFS application logs for ERROR entries around the time
• Search for the error code in IFS documentation
• Check if it's a known issue (IFS Community forums)
• Verify database connectivity and privileges

Phase 3: Local Testing (10-15 minutes)

• Reproduce the issue in SQL Developer with test data
• Add DBMS_OUTPUT to narrow down where the failure occurs
• Check variable values at each step
• Run the same logic manually to confirm expected behavior

Phase 4: Deep Dive (15-30 minutes)

• Enable Server Trace (Log_SYS) and re-run
• Check detailed logs in middleware logs
• Use SQL Developer's debugger with breakpoints
• Verify all assumptions about input data

Phase 5: Root Cause Analysis (as needed)

• Is it a logic bug? (fix the code)
• Is it a data issue? (fix the data)
• Is it a permission issue? (fix grants)
• Is it a concurrency issue? (locking, ordering)
• Is it an IFS framework issue? (escalate to support)

---

Key Takeaways

1. Start simple – DBMS_OUTPUT is your friend for quick debugging
2. Use Server Trace deliberately – enable the narrowest diagnostic trace and do not confuse it with durable audit logging
3. Leverage the debugger where authorised – SQL Developer breakpoints are powerful in a customer-controlled development environment, not a default managed-Cloud facility
4. Understand error handling – Know Error_SYS and how IFS structures errors
5. Master log analysis – IFS logs tell the complete story if you know how to read them
6. Never hardcode debug code – Wrap it in conditionals or remove it after debugging
7. Think systematically – Follow a checklist; don't just guess

---

Final Thoughts

Debugging is a skill, not magic. The developers who solve problems fastest aren't the smartest—they're the ones who know their tools and use them methodically.

Master DBMS_OUTPUT, Log_SYS, Error_SYS, IFS background-job status, the target release's logging tools, and—where authorised—SQL Developer's debugger. Practice on small issues and preserve correlation IDs and error backtraces when a larger incident occurs.

The next time you're staring at an error message, take a breath. Open your debugger. Read the logs. Step through the code. You've got this.

Happy debugging.