Chat now with support
Chat with Support

Toad for Oracle 13.1.1 - Getting Started Guide

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

Load Database Object

Work with Code > Work with PL/SQL Objects > Load Database Object

Use the Load Database Object dialog to load an existing object into the Stored Edit/Compile window for further editing.

To load an object from the database

  1. Click in the Editor window.
  2. From the Schemas/Owners drop-down:
    • Enter the first letter of the schema to perform an incremental search of the list.
    • Select the Schema/Owner of the object you want to load
    • Select the type of object you want to load from the drop-down list.
      • For databases with few objects, just select All.
      • For databases with many objects, select a type of object and then filter to show a manageable list in the left hand panel below.
  3. From the left hand panel, you can do one of the following:
    • Click an object to select it.
    • Enter the first few letters of the object you want to perform an incremental search.
    • Note: You can turn the preview off by using the Show Source checkbox.

 

Reload Object

Work with Code > Work with PL/SQL Objects > 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

Work with Code > Work with PL/SQL Objects > 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

NEWPROC.SQL

Create a Procedure

NEWFUNC.SQL

Create a Function

NEWPackage.SQL

Create a Package spec

NEWPackageBody.SQL

Create a Package body

NEWType.SQL

Create a Type spec

NEWTypeBody.SQL

Create a Type body

NEWTrigger.SQL

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"

Note:

  • *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

Work with Code > Work with PL/SQL Objects > 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.
  4. Note: If you select stored procedure, you can choose to either include the CREATE OR REPLACE in the DDL instead of just CREATE.

  5. 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.

 

Related Documents