The DBA panel in SQL Developer displays sys privs
Ensure that
- you have been granted the DEBUG CONNECT SESSION priv
- you have EXECUTE privs on DBMS_DEBUG_JDWP
- you have EXECUTE privs on the object you want to debug
Once you have done ALL of that, you only have one more thing before you actually start debugging. It’s time to COMPILE our PL/SQL object WITH DEBUG. If you do not perform this step, you may find that your breakpoints aren’t honored and that your watches don’t actually watch anything. To compile with debug information, just do this:
Click on the dropdown control next to the compile (gears) button
Oh, I almost forgot to remind you – you have a test/dev environment, right?
You want to mind having any objects compiled for debug in your production environments. It adds a bit of nasty overhead and will impact performance. It won’t show up when you are running it, but if your app is calling it a few hundred times a minute, it will stack up on you in a hurry. So, if you see the little green widget next to any of your PL/SQL objects in production, just be sure to compile it again to remove it.
Alright, you have all the privs, you have compiled for debug, and you’re in a nice, safe, & cozy place to debug (not PROD! Ok maybe sometimes PROD). You are just one or two clicks away.
There is a ‘debug’ button you could hit, but don’t do that yet!
The debug button will start a debug session
Let’s say you DO hit that button. You might be a bit disappointed. You see, the default behavior of the debugger is to run until it hits a breakpoint or an exception. But, we haven’t set any breakpoints yet! If you do this, it will indeed start a debug session and run your program. But, it will also finish and you won’t really see much of anything except for the normal output of your program.
So instead, I want you to open the Preferences dialog under the Tools menu, and go to the Debugger page. Find the ‘Start debugging option’, and change it to ‘Step Over.’
Once you do that, you’ll find that the program is sitting at the first line of executable code waiting for you to take over.
Just one more thing
Before the debug session begins, you’ll see a dialog that looks EXACTLY like the ‘Run PL/SQL’ one. You will need to input any required IN parameters. In addition if you want to debug a package member, be sure you actually select the right target.
And that’s about it for STARTING your debug session. There’s actually another way to start a debug session from OUTSIDE of SQL Developer, but I’m going to save that for another episode. I may even do it in IMAX 3D so I can start to recoup some of these production costs…step over versus into, watches, breakpoints, call stack, etc will be covered in the upcoming post as well.
Twitter
RSS
GooglePlus
Facebook
Debugging PL/SQL with SQL Developer: Episode IV
Dec 12, 2011 @ 09:27:59
[...] Skip to Episode V – Starting the Debugger [...]
Dec 16, 2011 @ 06:07:50
Hi Jeff,
just experimented with the plsql debugger in sqldev. Really cool stuff!
There is 1 tiny mistake in the blogpost btw: you need execute privs on DBMS_JDWP_DEBUG.
regards,
Tony van Esch
Dec 16, 2011 @ 10:04:52
We’re both wrong, it’s actually dbms_debug_jdwp, but good catch!
Jul 18, 2012 @ 11:47:19
How this can be Run/Debug from SQL Developer.
create or replace
PACKAGE test_pkg_emp AS
TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE t_varchar IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
FUNCTION inst_emp(P_EMPNO IN T_NUMBER,
P_ENAME IN T_VARCHAR DEFAULT CAST(NULL AS T_VARCHAR) ) RETURN NUMBER;
END test_pkg_emp;
create or replace
PACKAGE BODY test_pkg_emp AS
FUNCTION inst_emp(p_empno IN T_NUMBER,
p_ename IN T_VARCHAR DEFAULT CAST(NULL AS T_VARCHAR)
) RETURN NUMBER
AS
v_empno number;
BEGIN
FOR i IN P_EMPNO.FIRST..P_EMPNO.LAST Loop
insert into emp(empno,ename) values (p_empno(i),p_ename(i));
END LOOP;
SELECT COUNT(*) into v_empno FROM emp;
RETURN v_empno;
END inst_emp;
END test_pkg_emp;
From SQLPLUS this can be called as
DECLARE
v_eno test_pkg_emp.t_number;
v_ename test_pkg_emp.t_varchar;
V_No NUMBER;
BEGIN
v_eno(1):=9890;
v_eno(2):=9891;
v_ename(1):=’test1′;
v_ename(2):=’test2′;
v_no:=test_pkg_emp.inst_emp(v_eno,v_ename);
END;
/