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

Your page will be labeled with one of these titles:

  • Server Side Data Comparison Using Primary Key

  • Client Side Data Comparison Using Primary Key

  • Server Side Data Comparison Using All Fields (No Matching Primary or Unique Index is Available)

    Caution: There is a risk that client-side comparisons for very large tables could exceed memory capacity.

Source Only and Target Only tabs

These tabs have the same format.

You can choose to migrate selected rows only:

  • For the Source Only Rows tab, it means you insert them into target
  • For the Target Only Rows tab, it means you delete them from the target
Differing Rows tab

You can choose to:

  • Update from source to target

This tab displays:

  • Rows from the source dataset in gray
  • Rows from the Target dataset in white
  • Column values that differ between source and target are highlighted in yellow.

When you click Perform Comparison, the row count is performed first. The number in parenthesis on each tab indicates how many rows are in each grid. The + means Toad hasn't fetched all the rows yet.

Scroll down in the grid to update that number.

The Row Counts tab will either say “Row Counts Differ” or “Row Counts Match.”

DML:

If you choose to update via Array DML, every column has to be updated, even if the value didn’t change. It may also fire triggers that might not otherwise fire. The non-array DML option will update each row with only the data that has changed.

 

Review Differences

Work with Data > Review Differences

From the last three windows of the Compare Data wizard you are now ready to view the differences between your data sources.

  • The first window reviews rows in Source 1 that are not in Source 2.
  • The second window reviews rows in Source 2 that are not in Source 1.
  • The last window reviews all differences.

You must run the SQL code for each window as described below.

To make dataset editable

On the Review Differences page, select the Editable Dataset checkbox.

Note: In some editions of Toad, you can delete rows from one table, and insert them into the other directly in the grid.

To review rows

  1. Perform any desired optional steps:
    • Click the View/Edit SQL button to view or edit the SQL used to compare differences. You can make changes in the Edit SQL dialog box.
    • Click Check to verify that the query parses correctly.
    • Click OK to apply changes to your query.
    • Click Execute to find differences in the columns you want to compare.

To delete selected rows

  1. Select the rows you want to delete.
  2. Right-click and select Delete Selected Rows.

To delete all rows

Right-click and select Delete All Rows.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating