Solved

How to debug a package?

Posted on 2002-03-25
9
4,740 Views
Last Modified: 2007-11-27
How can I debug a package in an oracle 7.x (and up) database?

0
Comment
Question by:pfjluik
9 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 6893922
I know 2 ways to debug packages. The first is to use the debug tools of the Procedure Builder. The second is to use the package DBMS_DEBUG PL/SQL supplied package.

This is an example of running a procedure from one
session and using dbms_debug to debug it in a second.  The dbms_debug package
is provided to allow users to build their own debuggers for debugging server
side program units.  The example shown here is to give the reader an idea of
the calls required.  


Program Notes
-------------

This example was run on Solaris using 8.1.7.1 of Oracle.


References
----------

The dbms_debug package header provided in $ORACLE_HOME/rdbms/admin/dbmspb.sql
contains explanatory notes throughout.

Also see the Oracle8i Supplied PL/SQL Packages Reference.


Caution
-------

The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services.  It has been tested
internally, however, and works as documented.  We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.


Example
-------

Session 1                        Session 2
--------------------------------------------------------------------------------------------

create table emp2 as
   select * from emp;

create procedure testproc is
begin
  update emp2
  set sal= nvl(sal, 0) * 1.20
  where deptno =10;
  dbms_output.put_line('Rows: '||
                       sql%rowcount);
end;
/

alter procedure testproc
compile debug;

------------

-- Note if serveroutput is set on in
-- this session Session2 will need to
-- do an additional synchronize/continue
-- for testproc to return since sqlplus
-- calls dbms_output.get_lines which
-- will also wait to be debugged.

var x varchar2(50)
exec :x := dbms_debug.initialize()
print x
exec dbms_debug.debug_on
                              set serveroutput on
                              exec dbms_debug.attach_session('000D09CF0001')
                                                
                              -- Set a breakpoint at line 3 (must be an
                              -- executable statement)
                              declare
                                info dbms_debug.program_info;
                                bnum binary_integer;
                                ret binary_integer;
                              begin
                                info.namespace :=
                                  dbms_debug.namespace_pkgspec_or_toplevel;
                                 info.name := 'TESTPROC';
                                 info.owner := 'SCOTT';
                                info.dblink := null;
                                info.line# := 3;
 
                                ret := dbms_debug.set_breakpoint(info,3,bnum);
                                if ret != dbms_debug.success then
                                   dbms_output.put_line(
                                    'Failed to set break point');
                                end if;
                              end;
                              /
exec testproc
                              declare
                                info dbms_debug.runtime_info;
                                ret binary_integer;
                                 source varchar2(4000);
                                 -- Set runtime information mask since default
                                          -- info is only info_getStackDepth +
                                -- info_getLineInfo
                                mask pls_integer :=
                                             dbms_debug.info_getstackdepth +
                                     dbms_debug.info_getbreakpoint +
                                   dbms_debug.info_getlineinfo +
                                   dbms_debug.info_getoerinfo;
                                -- Set breakflags to step to next executable
                                -- statement
                                breakflags pls_integer :=
                                   dbms_debug.break_next_line;
                              begin
                                -- Start the interpreter
                                ret := dbms_debug.synchronize(info,0);
                                if ret != dbms_debug.success then
                                  dbms_output.put_line('Synchronize failed');
                                end if;
                                if info.reason =
                                  dbms_debug.reason_interpreter_starting
                                then
                                  dbms_output.put_line('Interpreter starting');
                                end if;

                                -- Continue to break point
                                ret := dbms_debug.continue(info,0,mask);
                                if ret != dbms_debug.success then
                                   dbms_output.put_line('Continue failed');
                                end if;

                                -- Output info returned
                                dbms_output.put_line('Line is '||info.line#);
                                if info.terminated = 1 then
                                   dbms_output.put_line('Program has terminated');
                                end if;
                                dbms_output.put_line('Breakpoint # is '||
                                                 info.breakpoint);
                                dbms_output.put_line('No. of frames on stack is '
                                                 ||info.stackdepth);
                                if info.reason != 3 then
                                  dbms_output.put_line(
                                    'Program suspended but not for breakpoint');
                                end if;
                                dbms_output.put_line('Program name is '||
                                                 info.program.name);
                                -- Find the line of code the break is on
                                select text into source from all_source
                                where  owner = info.program.owner
                                and    name  = info.program.name
                                 and    line  = info.line#;
                                dbms_output.put_line('Source code is '||chr(10)
                                                               ||source);
                                if info.oer != 0 then
                                  dbms_output.put_line('Exception is '||
                                                   sqlerrm(info.oer));
                                end if;

                                -- Step to next line and print it out
                                ret := dbms_debug.continue(info,breakflags,mask);
                                if ret != dbms_debug.success then
                                  dbms_output.put_line('Continue failed');
                                end if;
                                dbms_output.put_line('Line is '||info.line#);
                                select text into source from all_source
                                where  owner = info.program.owner
                                and    name  = info.program.name
                                and    line  = info.line#;
                                dbms_output.put_line('Source code is '||chr(10)||
                                                 source);

                                -- Continue to end of program now
                                ret := dbms_debug.continue(info,0,0);
                                if ret != dbms_debug.success then
                                  dbms_output.put_line('Continue failed');
                                end if;
                              end;
                              /
exec dbms_debug.debug_off
                              -- Need this since debug_off waits to be debugged
                              -- (Enhancement Request, bug 690796)
                              declare
                                info dbms_debug.runtime_info;
                                ret binary_integer;
                              begin
                                ret := dbms_debug.synchronize(info,0);
                                ret := dbms_debug.continue(info,0,0);
                              end;
                              /

                              exec dbms_debug.detach_session

--------------------------------------------------------------------------------------------


Sample Output
-------------

Output from Session 2 is:

Interpreter starting
Line is 3
Breakpoint # is 1
Number of frames on stack is 2
Program name is TESTPROC
Source code is
  update emp2

Line is 6
Source code is
  dbms_output.put_line('Rows: '||
0
 
LVL 1

Expert Comment

by:chdba
ID: 6894041
I can recommend you PL/SQL-Developer.

It's a nice tool - not for free, but the best I know for Oracle.
(Definitive better than procedure builder)

chdba
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6894475
There are multiple ways to debug PL\SQL packages.  Oracle's Procedure Builder is unfortunately probably one of the worst tools to use for this.

The cheapest option may be a combination of a text editor and SQL*Plus.  You will have to add some "dbms_output.put_line..." commands at various places in your PL\SQL code, then compile it and execute it.  You do need to "set serveroutput on" in SQL*Plus before running your PL\SQL code.

Unfortunately the output from dbms_output is not displayed line-by-line in real time, it is all displayed after the PL\SQL block finishes.  If you use this approach, it is best to add a debug_flag parameter to your PL\SQL blocks so the messages can be displayed when you want, and turned off when you no longer need them simply by passing a different parameter value (rather than editing your code again to remove all of the "dbms_output..." lines and recompiling).

The TOAD tool (now owned by Quest Software) has a good GUI debugger for PL\SQL.

There are other third-party tools also that can debug PL\SQL.
0
 
LVL 9

Expert Comment

by:konektor
ID: 6896174
you have to
alter package <package_name> compile debug;
alter package <package_name> compile debug body;

ant than you can use
- Oracle procedure builder
- TOAD
- SQL Navigator
to debug <package_name>.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:cybotto
ID: 6896767
unfortunatly I can't use this above suggestion.

One way to keep track when a package is falling over is to include a variable called field_id and every time a cursor opends, some logic or string manipulation is done I assign field_id a value like "bofore IF i<10 THEN" or "before open loop". In the exeption block I have a call to an error handler which inserts all errors/exeptions into an error_log. This has the advantage that different users can generate errors and don't need to remember what they did to generate it. It is very useful when other people using it.

To keep track of variables I normaly use HTP.P('<br>debug variable: '||variable) when accessing the package over the net, or dbms.putline('debug: variable'||variable). When the debug is not needed a simple search and replace of the package will remove/show all debug information like

HTP.P('<br>debug

replace with

--HTP.P('<br>debug

To keep track of time elapsed between some code block I normally use

HTP.P('<br>time place'||TO_CHAR(SYSDATE,'SSSSS'));

This has the advantage that it can be used in many programming languages without learning all this different debuggers.
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7063082
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7064662
Please close as a 0-point question since there is useful information in these reponses.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7082277
Lacking timely response from pfjluik or contributing experts, but seeing that there is useful information here, I recommend:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Accepted Solution

by:
Mindphaser earned 100 total points
ID: 7095997
moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now