2

Let's say I have a running java web application, where I am trying to save some data, using JDBC that is calling a PL/SQL package in the database.

In the java code, the execute() function has been called, the data to be saved has been sent to the PL/SQL package in the database, which will process the data and eventually save it.

Is there a way to debug the called PL/SQL package, on the fly, with Oracle SQL Developer or PL/SQL Developer, do these IDE's support such remote debugging functionality?

Oliver
  • 1,218
  • 1
  • 17
  • 21
  • What do you mean by "debugging a **database**"? I am not familiar with the concept. –  Dec 28 '17 at 14:56
  • 2
    Interactive debugging of PL/SQL is possible, but only if we have prepared for it beforehand. Not least because it requires the granting of certain privileges. Please read [this blog by @thatjeffsmith which is a top-notch overview of the steps involved](https://www.thatjeffsmith.com/archive/2014/02/how-to-start-the-plsql-debugger/) – APC Dec 28 '17 at 15:00
  • @mathguy thx, wrote in rush, now corrected – Oliver Dec 28 '17 at 16:46
  • @APC thanks, I will check that out, hope this will work with a JDBC call to the procedure as well – Oliver Dec 28 '17 at 16:49
  • 1
    I object to this being closed as "too broad". Remote debugging is as on-topic programming as can be, and while there might possibly be more than one alternative, it is not like a wast sea of options. Asking "I need to save some data for an online shopping application I am working on, how do I do that?" is a too broad question. This one is not. – hlovdal Dec 29 '17 at 00:48

1 Answers1

6

What you are looking for is a remote debug feature available in Oracle SQL Developer. There are several prerequisites to be met:

  1. All packages you want to debug should be compiled for debug:
    alter package SCHEMA.PACKAGE_NAME compile debug;
  2. The user from which schema you want to perform remote debugging should be granted with the proper privileges:
    • grant debug connect session to USER_NAME; is for availability to connect to remote user's session
    • grant debug any procedure to USER_NAME; is for availability to debug procedures (including those bundled in packages)
    • if you use Oracle 12c, you should take into account additional ACL restrictions, take a look here and here, also note that there are some useful Oracle views like dba_network_acl_privileges to check your existing ACL privileges
  3. The remote user should be able to "give a signal" for debugger
    • grant execute on SYS.DBMS_DEBUG_JDWP to REMOTE_USER_NAME; to have access to its CONNECT_TCP procedure

If these requirements are met (or you are able to meet them), you may follow e.g. this entry in Sue Harper's blog (or this article in Oracle blog of the same author, or this one) to reach your goal. In short:

  1. You should locally start your debug listener on a specific port and set a breakpoint (or set the proper debugger option).
  2. Remote user should run exec DBMS_DEBUG_JDWP.CONNECT_TCP( 'ip-address', port ) before running the code of interest, this may be implemented on session logon trigger.
  3. Debug your code remotely.

There may be some more specific on this (with Oracle it is more than real), but that is how I usually deal with it.

Also I would like to mention that PL/SQL Developer unfortunately does not support the remote debugging (uses DBMS_DEBUG package instead of DBMS_DEBUG_JDWP), even in the latest version. Oracle SQL Developer is written in Java, PL/SQL Developer is not, I think this is the root of the problem.

GoodDok
  • 1,770
  • 13
  • 28