How to remote debug PL/SQL with Oracle SQL Developer

There are quite a few posts (see the References section at the bottom) on how to debug PL/SQL remotely. That is when you want to step through another session that is running some PL/SQL procedure. Still I didn't find it obvious to get it working. Mainly because none of the blog posts have a diagram on how all components are connected and which component tries to connect with the other component(s). Therefore this post, including..... a diagram! :)

Setup
- on a Windows 7 desktop PC running SQL developer with IP xxx.yyy.19.84
- remote database running in VM with IP xxx.yyy.19.104. This one actually is running on the above desktop machine, but that should not change much if it would be on a separate machine, except there might be some firewall(s) in between.

In short this is what we are trying to achieve:

1. Start up the remote debug listener on port 4000 on the desktop machine
2. Start up SQl*Plus connecting to the remote database
3. Connect to the remote debug listener from the database. This might be the tricky part for some: SQL*Plus is connected to the remote database, so the DBMS_DEBUG_JDWP.CONNECT_TCP call is executed from that database! So that machine (.104) needs to be able to get to the machine *.84) with the remote debug listener listening on port 4000
4. Execute the stored procedure from the package you want to debug and SQL Developer will stop at the set breakpoints

Detailed steps

1. As mentioned in all the referenced blog posts, right-click on the connection and select Remote Debug....
2. In that popup enter:
   
   
   If done correctly, you should see something like:
   
   
   If you get a message saying "The debugger is not able to listen for JPDA using the specified parameters." then there is something wrong with the parameters you entered. Yes that's all I could figure out too... Potentially you entered the wrong Local address or maybe there's already something listening on the specified port.
   Note that the Local address is the IP of the machine SQL Developer is running on. Unless your database is running on that same machine, you can't use 127.0.0.1 (localhost). You have to use the full IP address.
3. Since the database (which is on xxx.yyy.19.104) needs to connect to the SQL Developer remote debugger listener, it must be able to get to that machine (xxx.yyy.19.84). You can test this by trying to telnet to it and the port the listener is listening on from the .104 machine:

   $ telnet xxx.yyy.19.84 4000 Trying xxx.yyy.19.84... Connected to MyPC (xxx.yyy.19.84). Escape character is '^]'. JDWP-Handshake ^]quit

   Look for the JDWP-Handshake string. If you get that, you know it can get to the remote debug listener on the .84 machine. Also note that when you quit the telnet session, the listener is stopped, so make sure to start it again!
4. Start up an SQL*Plus session from the .84 machine to the remote database. For example:

   sqlplus user/passwd@xxx.yyy.19.104/oracle

5. Connect to the remote lister on the .84 machine:

   SQL> set serveroutput on; SQL> execute DBMS_DEBUG_JDWP.CONNECT_TCP('xxx.yyy.19.84',4000);

   Note it is the .84 machine and of course port 4000 where the remote debugger is listening on.
6. In SQL Developer, set a breakpoint in a procedure you want to debug, e.g test_me. Tip: as a first try, set it at the first line of code in the method test_me, then you know for sure that if it gets called, it should stop there definitely.
7. Now call that procedure from the SQL*Plus commandline, e.g:

   DECLARE P_ANR VARCHAR2(25); P_RESULT VARCHAR2(4000); BEGIN P_ANR := 666; SOME_PCK.test_me( P_ANR => P_ANR, P_RESULT => P_RESULT ); DBMS_OUTPUT.PUT_LINE('P_RESULT = ' || P_RESULT); END; /

   
   Note the test_me method has an IN and an OUT parameter.
8. Your SQL Developer should now stop in the breakpoint you've set, see below SQL Developer screenshot of the Remote Debug session log window:
   

Remarks
I did not seem to need to run:

GRANT DEBUG CONNECT SESSION TO some_user; GRANT DEBUG ANY PROCEDURE TO some_user; /

You might still need to execute these commands if you get the permission errors you see in Step 5 here.

If you have a firewall on the .84 machine or between the two machines, you might need it to allow port 4000 to be accessed from the (other) DB machine...

References
Using SQL Developer to debug your anonymous PL/SQL blocks
Remote debugging with SQL Developer revisited
Application Express Community - Remote debugging
Sue's Blog... again... - Remote debugging with SQL Developer
Barry McGillin - Remote debugging with SQL Developer
Debug ApEx App with SQL Developer
Use Oracle SQL Developer to aid Oracle Application Express development
OTN Discussion Forums - Remote Debugging

Best of this Week Summary 12 October - 18 October 2009

• Last week was Oracle Open World 2009. Beside for Oracle DBMS and SOA specialists, these days the event is definitely also very relevant to Java professionals because of Oracle's (almost official) acquisition of Sun this year. Here's a set of relevant information from OOW:
  
  
  • The keynote on sunday which also describes the plans Oracle has with Java, MySQL and Sun hardware
    
  
  
  • James Gosling explaining to the developer audience what Sun was doing, what the scale is of Java activity around the world and across technology platforms.
    
  
  
  • The Dutch software company Amis also did quite a few presentations at OOW. Some of those presentations were already online before they were actually presented! All were interesting:
    
  
  
  • Good overview of what will be in the Patch Set 1 for the Oracle Fusion Middleware 11g stack, including JSF and ADF (mobile!). It's more than a patch!
    
  
  
  • Edition Based Redefinition (EBR): "Every database object (well, almost every database object - not tables!) can have different implementations/incarnations/versions in various editions. The object versions are all in the same schema - they only differ in the Edition they are created in."
    So no more direct table access! No big bang needed anymore. Challenges: what if a mandatory column is removed or added from the current EBR view (answer: special triggers). Including 2 suggestions for best practices.
    
  
  
  • XML processing/design tips.
    
  
  
  • This "session introduces SOA and the new Oracle SOA Suite 11g to the realm of database professionals from which it sometimes seems so far removed. What are the key SOA concepts and objectives? What is at the heart of Oracle SOA Suite 11g: composite applications, BPEL PM, and the mediator. The session shows how SOA services can be leveraged from the database, from triggers, PL/SQL units, or even SQL and how the database can publish events to the event delivery network. It covers how the SOA infrastructure can access the database, primarily using Oracle Database and Oracle Advanced Queueing adapter and how database developers can help in doing so efficiently. It ends with hints for applying SOA concepts to "normal" database development."
    
    Bit oriented at the database professional, but still interesting for those who want to stay up to date with Oracle's DMBS possibilities. And a bit scary too: it is possible in 11g to make webservice calls and publish them from the database(!). For example from PL/SLQ and put a webservice in front of PL/SQL... I haven't come up with a practical situation where you'd want to do this from an architectural point of view: large risk to getting tightly coupled systems and dependencies. If you have heavily invested (i.e built) in PL/SQL, it might be a valid option though.
    
  
  


• There is currently a renewed focus on the role of code generation in developing enterprise Java applications. Springsource recently released Roo, Skyway Software released Skyway Builder Community Edition version 6.3 and Blu Age released M2Spring. What should the software architects and developers look for in a Code Generation framework?
  


• Seven Wicket Do's and Don'ts.
  

Best of this Week Summary 20 April - 26 April 2009

• The big bigger biggest (sad?) news this week was of course Oracle going to buy Sun (here's Oracle's announcement). Rod Johnson (from SpringSource) is wondering what Oracle actually did buy. He concludes luckily one of the great strengths of Java is its developer and open source community, thus Oracle does not own the future of Java. Makes sense.
  
  Alef Arendsen (previously SpringSource, now JTeam) wonders which company/group will be the next, since only a few large opensource bodies are left: "the Apache Software Foundation (ASF), products in the Eclipse Foundation, products carying the JBoss name and last but not least, a broad set of products under the SpringSource umbrella".
  
  Sun "invited Oracle president Charles Phillips and chief corporate architect Edward Screven to an employee-only town hall last Wednesday", answering employee questions. In short: Oracle loves Sun's software (Java, MySQL) and hardware, but the future is unclear for OpenOffice and Sun's cloud effort.
  


• A beginners tutorial on how to integrate GWT with PHP using XML as the "bridge" between the two technologies.
  


• Manage your Amazon S3 with freeware CloudBerry Explorer. Makes it for example easy to move buckets between the US and Europe.
  
  Cloudkick is a free tool which allows you to migrate your Amazon EC2 AMIs to to Slicehost (owned by Rackspace).