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
and then we can run our function as described above.
[oracle@localhost ~]$ sqlplus hrdemo/hrdemo SQL*Plus: Release 22.214.171.124.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 126.96.36.199.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.