Get debugging information from your custom events with one simple package.
This is one of the first packages we deploy whenever starting a contract with a new client. It's extremely simple and provides an easy way to get debugging information into a SQL output window or in a background job. Explanations for procedures can be found below, followed by the entire package code.
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.
We primarily use these outputs when developing and rarely have the need to use them in a production environment so to save unnecessarily writing data to the buffer or deleting any references to DBMS_OUTPUT when deploying to production, a function checks which database the code is running in and will only output data if it's not in production. Make sure you edit the database name in the code below to your production database name.
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 programatically adds nice borders.
Here's an example of these procedures in action:
A nice feature of posting deferred jobs is the ability to add messages to the Background Job window as the procedure is executing. As we want to add outputs as our code is executing but don't want to commit everything we've done, we need to use the AUTONOMOUS_TRANSACTION pragma. The Background_Output procedure performs the same :P1, :P2, :P3 and :P4 replacements as the local output procedures and sends the result to IFSAPP.TRANSACTION_SYS.Set_Status_Info, which uses the sys context to post the message to the correct background job.
By default, the messages are displayed as INFO messages but this can be changed by entering WARNING in the type_ parameter.
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.