13 July 2006

Remote Debugging with SQL Developer

It occurs to me that while we talk about using the remote debug facility in SQL Developer that you may not know how to use it. So in a few steps and with a few screen shots, I'd like to show you how it's done.
(Please note, to see any of the images clearly, just double click on them.)
1. Let's start with a connection to the database. Create a database connection. (File -> New Connection) and complete the details. I only use the basic tab, so don't need to set up tnsnames or anything else. You'll see the database is on my own machine in this example, but it need not be.




















2. Now you can connect to any objects this user owns, using SQL Developer. You can browse the various objects the user has access to. I'm only interested in this procedure, which my user HR owns.














3. You can run and debug this procedure in SQL Developer, but that's not the purpose of the exercise. What we want to do is debug the procedure when it is executed from elsewhere, such as another programme or application. The starting point is to start a remote debug session in SQL Developer. You do this from the Database Connection as shown.














4. A dialog will display, requesting the listening port number and the IP address of the machine running SQL Developer - effectively it's your computer, listening for the database to connect and using that port range to do so.
You can set the range of ports through a Preference in SQL Developer. If you run into issues, look out for firewalls between the machines, blocking these ports.













5. Once you have set the remote debug details, you should see the run manager display these.











6. Now you should start a remote session. Using a SQL *Plus command line session will do.

* Invoke SQL *Plus for this user
* In the SQL *Plus session enter the following command:
exec DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', 4000 );

You should recognize the parameters from the previous dialog.

If you are debugging an application remotely, then this PL/SQL procedural call will need to go into that application, just for the debug purposes. You'll remove it afterwards.












7. Return to SQL Developer and set a breakpoint in the procedure.
Note: If you are debugging a procedure, you must remember to Compile for Debug, before you can start debugging.















8. Now you need to return to the SQL*Plus session and execute your procedure. If you debug in SQL Developer, an anonymous block is created for you to execute the procedure. In this case you'll need to write one to execute the procedure from SQL*Plus.

Note: In the procedure we have a DBMS_OUTPUT command, so you should also add the 'Set Serveroutput on' command.











9. As soon as you execute the anonymous block, you'll be returned to SQL Developer to debug the session there. Step into the code as you would in a usual debug session.
















10. You can watch data and modify values in the same way as a normal debug session.











In this example, I'll modify the hire date.
















11. Once you have reviewed the data, or made the modifications you want, resume debugging. Once the debug session is complete the control is returned to your remote session. In this case, SQL*Plus. You'll notice the modified date reflected in the output.












Quite a long piece, I know, but I think we often veer away from the unknown or untried. Hopefully once you have walked through an example, you might be able to make more use of this very useful feature.

39 comments:

Ofir Manor said...

Hi Sue,
This is great blog entry!
Just wanted to add that in many cases, we can't make the production application run the dbms_debug_jdwp, since we don't have access to its session. However - we can still use remote debugging!
The secret is to create a trigger on logon, and in it to selectively enable remote debugging based on username, hostname, client_info etc...
this way, we can remote debug a problem in an application just by creating the trigger, adding a breakpoint, asking the user to logoff, logon, and taking over.
Ofir

Sashidhar Kokku said...

Hello Sue,
Good blog entry. As a side note, I also wanted to let you know that me and a bunch of my collegues who use Sql Developer to interface to Oracle...find it to freeze oh-so-often. So much so that sometimes , we have to simply close it and run it again.
Is it because that its a freeware that Oracle is not spending time to make it a quality product?
Can you shed some light on this please.

Thanks,
Sashidhar Kokku

Sue said...

Ofir,

Thank you for the feedback. I am sure other users will be able to make use of your suggestion.

Sue

Sue said...

Sashidhar,

Thank you for your response. I am glad you are using the product and sorry it's not all that satisfactory in all areas for you. I've just finished writing a workshop for SQL Developer, which means I spend a healthy portion of my day in the product creating exercises and working back and forth. Over the years at Oracle, as both course writer and PM, I spend a very good portion of my time with pre-production software and early releases. I think that SQL Developer is robust. Perhaps I am not beating up on the product like you.;-)

It does bring me to another question tho'. Because the product is free you assume we don't spend time on it? Would charging you a tonne of money make you feel better? It'd be better for me! I'm happy to porpose this to management...;-)
Seriously though, we have a dedicated team of developers for SQL Developer who love the product and love working on the team and are every much as dedicated to this project as any of the development teams I have ever worked with. We are all committed to bringing you the best product, so that you have no doubt about which to select when looking for a SQL and PL/SQL development tool. The tool and team is not yet a year old, things will only get better.

Sue

Arul Ramachandran said...

Hi Sue,

Interesting blog on SQL Developer!

I use SQL Developer version 1.0.0.15.27 almost every day now and am pleased with it. Just recently I started using the Debug functionality and it was fun. I am a bit skeptical on the quality of the product though, because when I downloaded version 1.0.0.15.57 (I think), it would not even connect to the database. I deleted this newer version and went back to the older version and everything was hunky-dory.

Thanks,
Arul Ramachandran

Sue said...

Arul,

Thanks for the input. I am glad you are using SQL Developer regularly now. I am surprised the 15.57 release did not work for you as this is the release the majority of our users are on. (including me!) We have a new release planned for later this year and the team are always focused on quality. I hope you'll find the next release worth waiting for.

Sue

Anonymous said...

Hi Sue,
I am trying to use this remote debugging feature. I got everything except when I execute the procedure it doesn’t stop at the breakpoint. It just runs to the end.
Am I missing something here…do I need any privileges?
Thank you,
Prakash

Arun Gupta said...

Few things that I did to make it work:
01. To be able to debug a procedure or package owned by a user, the user must be granted DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges.
02. I substituted my own client machine's IP address instead of localhost and was able to get remote debug to work from a remote database server. I actually debugged a package owned by SYSMAN which is used in Oracle Dbconsole.
03. Before starting remote debug between a client PC and a database server, please ensure that there is no firewall blocking the port 4000 and that the port is actually free. In my case, the port 4000 was blocked so I used port 80. To test if a port is free or not on your PC, run telnet (Your PC's IP address) (port). If you get an error like:
Connecting To 168.192.55.39...Could not open a connection to host on port 80 : C
onnect failed

then there is nothing listening on this port on your local PC and it is free. Now start the remote debug listener from SQLDeveloper. Again do the same telnet from your PC. Now you will get:
JDWP-Handshake

This means local PC is setup right. Notice that if you close the telnet window, the remote debug listener will stop. Start it again on the same port and using your local PC's IP address.

Now run the same telnet from the database server.

telnet (your client PC's IP) (port)

You should again see
JDWP-Handshake

If this is working, then you are all set. If you are unable to telnet into your local PC from the database server, then try using a different unused port or have the port opened.
04. The dbms_debug_jdwp.connect_tcp is called with both local PC's IP address and port in single quotes.

dbms_debug_jdwp.connect_tcp('192.168.55.39','80');
I put this line right above the select statement I was trying to debug after the begin.

Hope these tips will help. All the above testing was done on 10gr2 client and 10gr2 database.

olaf said...

Hi Sue,

Great post, it works instantly (SQL Developer 1.0.0.14, db 9.2.0.4).

However I am trying to see some Spatial objects' values. These show up OPAQUE in values/watches.
Is there a way to see the actual object values or do you know if there are any API's to use in an extension to see the values ?

Thanks,
--olaf

Sue said...

Thanks for the feedback Olaf. We don't support Spatial features well in SQL Developer 1.0 and need to look at updating that in future releases.

We have just launched SQL Developer Exchange(http://sqldeveloper.oracle.com)

On this site, users can log enhancement requests, such as support for Spatial Objects. While I am aware we need this and we do have plans for more support, it would be good to see how others feel, as the rest in the community can vote on feature requests.

Sue

Anonymous said...
This comment has been removed by a blog administrator.
Sue said...
This comment has been removed by the author.
Mike Anderson said...

Thanks for the blog entry. I am having problems getting this to work trying to connect from my pc to the oracle (9.2.0.7) box (aix 5.3). Is this possible for me or am I spinning wheels?

Mike

Mike said...

I should clarify. SQLDeveloper connects fine. It is at step four that I am having problems. I get the message "The debugger is not able to listen for JPDA using the specified parameters."

Regards,

Mike

Anonymous said...

Hi do you need to install a DBMS_DEBUG_JDWP package?
And if so what is the name of this package is it part of
the DBMS_DEBUG package dmspb.sql and prtpb.plb?

Regards
David Svennevid

Aníbal said...

Sue, thanks a bunch for sharing your knowledge on the subject, pretty interesting. When debugging some packages that use user-defined datatypes (pl/sql records), the values of those fields are shown as OPAQUE in the data or smart data tab. Is this a bug? Are the user-defined datatypes not supported to show the values? Thanks for your response.

Anonymous said...

Sue, thanks a bunch to share your knowledge on the subject, pretty interesting. I have a question: when debugging some store procedures that use user-defined datatypes (pl/sql records), the values of the these variables are shown as OPAQUE. Is this a bug? Are user-defined variables not supported to show their values yet? Thanks in advance for your response. - Aníbal

dune218 said...

Hi Sue,

I am trying out this debugging-thing, but to get it started is unnecessary complex because there is lack of documentation and errors in documentation.
If the following two points would have been explained, it would have been easier:
1. who connects to whom?
I have remote databases, and starting debugging on 127.0.0.1 then does not work. To make it work, I first had to find out that the address was supposed to be the address of the PC running SQL debugger. (I thought it was the db-server, since the db-objects are debugged)
2. there is an error in your tutorial. When it reads " Invoke SQL *Plus for this user
* In the SQL *Plus session enter the following command:
DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', 4000 );
"
This does not work, since it misses 'call' or 'exec' at the beginning. (it is in the picture though)

Hope this helps!

Sue said...

Thanks for the update, I have added "exec" into my command. Feedback always helps. It seems I should create another entry to repond to the issues here - or even just update this! Thanks too for the doc comment. I guess that's why I did this blog entry. The functionality has been in JDeveloper for some time and not many folk have used it. I'll file a doc bug.

Regards
Sue

Romit said...

Hi Sue,

Pretty Helpful! Thanks for the tip. It was great.

Anonymous said...

dear Sue
It seems that i am unable to connect to the database.
i get this error:
"Status:Failure -io exception:The network adapter could not establish the connection"

Sue said...

Hi Anonymous,

Which database? This doesn't sound like a SQL Dev error, but a generic "can't connect to your database" error. I get it when might tns listener is not up, or the database doesn't reconize the listener. I restart both and am fine again.
It's probably best to log your query on the SQL Dev Forum. There have been queries and solutions to this on the SQL Dev forum, that might help. The forum is here:http://forums.oracle.com/forums/forum.jspa?forumID=260

Sue

martin k. solomon said...

Sue,
I have a connection in Sql Developer to a database on a remote computer.
I would like to debug a pl/sql
procedure in that database by running the procedure from the connection tree. However,
the Debugger does not stop on the first breakpoint in the procedure. It just drops through to the end of the procedure. Do I need to use
remote debugging to get this to work? If so, exactly how should I do this?

Thanks,
Marty Solomon

Anonymous said...

Hi Sue!
I'm new with this tool. We want to use it for debug pourposes, but when we set the Debugger - Listen for JPDA, it never shows up in the Run Manager list, and of course we cannot debu, we have the rns packet failure error.

Could you help us?

thanks a lot!

Patricia

Anonymous said...

This seems to apply to SQLDeveloper 1.0 and things have changed in 1.1.

In particular, the initial remote debug setup is no longer 'Listen for JDPA', but 'Attach to JDPA'. This implies that there should be a listener at the server end.

Anonymous said...

I have trouble getting even the first step to work. It will be very helpful, if someone can explain what happens due to the first step. What does the debugger connect to? Is there a process listening in port 4000 of my local machine when I start remote debugger? Or should I do the SQL*Plus step first so that it will start a process that listens in port 4000. A clear guide line with more explanation will make it easier to understand.

I am trying to think along the lines of Java debugging. As you know the remote JVM listens on a particular port and we attach to that JVM from our Java IDE by specifying the machine name and port number of that remote JVM. In this is case it is very clear what is listening and the where the IDE is connecting to.

If specifying the localhost for the debug step is not confusing enough, lock of clear explanation makes it even harder to understand this.

Any help will be much appreciated.

Peeyush said...

Hi,
I'm using version 1.1.2 . Is there no way to debug procedures defined in a package. It just says, source does not have a runnable target.

Sue said...

Peeyush,

Yes, you can. Be sure you select the package name and then hit the debug button. The dialog that you are then given carries the list of all procedures in the package and you can select the one you want to run.

Sue

mingkit said...

I have try to debug on Package and added breakpoint on body. When execute debug of package, it never stay on the breakpoint on the package's body but it can finish.

I also observe is not work of step into... i am using version 1.2.1

Ali said...

is there any documentation to dbms_debug_jdwp anywhere?

I notice that if you desc the package, there are more params for the tcp_connect call, including sid and serial.

is it possible to use these to register a known session from a different sqlplus session for instance?

from what I can see, the only way I can get an 'listen' and intercept a call is by changing each app to call the ...tcp_connect - over an above the logon trigger idea

Borto said...

using the SQLPlus to execute the DML command that will force the use of the trigger, I can work without generate any error on SQLDeveloper

Anonymous said...

Wow, this feature is *Really* useful.

One quick tip, if user-defined datatypes, show up with type OPAQUE in the Data window of the debugger, then that means you need to compile those types with debug enabled too.

Paresh said...

Hi Sue,

How do I stop/terminate Remote Debug **Listener** without closing SQL Developer?

Thanks,
Paresh

Anonymous said...

Great post, extremely clear. Thanks!

Joe said...

Hi Sue,

First of all, thanks to you and the team for all your hard work on this product. I enjoy using it and like many things about it. BUT.... (you knew there was gonna be a but, didn't you ;)

Having used it extensively for PL/SQL remote debugging on a real and complex product, I have to say it's a frustrating and flaky experience. Maybe this is down to my own mistakes - but in that case, I think the product could do more to flag up what the mistakes I am making actually *are*. Here are some of the problems I have experienced:

*Product frequently fails to un-grey-out the debug navigation buttons even when it has hit a breakpoint. One workaround I have found - use 'Run to cursor' from the context menu. This usually prods it into reenabling those buttons. Definitely looks like a bug.

*Product starts the Debug Listener but silently fails to debug if the user does not have the right access grants set. Surely it should report an error?

*There are two ways of doing "Compile for debug" on a package body - right click in the navigator tree control, or right click in the source code. Only the second way actually appears to compile with the debug info. The first way just does a regular compilation. Looks like a bug.

*The little green ticks on breakpoints which indicate that SQL Developer will actually break on them appear and disappear apparently at whim. Again, probably due to my stupidity - but if SQL Developer decides it won't break on those breakpoints, can it at least indicate why?

*Putting 'localhost' into the "Debugger - Listen for JPDA" dialog only works if your DBMS is on your machine. Otherwise you have to specify your debugger machine's full IP address. Your tutorial could be clearer about this - and perhaps SQL Developer could pop up a warning to this effect.

OK I'll stop moaning now. Again, great product in many ways, but I think it could use a little more work on its implementation of remote debugging. I am using v1.5.1.

Thanks, Joe

Anonymous said...

I found I could fix an otherwise unsolvable "Source does not have a runnable target", just by stopping and restarting Sql Developer

Anonymous said...

Hi,
I'm grateful for the excellent post on debugging PL/SQL in SQL Developers.

Thanks!
Mitch

Anonymous said...

Excellent. thanks

Hoyt Velasquez said...

These are indeed great tips in SQL remote debugging. Thank you for posting.