Debugging PL/SQL code without debugger (Oracle)

[Ursego]

Link to this posting

by Ursego » 21 Oct 2023, 17:56

The dbg() procedure (download) is used to capture run-time info and log it in a table for subsequent analysis. Comes in handy when debugging in the normal way is hard or impossible. For example, if the investigated proc gets non-obvious parameters, it's easier and more reliable to perform the user scenario, and then read the captured info. If the investigated proc reads data from another package, the regular debugging is impossible at all if that data is populated by other parts of the app.

FEATURES:
* Extracts the calling package and the procedure names from the call stack and adds them to debug info, so you don't need to pass them see the calls hierarchy.
* Cleans up after the previous debug session to eliminate a buildup of old log records. You can debug again and again, and always see only the last results.
* Uses an autonomous transaction, which:
- doesn't impact the main transaction;
- allows you to debug exceptions (otherwise, the debug records would be deleted);
- doesn't re-set the SQL%ROWCOUNT of the main transaction (which may be used in the logic after you call dbg()).

Examples of use are provided in the procedure's header comment.