Oracle
  Home arrow Oracle arrow Page 2 - Debugging PL/SQL Code
Dev Shed Forums 
Administration  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Download TestComplete 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ORACLE

Debugging PL/SQL Code
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 6
    2007-12-13

    Table of Contents:
  • Debugging PL/SQL Code
  • Tracing Execution of Your Code
  • Controlling trace
  • Tuning PL/SQL Programs

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
     
    ADVERTISEMENT

    Virtual Tradeshows by Ziff Davis Enterprise - A Unique Opportunity to Connect with IT Experts, Access Information, and Gain Insight on today's Technology

    Debugging PL/SQL Code - Tracing Execution of Your Code
    (Page 2 of 4 )

    Earlier versions of Oracle offered some PL/SQL trace capabilities, but Oracle8i Database introduced an API that allows you to easily specify and control the tracing of the execution of PL/SQL procedures, functions, and exceptions. The DBMS_TRACE built-in package provides programs to start and stop PL/SQL tracing in a session. When tracing is turned on, the engine collects data as the program executes. The data is then written out to the Oracle Server trace file.

    In addition to DBMS_TRACE, you can take advantage of the built-in function, DBMS_UTILITY.FORMAT_CALL_STACK, to obtain the execution call stack at any point within your application.

    The PL/SQL trace facility provides a trace file that shows you the specific steps executed by your code. The DBMS_PROFILER package (described later in this chapter) offers a much more comprehensive analysis of your application, including timing information and counts of the number of times a specific line was executed.

    DBMS_UTILITY.FORMAT_CALL_STACK

    This function returns the execution call stack (the sequence of program calls) down to the point at which you call the function. Here is an example of the formatting of this stack string:

      ----- PL/SQL Call Stack ------
      object   line    object
      handle   number  name

      88ce3f74   8  package
                    STEVEN.VALIDATE_REQUEST
      88e49fc4   2  function STEVEN.COMPANY_TYPE
      88e49390   1  procedure
                    STEVEN.CALC_NET_WORTH
      88e2bd20   1  anonymous block

    One of the best places to use this function is within an exception handler, as in:

      EXCEPTION
        
    WHEN OTHERS
        
    THEN
          
    DBMS_OUTPUT.PUT_LINE (
             DBMS_UTILITY.FORMAT_CALL_STACK); 
      END;

    Better yet, grab this information and write it to your log table, so that the support and debug teams can immediately see how you got to the point where the problem reared its ugly head.

    There is, by the way, one big problem with the exception section above: if your call stack is deep, the formatted string will exceed 255 characters in length. Before Oracle Database 10g Release 2, DBMS_OUTPUT.PUT_LINE would raise an exception in such cases. To avoid this problem, you might consider using Darko Egersdorfer’s callstack package, found in the callstack.pkg file on the book’s web site.

    Installing DBMS_TRACE

    This package may not have been installed automatically with the rest of the built-in packages. To determine whether DBMS_TRACE is present, connect to SYS (or another account with SYSDBA privileges) and execute this command:

      BEGIN DBMS_TRACE.CLEAR_PLSQL_TRACE; END;

    If you see this error:

      PLS-00201: identifier 'DBMS_TRACE.CLEAR_PLSQL_TRACE' must be declared

    then you must install the package. To do this, remain connected as SYS (or another account with SYSDBA privileges), and run the following files in the order specified:

      $ORACLE_HOME/rdbms/admin/dbmspbt.sql

      $ORACLE_HOME/rdbms/admin/prvtpbt.plb

    DBMS_TRACE programs

    The following programs are available in the DBMS_TRACE package:

    SET_PLSQL_TRACE
      
    Starts PL/SQL tracing in the current session

    CLEAR_PLSQL_TRACE
      
    Stops the dumping of trace data for that session

    PLSQL_TRACE_VERSION
      
    Gets the major and minor version numbers of the
       DBMS_TRACE package

    To trace execution of your PL/SQL code, you must first start the trace with a call to:

      DBMS_TRACE.SET_PLSQL_TRACE (trace_level INTEGER);

    in your current session, where trace_level is one of the following values:

    • Constants that determine which elements of your PL/SQL program will be traced:

        DBMS_TRACE.trace_all_calls   constant INTEGER := 1;
        DBMS_TRACE.trace_enabled_calls
                                     constant INTEGER := 2;
        DBMS_TRACE.trace_all_exceptions
                                     constant INTEGER := 4;
        DBMS_TRACE.trace_enabled_exceptions
                                     constant INTEGER := 8;
        DBMS_TRACE.trace_all_sql     constant INTEGER := 32;
        DBMS_TRACE.trace_enabled_sql
                                     constant INTEGER := 64;
        DBMS_TRACE.trace_all_lines   constant INTEGER := 128;
        DBMS_TRACE.trace_enabled_lines
                                     constant INTEGER := 256;

    • Constants that control the tracing process:

        DBMS_TRACE.trace_stop        constant INTEGER := 16384;
        DBMS_TRACE.trace_pause       constant INTEGER := 4096;
        DBMS_TRACE.trace_resume      constant INTEGER := 8192;
        DBMS_TRACE.trace_limit       constant INTEGER := 16;

    By combining the DBMS_TRACE constants, you can enable tracing of multiple PL/SQL language features simultaneously. Note that the
    constants that control the tracing behavior (such as DBMS_TRACE.trace_ pause) should not be used in combination with the other constants (such as DBMS_TRACE.trace_enabled_calls).

    To turn on tracing from all programs executed in your session, issue this call:

      DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_calls);

    To turn on tracing for all exceptions raised during the session, issue this call:

      DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

    You then run your code. When you are done, you stop the trace session by calling:

      DBMS_TRACE.CLEAR_PLSQL_TRACE;

    You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will usually look at the modification dates to figure out which file to examine. The location of the trace files is discussed in the later section, “Format of collected data.”

    Note that you cannot use PL/SQL tracing with the shared server (formerly known as the multithreaded server, or MTS).

    More Oracle Articles
    More By O'Reilly Media


       · This article is an excerpt from the book "Oracle PL/SQL Programming, Fourth...
     

    Buy this book now. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.

       

    ORACLE ARTICLES

    - Tuning PL/SQL Code
    - Debugging PL/SQL Code
    - Testing PL/SQL Code
    - Working With PL/SQL Code
    - Conditional Compilation for Oracle Database ...
    - Compile-Time Warnings for Oracle DB 10g
    - Compiling PL/SQL Code for an Oracle Database
    - Troubleshooting PL/SQL Code
    - Managing PL/SQL Code
    - Data Manipulation and More for HTML DB Appli...
    - Oracle Database Fundamentals
    - Adding Processes to HTML DB Applications
    - Adding Computations, Processes, and Validati...
    - Sub-templates and More with Oracle HTML DB
    - Focusing on Templates in Oracle HTML DB

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway