Chat now with support
Chat with Support

Toad for Oracle 12.12 - Getting Started Guide

Table of Contents Welcome to Toad Toad Resources Required Privileges Create and Manage Database Connections Editor Basics Schema Browser Basics Data Grid Basics Work with Data Work with Database Objects Work with Code
Write Statements and Scripts Work with the Query Builder Execute Statements and Scripts Save Statements (SQL Recall) Work with PL/SQL Objects Debug Analyze Code Optimize SQL
Customize your Toad Environment Use Other Quest Software Products with Toad

Debug PL/SQL

Toad provides an intuitive and efficient way to write, run, and test your SQL and PL/SQL code. Toad supports efficient code management for a single developer or a whole team of developers.


Minimum Oracle Database Requirements

For all databases, you must have the Oracle Probe API v2.0 or later installed in order to debug PL/SQL using Toad.

To check the Oracle Probe API version

  1. Make sure a package called DBMS_DEBUG exists in the SYS schema.

    To find out what version of the Probe API you have, run the following anonymous PL/SQL block in the SQL editor with a DBMS Output window tab selected:


    probe_major_ver varchar2(10);

    probe_minor_ver varchar2(10);


    dbms_debug.probe_version(probe_major_ver, probe_minor_ver);

    dbms_output.put_line('MAJOR='| | probe_major_ver);

    dbms_output.put_line('MINOR='| | probe_minor_ver);


    If the DBMS Output window displays:



Then the version of Oracle Probe API is 2.2.

Database Version Notes

For Oracle 10g and 11g, you must have the DEBUG CONNECT SESSION privilege, or Oracle will not let you use DBMS_DEBUG.

Additional notes

  • You cannot use the Evaluate/Modify window to change argument values.
  • Watches on package variables are only allowed for Probe v2.2 or higher.


Start Debugging

You debug PL/SQL objects in the Editor.

When you open a complete package or type in the Editor, the spec and body open in separate tabs by default. However, Toad provides options to control how objects are split, reassembled, and saved. See Editor Open/Save Options in the online help for more information.

To use the debugger fully with PL/SQL or Java packages, you need to compile your object with debug information. If you have not compiled with debug information, in databases in versions before 10g you can step into a unit, step over and so on, but you cannot see watches unless the object is compiled with debug. In a 10g or later database you cannot step into code or step over unless the object was compiled with debug. You can only execute.

In addition, if you are debugging an object that has dependent objects, you cannot step into the dependents unless they, too, are compiled with debug information.

To start debugging

  1. Open a PL/SQL object in the Editor.
  2. Click on the main toolbar or select Session | Toggle Compiling with Debug. This enables debugging.
  3. Compile the object on the database.
  4. Select one of the following options on the Execute toolbar to begin debugging:
    • Execute PL/SQL with debugger .
    • Step over ()
    • Step into ()
    • Run to cursor ()


Set Parameters

Some PL/SQL has variable parameters that need to be set before you can run the code. If values for these variables are not set, an Oracle error may display when you run the code.

Note: The values entered in the Set Parameters window are for the column values, not the argument values of triggers. Set Parameters in Triggers

To set parameters

  1. Select Debug | Set Parameters.

  2. Enter any necessary values in the Arguments grid.


    When the input type is set to Expression, anything in the Value column is used as-is in the anonymous block. This is useful for setting input value to, for example, the return value of a function or a SELECT statement.

    In the case of a SELECT statement, Toad detects that it is a SELECT statement and inserts the required INTO clause into the anonymous block. The SELECT must return a single value (single row, single column), such as the count number in the following example, because the code is initializing one variable.

    Example input expression:

    SELECT COUNT (*) FROM user_objects WHERE object_name LIKE 'EMP_TEST_%'

    How it translates to input in the anonymous block:


    INTO var_P1

    FROM user_objects

    WHERE object_name LIKE 'EMP_TEST_%' ;

    Expression and Null are mutually exclusive. If neither of those columns are selected, the value is treated as a literal.

  3. Select the Output Options tab to specify how you want output displayed. Review the following for additional information:
    PL/SQL Results Select one of the following...

    Print OUT arguments/RETURN values to DBMS Output

    Select this option to send the results to the DBMS Output window.

    Fetch Cursor Data

    Select this option to load REF CURSORS output into memory. Note: To be able to view the cursor data after execution, this option must be selected.

    Print to DBMS Output (char/number columns only)

    Select to fetch data from the REF CURSOR and print it to DBMS output one row per line. You can also select the following options:

    • Fixed column width (characters)—Select to modify the generated code to apply the Oracle RPAD() function to all values printed to DBMS output, passing the user-specified width. This will not only right-pad values with spaces, but also truncate values longer than the given width.
    • Row fetch limit per cursor—Limits the number of rows fetched. Applies to both DBMS output and table options.

    Note: Toad raises an exception if a line is more than 255 characters long. This option will only print values of char/number datatypes. For other datatypes, the name of the datatype is printed in square brackets.

  4. To rebuild the code and see the variables, click . Important! This rebuilds the code and all changes to the anonymous block will be lost.
  5. Set Profiler parameters as needed. Use a PL/SQL Profiler


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating