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.
the errors associated with any objects created beforehand the USER_ERRORS
can be queried directly using the where clause to filter the particular
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.
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 users 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.
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
| 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 |
Copyright - © 2004 - 2017 - All Rights Reserved.