Look Up Foreign Keys
When you are editing table data in the Schema Browser, you can look up foreign keys and enter the associated data into the selected table.
Foreign key lookup will also work when the grid is in Read Only mode. You can disable this feature from the Toad Options | Schema Browser | Data and Grids | Enable FK Lookup.
To lookup foreign keys
- In the Schema Browser, click the Tables tab.
- Select the table you want to edit and in the details pane click the Data tab.
- Click in a column that has foreign keys. Click again to activate Editing mode. A drill down button displays.
- Click
to display the rows in the referential table.
- To enter data in appropriate columns in the data grid, select a row and click OK.
The Foreign Key Lookup Window
Within the lookup window, all foreign key constraints are included. If you want Toad to ignore disabled constraints, see Toad Options | Schema Browser | Data.
You can filter rows by typing or by directly editing the query.
To filter rows by typing
- Make sure the Filter by box in the lower left is selected.
- Put your cursor in one of the displayed columns. You can now filter by typing (if you type "ab" it will filter out rows that do not begin with "ab").
To filter rows by editing the query
- Click Edit Query.
- Edit the query within the editing window.
- Specify variables by clicking the Variable button and entering variable information.
- Check that your query syntax is accurate by clicking the Check button.
- Click OK to run your new query and filter the lookup grid.
Compare Objects
Toad enables you to view, add, modify, and get information about database objects. You can work with these objects quickly and without having to use SQL commands.
Compare Individual Objects
You can compare individual objects to other objects. If the objects are of the same name and type, the comparison produces a synchronization script, which you can edit if needed, and then run to bring the source and target back into synchronization.
You can initiate the comparison from the Schema Browser or from the Database menu.
To compare objects from the Database menu
From the Database menu, select Compare | Single Objects.
To compare individual objects from the Schema Browser
In the Schema Browser, right-click on an object or a pair of objects, then select Compare.
Note: You can also select multiple objects from the Schema Browser for comparison. This is not a single-object comparison, and not covered in this topic. If you do select multiple objects, you are redirected as follows:
- If you select more than two objects not owned by a schema, such as users, tablespaces, or profiles, the comparison is directed to the Compare Databases window, which is filtered to only those objects on the Object Set tab. See Compare Databases for more information. Compare Databases
- If you select more than two schema-related objects, such as tables, views, or packages, the comparison is directed to the Compare Schemas window, which is filtered only to those objects on the Object Set tab. Compare Schemas
To configure and run the comparison
-
On the Objects tab:
- Select the source and target database connection.
-
Under Target, select one of the following:
- Live Object to compare the source object to the actual target object in the database.
- Text File to compare the source object to a script file of the target object.
-
Do one of the following, which will determine the objects that you can choose under Object Type:
- To compare schema objects, select Schema, then select a schema for the source and target.
- To compare non-schema database objects, clear the Schema check box.
- Select from Object Type.
- Select from Object Name.
-
On the Options tab:
- For Schema Objects: select any additional schema objects that are associated with the selected objects that you want to include in the comparison.
- For Tables:
- Select whether or not to reorder the target table with DBMS_REDEFINITION. This package from Oracle enables the redefinition when the table is accepting DML changes.
- Include Triggers—Select to include the triggers on the table.
-
Click Compare. The results are shown on three tabs:
- Compare Scripts tab: Shows the source and target DDL that was used to compare the objects in the differences viewer.
-
Compare Columns tab: Shows the columns that were compared. You can add the following to this view:
Option Description Alphabetical
Precision/Scale
Shows and compares the precision and scale of the columns.
Hidden Columns
Shows and compares hidden columns.
- Sync Script tab: Shows the sync script that Toad generated from the comparison. This tab is only available if the objects chosen have the same name, and are in different schemas. Using the toolbar options, you can copy the script to the clipboard, open it in the Editor to make changes, or save it to a file.
Compare Multiple Tables
The Toad Compare Multiple Tables wizard makes it simple to compare more than two tables. You simply point Toad the schemas, then synchronize the data in the tables of those two schemas.
Note: This feature will synchronize the data in the compared tables, but there is not an option to compare the data differences first. To compare the data, go to Database | Compare | Data.
To compare multiple tables
-
Do one of the following:
- From the main Toad menu, select Database | Compare | Compare Multiple Tables.
- On the DB Misc tab of the Automation Designer, double click
(Compare Multiple Tables) to add the action to your app, then double-click the action to open the Properties dialog.
-
Make selections according to the following descriptions. The descriptions are listed in the order in which they appear in the wizard. Click Next to move from one page to another.
Choose Schemas page
Schemas tab
Select the source and target database connections and schemas that contain the tables that you want to compare.
Specify any database link options as desired.
There are two different techniques of synchronization that this feature can perform. Which one Toad uses depends on what you do on this tab.
- If the tables to be compared are in the same database, use the same connection to access the source and target schemas. In this case, Toad can synchronize the tables with just two SQL statements per table: a DELETE and a MERGE. All of the work is performed by the database, so the data does not get fetched to your client PC, and you will not run out of memory if you are synchronizing large tables. This method is the faster method.
-
If the source and target schemas are in different databases, select Allow Toad to create a temporary DB Link from Target to Source. In this case, Toad creates a private database link on the target database that points back to the source. This allows Toad to join the tables and perform the DELETE and MERGE operations as if the tables were in the same database. The database link has full TNS information, so there is no need for an entry to exist in the TNSNames.ora file on the target server. Toad drops the link when the synchronization is finished.
Note: Toad refers to the previous two methods as Comparison by SQL.
- If you cannot allow a database link to be created, and the schemas are in different databases, then Toad must fetch all of the rows from each table to your PC and then compare them row by row. This is the slowest and most memory-intense option. If possible, use the database link when the schemas are in different databases. Note: Toad refers to this method as Row-by-row comparison.
Options tab
This tab enables you to determine what Toad does when it finds anomalies during the initial comparison.
| Option | Description |
|---|---|
| If columns don't match exactly |
Select what you want to do if the source and target tables do not have the same number of columns.
|
| If a table contains unsupported datatypes | Select what you want to do if a table contains an unsupported datatype. See Potential comparison problems for examples. |
| If any table is to be skipped | Select what you want to do when incompatibilities are found during the comparison. |
| If an error occurs during synchronization |
Select what you want to do if there are synchronization errors. Rollback current table and continue directs Toad to issue a commit after each table (except if the synchronization of that table is rolled back due to an error). Rollback all tables and stop directs Toad to either commit at the end of the process or roll it back when the first error happens. Note: It is not possible to provide the option to commit or do not commit, because DDL occurs at the beginning and end of each compare and synchronization (disable/enable of constraints and possibly triggers, create/drop database link), and therefore commits are implicit in most cases. |
| Triggers | By default, triggers on the compared tables are disabled so that they do not cause DML to be performed on child tables that may also be compared and synchronized by Toad. Double operations like this will cause errors. Clear this selection if you need the triggers to fire. |
Choose Tables page
To compare all of the tables with matching names in your selected schemas, do nothing on this tab. Otherwise, use this page to specify how to match table names, handle table names that do not match or compare a subset of the tables in the selected schemas.
| Option | Description |
|---|---|
|
Match tables automatically and Table names match exactly |
Both of these options are enabled by default and indicate that you do not want to refine the table selection further. Otherwise:
|
| Table name filter | To load only specific tables based on their names, enter standard filter syntax here. |
|
To match target tables to source tables |
Select Add before table name or Add after table name if the tables in one schema have a different naming convention than tables in the other schema. Examples:
Source has TABLE1, TABLE2, TABLE3 Target has TBL_TABLE1, TBL_TABLE2, TBL_TABLE3 You want to compare TABLE1 with TBL_TABLE1, etc. Put TBL_ in Add before table name.
Source has TABLE1, TABLE2, TABLE3 Target has TABLE1_TBL, TABLE2_TBL, TABLE3_TBL You want to compare TABLE1 with TABLE1_TBL, etc. Put _TBL in Add before table name.
Source has TABLE1, TABLE2, TABLE3 Target has TBL1, TBL2, TBL3 You want to compare TABLE1 with TBL1, etc. Put TABLE in Search for and TBL in Replace with.
Source has TBL_TABLE1, TBL_TABLE2, TBL_TABLE3 Target has TABLE1, TABLE2, TABLE3 You want to compare TBL_TABLE1 with TBL_TABLE1, etc. Put TBL_ and leave Replace with blank. |
| Reload | Click this option to reload the list of tables after applying filters. |
| Include |
Select this checkbox for every table that you want to include in the synchronization. To map non-identical table names, use the dropdown selector in the Target column to map a target to the source table listed in that row under Source. |
| Validate | To check the compatibility of the source and target tables for comparison, click this option. See Potential comparison problems for examples. |
Choose Output page
| Option | Description |
|---|---|
| Sync target tables immediately |
Select this option to start the synchronization the moment you click When this option is selected, Array DML is active and selected by default. Array DML means that Toad will apply deletes in batches of up to 1000 rows at a time, and inserts/updates in batches of up to 100 rows at a time. Array DML greatly increases the speed of the synchronization, so you should leave it selected. In this mode, update statements update every column in the rows being synchronized, even if only one column value actually changed. If you do not agree with this methodology, uncheck Array DML. Note: If a large percentage of the rows in a large table need to be synchronized, running the synchronization without Array DML may be very slow compared to using Array DML. |
|
Create one script... |
Select Create one script for each table or Create one script for all tables and then specify the output folder or file name. |
Compare and Sync page
Click 
The grid shows a row for each table to be compared. Toad makes two passes through the list, first to evaluate the tables for compatibility and to disable foreign keys, and the second to perform the comparison and synchronization. The comparison type is shown:
- For Comparison by SQL (performed by the database), Toad displays the number of rows that are deleted and merged (inserts and updates).
- For a Row-by-row comparison (performed by Toad on your PC), this window displays details about the rows that are inserted, deleted, and updated, as well as the number of matching rows (the Matches column). The Time Remaining column shows how much time remains on each step (one step is deletes, the other is insert and update).
Potential comparison problems
The following chart shows potential validation problems that can arise from incompatible source and target tables. In this table, Supported in Row By Row Comparisons refers to comparisons performed by Toad on your PC, and Supported in Comparison by SQL refers to comparisons performed by the database.
