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

Lessons learned Seam 2.2 project

Quick post with some bullet points of lessons learned during my last (and first) Seam project:

• Seam 2.2
• Hibernate 3.3.1
• JEE 5
• EJB 3.0
• JSF 1.2
• Richfaces 3.3
• JBoss 5.1
• Drools 5
• SQLServer 2008
• MySql 5.x
• TestNG
• Hudson

Seam
- To have a navigation for a method with a parameter like exportSelected(String value): see Seam navigation based on a function with parameters
- If your Seam app keeps re-deploying (restarting) when using JBoss within Eclipse: delete all files that end with 'dia' in your WEB-INF dir. See: Seam keeps redeploying For example, I modified pages.xml, which apparently created a pagesdia (or similar) file... after removing that one, it worked fine again.

Hibernate
- To see which validator fails on a persist():

try { entityManager.persist(verbinding); } catch (InvalidStateException e) { for (InvalidValue invalidValue : e.getInvalidValues()) { System.err.println("--------------> create(): exception Instance " + "of bean class: " + invalidValue.getBeanClass().getSimpleName() + " has an invalid property: " + invalidValue.getPropertyName() + " with message: " + invalidValue.getMessage()); } throw new RuntimeException(e); }

MySql
- To see data in better format in MySQL: show engine innodb status\G (so add the \G to a query)

Hibernate/JPA
- NamedQueries are loaded and parsed at startup time, so that's an advantage above em.createQuery() which are only parsed and evaluated at runtime. So with NamedQueries you get the errors sooner.

Drools
To solve this error in Drools decision table .XLS spreadsheet:

[testng] DEBUG [test.service.drools.DroolsHandling] getKnowledgeBase: getting path info from settings service [testng] DEBUG [test.service.drools.DroolsHandling] getting test.service.drools.DroolsHandling ruletable file: HandlingModelTest.xls [testng] DEBUG [test.service.drools.DroolsHandling] getKnowledgeBase: Trying to open a File [testng] WARN [jxl.read.biff.NameRecord] Cannot read name ranges for Excel_BuiltIn__FilterDatabase_1 - setting to empty [testng] WARN [test.service.drools.DroolsHandling] There are errors in the decision table file [testng] WARN [test.service.drools.DroolsHandling] Decision table error: message=[ERR 102] Line 9:16 mismatched input '"DroolsHandling6"' expecting ']' in rule "DroolsData_12" in pattern stringDataset [testng] WARN [test.service.drools.DroolsHandling] line: 9 [testng] WARN [test.service.drools.DroolsHandling] Decision table error: message=[ERR 102] Line 9:35 mismatched input '"Prefab"' expecting ']' in rule "DroolsData_12" in pattern stringDataset [testng] WARN [test.service.drools.DroolsHandling] line: 9 [testng] WARN [test.service.drools.DroolsHandling] Decision table error: message=[ERR 102] Line 22:16 mismatched input '"DroolsHandling6"' expecting ']' in rule "DroolsData_12" in rule "DroolsData_13" in pattern stringDataset [testng] WARN [test.service.drools.DroolsHandling] line: 22

Make sure your definition has all the cells merged that contain a condition! For example, check the red arrow pointing at the border of the cells between G9 and H9.

The last cell for the condition Prefab is not merged (i.e one cell) with all the other conditions. Here's now how it should be: see again the red arrow, there's no cell separation anymore, all condition columns are now merged into 1 cell.

- A space in a condition in a decision spreadsheet can cause a non-match! E.g GATE VALVE is not matched. Fixed it by always replacing a ' ' with an '_'.