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

Reload Object

The reload object options give you an easy way to synchronize your PL/SQL source with objects also existing on the database.

You can reload objects in one of several ways.

To reload an object from the Navigator

  1. In the Navigator, select the object you want to reload.
  2. Right-click and select Reload.

To reload all objects from the Navigator

  1. In the Navigator, select the object you want to reload.
  2. Right-click and select Reload All.

To reload from the Toolbar

  1. Place your cursor in the object in the Editor that you want to reload.
  2. Click (Reload object from database) on the toolbar.


Create New PL/SQL Object

Use the Create New PL/SQL Object dialog to create a new database object. Toad opens a dialog that enables you to specify the parameters for the CREATE statement and allows you to select a default or custom template to speed the creation of the object.

To create a new PL/SQL Object in the Editor

  1. Click on the Editor toolbar. (If this button does not display, you may need to add it. Show/Hide Toolbars
  2. Select the type of object you want to create.
  3. Enter the name of your new object in the New Object Name box, or leave this blank for now and enter a name when you save the object.
  4. Select a template.

To create a new PL/SQL Object from the Schema Browser

  1. In the schema browser object type drop-down, select the object type that you want to create (package, procedure, function, and so on).
  2. Click . The button is context-sensitive based on your object type selection and launches the appropriate dialog.
  3. Provide the information and select a template.

Default Templates

The following default templates are stored in the Toad for Oracle\User Files folder:

Template Purpose


Create a Procedure


Create a Function


Create a Package spec


Create a Package body


Create a Type spec


Create a Type body


Create a Trigger spec

In addition, there are two template types that you can use only within packages. These are Package Function and Package Procedure. See Use Templates within Packages in the online help for more information.

Custom Templates

You can use a text editor to create new templates and edit the default templates. The new or revised template will be shown in the template selection drop-down when you create a new object.

To create a custom template

  1. Select Toad Options | Proc Templates from the Toad menu.
  2. See Procedure Template Options in the online help for more information.

Keyword substitutions in templates

There are several keywords in the templates for which Toad will automatically substitute in values when you open the templates.

%YourObjectName% Object Name

%SYSDATE% Workstation date, for example, mm/dd/yyyy

%DATETIME% Workstation date and time, for example, mm/dd/yyyy hh:mm:ss am

%DATE% Workstation date, for example, mm/dd/yyyy

%TIME% Workstation time, for example, hh:mm:ss am

%USERNAME% Username specified in Toad Options, Editor node

%TRIGGEROPTS% Trigger Options for triggers only, for example, "Before insert on, for each row"


  • *YourObjectName* is also supported for backwards compatibility.
  • The keywords ARE NOT case sensitive.
  • The date and time formats come from the Windows Control Panel settings.
  • This feature is only in the Commercial version of Toad, not the freeware Toad.

In addition to these, you can specify custom keywords before you create the object.See Procedure Template Options in the online help for more information.


Extract Procedures

You can extract a procedure from existing code into a new stored procedure or locally defined procedure.

Creating the new procedure and call depend heavily on the parser to determine which identifiers in the text selection must be declared as parameters in the new procedure. If Toad cannot parse the code, no extraction occurs.

To extract procedures

  1. Select the code you want to extract in the Editor.
  2. Right-click and select Refactor | Extract Procedure.
  3. Select a procedure type.

    Note: If you select stored procedure, you can choose to either include the CREATE OR REPLACE in the DDL instead of just CREATE.

  4. Enter the procedure name.

    Tip: The new procedure and the resulting procedure call are created an inserted so that the code is syntactically correct, but no formatting is done to the code. You can have Toad format the code by pressing SHIFT+CTRL+F.


Generate DBMS Output

Oracle provides a package, called DBMS_OUTPUT, which is specifically designed with functions for debugging PL/SQL code. It uses a buffer that your PL/SQL code writes into, and then a separate process queries the buffer out and displays the contents.

The Editor has a Right-Click menu option that will generate a DBMS_OUTPUT statement for a highlighted variable. The same menu has an option to create a blank DBMS_OUTPUT statement.

To generate an output line from a variable

  1. Highlight the variable in the Editor.

    Right-click and select Output Statements | Make Output Statement.

    Note: The statement is created on the clipboard.

  2. Paste the statement in your code where you want it.

To generate a blank output statement

  1. In the Editor, right click and select Output Statements | Blank Output Statement.
  2. Paste the statement in your code.

To view DBMS Output

On the main toolbar, select View | DBMS Output.

DBMS_OUTPUT results only display after the procedure has completed execution, not while you are single stepping through the code. In nested procedure calls, all procedures must have run to completion before any DBMS Output content is displayed.


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating