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

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:

select * from employee

Editable:

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

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

Use Toad's Compare Data wizard to compare data between tables within different schemas, or different databases, and then generate a synchronization script that you can run as-is, save as a file, or send to the Editor to be refined.

Compare Data 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.

Choose Tables page

Tables tab

  1. Select the source and target database connections and schemas that contain the tables that you want to compare.
  2. Specify database link options as needed:

    • If your first data source is remote, select an existing DB Link.
    • If your first data source is local, leave this box blank.
  3. Select the object type. Tables, Views and Snapshots are supported. Click the > arrow to copy the name to the Target side if the source and target object types are the same.
  4. Select the object owner (schema). Click the > arrow to copy the name to the Target side if the names of the source and target schemas are the same.
  5. Select the object name. Click the > arrow to copy the name to the Target side if the names of the source and target objects are the same.
  6. (Optional) Add a WHERE clause to further refine the object selection. Click the > arrow to copy the clause to the Target side.

Options tab

Option

Description

Comparison Method

Select the comparison method. Toad displays information about each method.

Sort Area Size

(Optional) Set a sort size other than the default, and specify the sort size that Toad goes back to when the window closes:

This only affects comparisons that do not go through a Database Link.

Optimizer Hints

Specify either full table scan or the use of a parallel hint.

When parallel hint is selected, you can specify the degree of parallelism.

Select Columns page

Select the columns that you want to include in the comparison. Toad notifies you if the column cannot be compared because it does not exist in both tables or if the data types cannot be compared.

Specify Key page

If a table has a primary key, Toad will find it automatically and put the primary key columns under Key Columns on the right-hand side.

If no primary key is found, you can specify the column(s) that can be used as a primary key.

With the primary key identified (automatically or manually), Toad will use those columns to identify whether 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 of the columns, and rows are either matches, or in one table, or the other.

Compare and Sync page

  1. Select what you want to do with rows that match: Count them, display them, or ignore them.
  2. Click Compare to start the comparison. In the results, the number in parenthesis on each result type (on the left side) shows how many rows are in each grid. A + means Toad hasn't fetched all the rows yet.

Results output

Option

Description

Row Count(Match | (Differ)

Displays the total number of rows that were compared.

Match means the number of rows in each table is the same. Differ means there is a difference in the row count between source and target tables.

Source Only and Target Only

These tabs have the same format and show the number of rows that exist only on the source and only on the target.

Differences

Displays the values of each compared source and target row and highlights differences.

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.

Matches

This result is displayed if you selected Display as the way to handle rows that match. The row values are shown, which reflect their state in both source and target.

Synchronization

Here you can:

Click to run the script to synchronize the source data with the target data. No changes are made to the source data. Missing rows are added to the target, extra rows are deleted from the target, and mismatched rows are updated on the target.

Click to send the script to the Editor for further refinement.

Click to save the script as a file.

Choose which statement types to execute with the script. By default, Toad enables whichever DML types are needed to add missing rows, delete extra rows, or update mismatched row values. You can opt not to do some of these things if needed.

Note: If you choose to update with Array DML, every column must be updated, even if the values match. It may also fire triggers that might not otherwise fire. Deselect the Array DML option to update each row only with the data that has changed.

 

 

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