Debugging your PL/SQL in Oracle Autonomous Databases

Debugging PL/SQL isn’t a new topic here. But someone said they didn’t realize it was possible to do step-wise line debugging in Oracle Autonomous.

I’m here to set those concerns aside, you CAN DEBUG IN AUTONOMOUS!

There’s only a few things you require:

• your Cloud wallet to connect securely
• username and password
• privs to EXECUTE and DEBUG (DEBUG CONNECT SESSION)
• Oracle SQL Developer (v20.2 or higher), configured to use DBMS_DEBUG
• some PL/SQL compiled for DEBUG

I’ve got an Always Free Autonomous Transaction Processing instance running. And I’ve created a user called ‘JEFF’ – he’s nothing special.

Some silly PL/SQL to step through

CREATE OR REPLACE PROCEDURE test_debug (x IN INTEGER) IS
 z NUMBER;
BEGIN
 FOR y IN 1..x LOOP
 DBMS_OUTPUT.put_line(y || '.. ');
 z:= x + y;
 END LOOP;
END test_debug;
/

Debugger Settings of Note

Set these, esp the DBMS_DEBUG one

Debugging

Once you’re connected, open your program. Compile for DEBUG. Set a breakpoint. Hit the BUG button. Supply any required inputs. And you’re cooking with gas!

Need to learn more about the debugger?

I’ve written on this topic A LOT. You can read all those posts here.

Want a movie? Here’s a more in-depth 8 minute tour.