Remote debugging with SQL Developer

For all of us who use SQL Developer, we know how to debug with SQL Developer. Compile for debug, breakpoint and go. However folks are confused by what “remote debugging” is and how it works. At its most basic it allows us to run a procedure in a session and debug it from another session.

Let’s say we have a simple function like this

demo@ORA12C> create or replace function foo(x int)

  2  return number as

  3  begin

  4     if mod(x,2) = 0 then

  5             return x+2;

  6     else

  7             return x+1;

  8     end if;

  9  end;

 10  /

Function created. 

 

We compile that function for debug as normal like this.

 

demo@ORA12C> alter function foo compile debug; 

 

Function altered. 

 

Now for remote debugging, we want to go to another session and run this function from there, for clarity we can do it with SQL*Plus, before that however we need to switch on the remote debugger listener, so that we can attach to a session.  

 

So right click the connection from the SQL Developer and say “Remote Debug”, which will pop up a little window like this.

 

We are running the database server on this same machine, so we don’t need to add any other information, but if you are connecting to database running on a different machine, add that host name to this LOCAL ADDRESS field and choose the appropriate port.  

When you click OK on this, Run manager is show with the listener details.

 

Now from SQL*Plus we run our function like this.

 

Once we run the anonymous block, the remote debugger kicks in and we stop at the appropriate breakpoint in the code.

From there you can debug the code further in the SQL Developer. This works for all the application (Java,.Net,C,C++,Phyton,R,Ruby etc) calling PL/SQL code.