Debugging PL-SQL calls from Java Session Using Eclipse and SQL Developer

最新推荐文章于 2020-06-08 17:14:36 发布

置顶个人知识管理布道师

最新推荐文章于 2020-06-08 17:14:36 发布

阅读量884

点赞数 1

分类专栏： Oralce数据库文章标签： java plsql 调试

本文链接：https://blog.csdn.net/u010689306/article/details/51900089

版权

Oralce数据库专栏收录该内容

46 篇文章 1 订阅

订阅专栏

1 问题背景

最近在做自己的开源项目,使用jdbc去调用oracle的一个分页存储过程,如下图,1出执行的代码始终得不到数据(rs.next=false).几经折腾,在eclipse中调试java代码,没有找到原因.

在pl/sql developer中调试存储过程,也没有找到原因,由于中pl/sql developer中调试存储过程需要手动给参数赋值,结果每次cursor变量都有值,但就是java中调用没有值.后来很想看到在调试java代码中的存储过程时,能否同时进入pl/sql developer中,调试存储过程sql代码,等存储过程sql代码调试完,再返回java中,接着调试java代码.带着疑问,在google上不断搜索.以下就是解决方案.

2 详细步骤

a)Open the package in Oracle SQL developer. Right click both body and spec and compile for DEBUG

Note: If the package is using some user defined type objects as input or output then compile those objects for DEBUG.

b)Go to tools -> Preferences -> Debugger. Select the option Prompt for Debugger host for Database Debugging.

c)Right click the DB connection and start the remote debug session

It will open the Listen for JPDA dialog. In Local address enter the ip address of the machine and hit OK.

This will start the debug listener as follows.

d)Now in the java program calling the PL/SQL package before the actual PL/SQL call enter the following code

//For debugging only

CallableStatement debugStatement = getCallableStatement("begin DBMS_DEBUG_JDWP.CONNECT_TCP( '<ipaddress provided above>', 4000 ); end;"); //Note the ipaddress and port – Same as the values provided in the previous dialog.

debugStatement.execute();

// execute the actual PL/SQL call

statement.execute();

In my case :

e) Now open the PL/SQL package and put a break point where you want to debug something.

When you invoke the PL/SQL program from Java

It will stop at the first break point in PL/SQL as follows. Note that you can see the values passed by Java in the user defined object. The objects appearing as opaque needs to be recompiled for debug. Now you can debug through each line in PL/SQL much like java code