Chat now with support
Chat with Support

Toad for Oracle 12.11 - 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

Understand Editable Resultsets

Work with Data > Understand Editable Resultsets

A data grid is fully editable providing that the query itself returns a resultset that can be updated. Query statements must return the ROWID to be editable. For example:

Not Editable Editable

select * from employee

select employee.*, rowid from employee

Notes:

  • You can substitute EDIT for SELECT * FROM. Toad translates it into the editable version of the statement. For example, edit employee returns the same result as select employee.*, rowid from employee.
  • If the resultset should be editable but remains read only, make sure the Use read-only queries checkbox is not selected on View | Toad Options | Data Grids | Data options page.

 

Copy Data to Another Schema

Work with Data > Copy Data to Another Schema

You can quickly copy data from one or multiple tables to the same table or tables in another schema or database. Toad builds insert statements that use array binding in the variables to copy the data, or if you prefer, create masked data. If you set the array size to 500, then 500 rows are inserted with a single insert statement. The array size is adjustable.

Note: Toad copies data from one schema to another between tables that have the same table name. The tables must exist prior to running this command.

To copy data to another schema

  1. Select and right-click one or more tables in the Schema Browser.
  2. Choose Copy data to another schema from the menu.
  3. Click the Source/Dest and Options tab to select destination connection, schema, and options.
  4. To select tables, add and test a WHERE clause, and specify data masking, use the Tables, Where Clauses, Data Masking tab

    Tip: You can check your WHERE clauses by clicking (Test Where Clauses).

 

Compare Data

Work with Data > Compare Data

You can use Toad's Compare Data wizard to compare data between tables within different schemas, or different databases. This can be useful for comparing the data in a production and test environment, for example.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To access the Compare Data wizard

From the Database menu, select Compare | Data.

Select Data Sources page

Description

Use DB Link

If your first data source is remote, select an existing DB Link.

If your first data source is local, leave this box blank.

Object Type

Tables, Views and Snapshots are supported.

Performance Options Output page

Description

Sort Area Size

This only affects queries going through a Database Link.

When selected:

  • The default area size is 10 MB
  • You can select to set another sort area size when the first window closes. The default for this is also 10MB.

Optimizer Hints - Use parallel hint

Default: Cleared

When selected, you can set the amount of parallelism you want. The default amount when selected is 4.

Select Columns page

Description

Column colors

Black - Columns display in both sources and can be compared.

Red - Columns cannot be compared.

Purple - Columns display only in Source 1.

Teal - Columns display only in Source 2.

Specify columns to uniquely identify each row... Description
Columns and Key Columns

If a table has a primary key, Toad will find it automatically and put the primary key columns in the RHS.

If no primary key is found, you can specify the field that can be used as a primary key.

With the primary key identified (automatically or manually), Toad will use the primary columns to identify if a row is “in both tables but with differences.”

If no primary key is identified, Toad has no way of identifying a row except by using all columns, so no ‘differences’ tab is shown, and rows are either matches, or in one table, or the other.

(Final Step) Description
Page title