CODEWITHSUNDEEP
javaoracle-databasedebuggingplsqlplsqldeveloper
Oliver
Oliver

Reputation: 1269

Remote debug PL/SQL packages

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?

Upvotes: 2

Views: 1765

Answers (1)

GoodDok
GoodDok

Reputation: 1850

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.

Upvotes: 6

Related Questions