Oracle
  Home arrow Oracle arrow Page 3 - Debugging PL/SQL Code
The Best Selling PC Migration Utility.
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

    Route your faxes to your email inbox. Private, secure fax numbers available from CallWave. Choose your fax number.

    Debugging PL/SQL Code - Controlling trace
    (Page 3 of 4 )

    The trace files produced by DBMS_TRACE can get really big. You can focus the output by enabling only specific programs for trace data collection. Note that you cannot use this approach with remote procedure calls.

    To enable a specific program for tracing, you can alter the session to enable any programs that are created or replaced in the session. To take this approach, issue this command:

      ALTER SESSION SET PLSQL_DEBUG=TRUE;

    If you don’t want to alter your entire session, you can recompile a specific program unit in debug mode as follows (not applicable to anonymous blocks):

      ALTER [PROCEDURE | FUNCTION | PACKAGE BODY] program_name COMPILE DEBUG;

    After you have enabled the programs in which you’re interested, the following call will initiate tracing just for those program units:

      DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

    You can also restrict the trace information to only those exceptions raised within enabled programs with this call:

      DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

    If you request tracing for all programs or exceptions and also request tracing only for enabled programs or exceptions, the request for “all” takes precedence.

    Pausing and resuming the trace process

    The SET_PLSQL_TRACE procedure can do more than just determine which information will be traced. You can also request that the tracing process be paused and resumed. The following statement, for example, requests that no information be gathered until tracing is resumed:

      DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_pause);

    DBMS_TRACE will write a record to the trace file to show when tracing was paused and/or resumed.

    Use the DBMS_TRACE.trace_limit constant to request that only the last 8,192 trace events of a run be preserved. This approach helps ensure that you can turn tracing on without overwhelming the database with trace activity. When the trace session ends, only the last 8,192 records are saved.

    Format of collected data

    If you request tracing only for enabled program units and the current program unit is not enabled, no trace data is written. If the current program unit is enabled, call tracing writes out the program unit type, name, and stack depth.

    Exception tracing writes out the line number. Raising an exception records trace information on whether the exception is user-defined or predefined, and records the exception number in the case of predefined exceptions. If you raise a user-defined exception, you will always see an error code of 1.

    Here is an example of the output from a trace of the showemps procedure:

      *** 1999.06.14.09.59.25.394
      *** SESSION ID:(9.7) 1999.06.14.09.59.25.344
      ------------ PL/SQL TRACE INFORMATION ----  Levels set : 1

      Trace:  ANONYMOUS BLOCK: Stack depth = 1
      Trace:   PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2
      Trace:    PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3
      Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4
      Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4
      Trace:    PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3
      Trace:   PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3
      Trace:   PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
      Trace:   PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
      Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4

    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




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