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 waste days. Good ones save weeks. 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 compilation needed – just add it to your code
• Output appears immediately in SQL*Plus, SQL Developer, or IFS Developer Studio
• Works in background jobs and scheduled tasks (with caveats)
• Zero overhead for small debugging

Gotchas and Limitations

1. Buffer overflow – DBMS_OUTPUT has a 1MB default buffer. Large operations silently overflow. Increase it with:

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 anything more sophisticated than a quick log, use Log_SYS, IFS's server-side tracing framework. It's designed for production code and integrates seamlessly with the IFS debug console.

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

From SQL*Plus or SQL Developer, start tracing for your custom application code:

Categories

IFS defines these trace categories:

• Framework – Internal IFS framework code
• Application – Your custom PL/SQL
• Bootstrap – System initialization (rarely useful)
• Installation – Setup-related tracing
• CallStack – Full call stack tracing
• Custom – Your own categories (define in code)

Custom Log Categories

Then filter to see only your traces:

Viewing Traces

1. In IFS Client (Enterprise Explorer) – Open the Debug Console → PL/SQL Trace tab
2. In SQL*Plus – Enable output and query directly
3. In Server Logs – Check middleware logs under <IFS_HOME>/instance/<instance_name>/logs/j2ee/

The advantage: Server Trace works everywhere, even in background jobs and scheduled tasks—unlike DBMS_OUTPUT.

---

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

When DBMS_OUTPUT and Server Trace aren't enough, SQL Developer's integrated debugger lets you set breakpoints, inspect variables in real time, and step through code line by line.

Compiling for Debug

First, ensure your package is compiled with the DEBUG flag:

Without this, the debugger won't work properly. The flag stays active until the package is recompiled without it.

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: Debug from IFS Enterprise Explorer

Set a breakpoint in your code, then trigger the procedure from the client application. The debugger connects back to SQL Developer automatically.

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 Complex Insert

Set breakpoints at comments, run the debugger with test data, and watch the values change as the code executes. You'll immediately see where logic fails.

---

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

Each maps to a specific Oracle error code (usually -20100 through -20150 range).

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

Use these in exception handlers:

---

5. Reading IFS Application Logs Like a Pro

IFS logs everything: database calls, API requests, errors, performance timings. Learn to navigate them and you'll solve problems in minutes.

Log Files Location

• Application Server Logs: <IFS_HOME>/instance/<instance_name>/logs/j2ee/
• ifsalert.log – Errors and critical issues
• ifslog.log – General application logging
• ifsconnectlog.log – IFS Connect integration activity

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

Edit ifs-logging.properties:

Or change dynamically via JMX console (no restart needed).

Reading a Log Entry

A typical log entry looks like:

[2026-04-03 14:23:45.123] [REQID:12699411] [database] [TRACE]
SELECT * FROM customers WHERE customer_id = 12345

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 shows you every database call, API invocation, and log statement for that single user action. Incredibly powerful.

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)

Use AUTONOMOUS_TRANSACTION to post messages to the background job window without committing your main transaction:

Pattern 3: Conditional Debugging (Dev vs. Production)

Wrap debug code so it only runs in non-production:

Or use compiler flags:

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 for production – Log_SYS works everywhere, even in background jobs
3. Leverage the debugger – SQL Developer's breakpoints and variable inspection are powerful
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, SQL Developer's debugger, and log analysis. Practice on small issues. When a big one hits, you'll know exactly what to do.

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.