Quickly & Easily Output Debugging Data in IFS

This is one of the first small utilities we consider when starting work in a database-backed IFS Applications development environment. It provides an easy way to get debugging information into a SQL output window or into the status lines of a background job. Keep it in a customer-owned component, review it through the normal delivery process, and do not deploy a generic diagnostic package to production without the client's approval.

SQL Output Window

When most developers need to quickly display information relating to some code, DBMS_OUTPUT.Put_Line is the usual choice and it works great. One downside that we've come across is if you want to include variables or fields in your output, the DBMS_OUTPUT.Put_Line method becomes a jumbled mess of concatenations, making it slightly more difficult to quickly change some text or a variable or a field that's included in that long string.

The solution to this was to create a small procedure, Local_Output. This procedure works very similarly to IFS' built-in error procedures, which replace :P1, :P2, :P3 and :P4 in a string with input parameters. Another parameter of the procedure, put_or_line_, determines whether DBMS_OUTPUT.Put_Line or DBMS_OUTPUT.Put should be used.

In SQL Developer, enable the DBMS Output pane for the connection first; in SQL*Plus or SQLcl, use SET SERVEROUTPUT ON. Otherwise the procedure can run successfully while nothing appears on screen.

We primarily use these outputs while developing. To avoid filling the buffer in production, the original utility checked the database name and only emitted data outside production. If you retain that approach, make the environment classification an explicitly managed setting rather than relying on an easily forgotten hard-coded name. The safer example below allows only explicitly named development/test databases and treats every unknown name as live; replace IFSDEV and IFSTEST, then test the setting in every environment before relying on it.

A stored package needs a direct grant—not a grant inherited through a role—to select SYS.V_$DATABASE. Do not request broad catalogue access for this helper. If the customer-owned component does not already have an approved narrow route to the database identity, use a managed application setting for the environment classification instead.

Some outputs need to stand out more than others. Adding a couple more DBMS_OUTPUT.Put_Line calls with dashes as borders works well but wouldn't it be nice to have everything in one call and the right number of dashes added automatically? Local_Output_Header has the same :P1, :P2, :P3 and :P4 parameters as Local_Output but programmatically adds nice borders.

Here's an example of these procedures in action:

Background Job Outputs

A useful feature of a deferred job is the ability to add status lines while the job is executing. Transaction_SYS.Set_Status_Info writes only when the current session is running as a deferred/background job; calling it from an ordinary interactive session does not create a background-job message.

In the Apps 10 API, Set_Status_Info participates in the caller's transaction, while Transaction_SYS.Log_Status_Info uses an autonomous transaction and commits the status line. Prefer the released Log_Status_Info method when a diagnostic line deliberately needs to survive a rollback. If you maintain an older custom wrapper that declares PRAGMA AUTONOMOUS_TRANSACTION, that wrapper must commit or roll back its own transaction and must never replace the business transaction's error handling. The Background_Output procedure performs the same :P1, :P2, :P3 and :P4 replacements; whichever released method it calls, do not hard-code the application-owner schema.

In the Apps 10 API, the optional status type defaults to WARNING, and the accepted values are WARNING and INFO. Pass the appropriate string explicitly when the distinction matters; do not assume that an omitted parameter produces an informational line.

To quickly add spacing between background job messages, the Background_Output_Spacer procedure can be used, which simply adds a background job output filled with dashes.