Exploring Debugging Procedures in Oracle10g

Debugging is the art of finding and reducing the number of bugs or defects in a program and varies a lot with each programming language and the tools available. The ability to debug procedures, triggers and functions in oracle is made easier with the advent of oracle 10g. Special commands and utilities are generally used to detect errors.

In SQL , one way of debugging is to use the ‘show errors’ command. This command when executed shows the errors associated with the last created procedural object. It displays the line and column number for each error along with the error text from the USER_ERRORS data dictionary view object.

To view the errors associated with any objects created beforehand the USER_ERRORS can be queried directly using the where clause to filter the particular object.
Example :
Select line,
position,
text
from USER_ERRORS
where name =’EMP_PROC’
and type =’PROCEDURE’
order by sequence;

In addition to this method we can also use the DBMS_OUTPUT package utility to debug. To use this we need to have the set serveroutput on command before execution of lines of the procedure text. There are three debugging functions which can be executed within this package. PUT and PUT_LINE can be used to display the debugging information required.
Example:
DBMS_OUTPUT.PUT_LINE(‘ With Loop the value of Amount is: ‘||Amount);

The PUT puts multiple outputs on the same line and PUT_LINE puts each output on a separate line. The NEW_LINE used with PUT signals the end of the current output line.

Another method of debugging PL/SQL is by using SQL*Debug. It can be invoked from anywhere in PL/SQL. It prints the debug messages into a windows program.

Example : OraDebug.debug(‘Inside Procedure Employee Detls’);

The SQL*Debug windows program can be used to register for debug messages from a user’s particular session. Only if there is a debug program listening for the messages, the debug messages will be generated and printed. It can also be used to monitor PL/SQL packages. It does not degrade the performance because it is only executed when an error occurs.

The new Oracle SQL Debugger has many features to debug SQL functions and procedures. A user can set a break point at any point in the function or procedure source code in order to execute up to the line. These breakpoints can be viewed in the breakpoints tab. The execution of code can be stopped at any time using the stop button and the output messages can be viewed in the console tab. The step over button allows movement to next line of code. Variables and their values can be viewed in the variables tab. A query window can be opened to run queries before debugging or during debugging when there is a pause.

It is also possible to debug PL/SQL code in oracle with DBMS_DEBUG. Here two sessions are needed, a debugee session and a debugger session. The debugee session is the session where the pl/sql code is run while the debugger session is used to control the debugee session and debug it by setting breakpoints, clearing them and querying variable’s values.

In Oracle10g, conditional PL/SQL compilation is one of the ideal tools for debugging PL/SQL. It can be used for altering the source code on a selective basis depending on compiler directives. The functionality in a PL/SQL application can be customized without removing source code, to utilize the latest functionality with the latest database release and disable new features when applications runs in an older release versions of the database. Also it can be customized to activate debugging or tracing functionality in the development or testing environment and hide it in production environment if required.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |