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.

Resource isn’t necessary…and you’ll need to be debugging something you already have EXEC privs.

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

The first setting is new for version 20.2 of Oracle SQL Developer.

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!

A complete debug session/demo – enjoy!

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.

This was recorded using the JDWP debugger implementation – but the core concepts all apply!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment