Thursday, 12 April 2012

Remote Debugging with SQL Developer revisited.

As part of the development process, we all have to work out the bugs in our code.  For all of us who use SQLDeveloper , we know how to debug with SQL Developer. Compile for Debug, breakpoint and go.  However, People still get 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 if from another.

So, Lets say we have a simple procedure on employees table like this.

create or replace
FUNCTION GET_EMP_NAME 
(
  ID IN NUMBER  
) RETURN VARCHAR2 AS 
name varchar2(100);
BEGIN
 select first_name||' '||last_name into name from employees
 where employee_id = ID;
  RETURN name;
END GET_EMP_NAME;


We can compile this for debug in SQLDeveloper as normal.  Now, for remote debugging, we want to go to another session and run this function there.  For clarity, we can do it in SQL*Plus.  Before that however, we need to switch on the remote debugger listener so we can attach to a session.  So, firstly, right click on your connection and choose remote debug, which will pop up a little window

For our purposes, on localhost, we dont need to add any other information, but if you are connecting to another database on another machine, add the host name to the local address field and choose an appropriate port.  When you click ok on this, the Run manager is shown with the listener details on there as shown above.

Now, here we are with SQL*Plus, fire it up with our demo user and make sure to execute the command

 execute DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',4000);

and then we can run our function as described above.

[oracle@localhost ~]$ sqlplus hrdemo/hrdemo

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 12 19:16:37 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HRDEMO@ORCL> set serveroutput on
HRDEMO@ORCL> execute DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',4000);

PL/SQL procedure successfully completed.

HRDEMO@ORCL> begin
  2  dbms_output.put_line(get_emp_name(100));
  3  end;
  4  /


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

On a last note, this works well in Application Express too so when you make a call to a function which you have remote debug switched on for, the debugger will break on the line as long as you have debug switched on in the developer toolbar.

2 comments:

Faiz said...

Can we do the same thing for a Java Application calling PLSQL code ?

Barry McGillin said...

of course, just put the debug enable into your java code to enable it and all same rules apply