Toad for Oracle 15.0 - Getting Started Guide

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.

   Notes: 

   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:

   SELECT COUNT (*)

   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

Set Parameters in Triggers

Note: The INSERT INTO… code is not valid until you enter column values.

Handle Dependencies

You can use the Debugger to check for dependencies, and compile them.

Manually compiling dependent & referenced objects

To compile dependencies manually

Click or its drop-down on the debug toolbar and select the appropriate option.

Automatically Compiling Dependent Objects

Toad can automatically compile any procedures that call the current procedure.

To set options to handle dependent objects

1. From the View menu, select Options.
2. In the left pane, select Debugger | General.
3. In the Compile Dependencies area, select one of the following:
   • Yes - Toad will always compile dependencies.
   • No - Toad will never compile dependencies.
   • Prompt - Toad will check for dependencies and ask if you want to compile them.

See Debugger Options in the online help for more information.

Viewing Dependencies and their Status

You can visually view dependencies and their status.

To view dependencies and references

1. From the Schema Browser, objects panel, click the Procedures, Functions, or Packages tab.
2. Select the procedure you are debugging, then in the details pane, click the Deps(uses) and Deps (used by) tabs. Status of the procedure is listed in the Status column in the details pane as valid or invalid.

Call Stack Window

The Call Stack window displays the chain of functions and procedures as they are called, in the order they are called, with the most recent function or procedure listed on the top.

At the end of each procedure name is the current line number in that procedure. So, if you step into procedure B from line 5 of procedure A, then the call stack will look like this:

Procedure B(1)

Procedure A(5)

When using the JDWP Debugger, the Call Stack window displays call stack frames in the left pane and variables in the right pane. The Variables pane allows you to set values: right-click the variable and select Set value.

To access the call stack

From the Desktops area, click the Call Stack tab.

To add the call stack tab to the Desktop

Right-click in the Desktops area and select Desktop Panels | Call Stack.