Toad Tips and Techniques

 

Hi,

2017 is upon us. Toad ventures into another year! Toad can do so many things but I think people sometimes forget the little things that Toad has to offer.

Let’s start with one of my favorite topics for people new to the Oracle RDBMS…such as business analysts. They know their data but they don’t know how to access it, and they sometimes don’t know SQL at all.

I call this SQL without typing.

Using the Schema browser or simply typing in a table name and pressing F4 (either with the name highlighted or the mouse on it), you get the following describe panel. This panel shows you quite a bit of information including the data. Sometimes it’s nice to see some of the data.

Clicking the ‘View/Edit Schema Browser Query’ button (shown in the red circle below),  shows the SELECT statement that created this data. Again, Toad allows the user to create SQL with very few keystrokes. You can change this SQL and change the content of the Data display, or, you can copy/paste this SQL into the SQL editor for your own uses (perhaps a report or script). 

These techniques allow you to create valid SQL statements without typing!

 

 

The data tab is another data grid. Data can be added, changed, and deleted from here as well.

Toad contains bits of code called ‘Code Snippets’. These snippets contain most of the SQL functions, date formats, hints, and other bits of SQL and PL/SQL code that are of interest to the Oracle developer, and even the data analyst! Simply drag and drop these code fragments into your SQL statement. 

Code Snippets is a dockable palette that allows easy access to various bits of code, SQL functions, date formats, hints, and the like. These are found on the menu item View à Code Snippets. Use the push-pin to auto hide the palette, use the drop down menu on the palette to select other code snippets. 

 

Toad options allow for additional items to be added or existing items to be changed.

 

 

 

 

Toad allows for the snippet text to be modified. Categories can be added, changed, deleted and additional options can be added/changed/deleted for each category.

 

Toad has an ER Diagrammer. I think data analysts should use this feature all the time when learning about their data and the important data relationships.

Use the   button for the ER Diagrammer. This tool makes a nice ERD picture of your objects and their relationships. Often it is these relationships that are difficult to follow. The ER Diagrammer can be accessed from the menu item Database -->Report -->ER Diagram.

This tool makes use of the Object Palette that is accessible from the View à Object Palette. Drag and drop the table objects from this object palette and drop them into the main window. There is also an Object Palette button on the ER Diagrammer tool bar.

The ER Diagrammer is shown below.

To work these examples, start the Object Palette and change the user to SCOTT. Drag and drop the EMP and DEPT tables from the Object Palette to the canvas area.

 

 

 

Notice the relationships. The DEPT table has the primary key (gold key) that is related to a foreign key in the EMP table. This tells the user that the DEPT table should be the lead table in the Master/Detail Browser.

The SQL Modeler/Query Builder is a little different. It is started using the SQL Modeler button   from the main tool bar or from the ER Diagrammer. The SQL Modeler can also be found on the Database --> Report --> Query Builder. It works in much the same way, using the Object Pallet, although, the ER Diagrammer can start the Query Builder and transfer all the selected objects to it. The main difference in the two modelers is the Query Builder will create a SQL statement based on the relationships.

This is the Query Builder capability. Click on the   button to paint in the relationships.

  

  

Notice as objects are put onto the canvas and the columns are selected by clicking the box next to them, that the grid fills in on the left and the SQL builds in the bottom pane! Use the W and H buttons (see circle above) to add Where clause items and Having clause items to the SQL. The query can be executed from here   , saved   , and even moved to a tab in the SQL Editor  !

A nice drag and drop feature for column names are called Toad insights. How many times are you trying to build a SQL statement but the column names are long and you are not very good at typing. I would have loved to have had this feature in yesteryear!

 

***Note*** Did you know that Oracle12.2 is allowing up to 128 byte table and column names? Yes, PL/SQL object names too. Features like this and the ability to create SQL without typing will soon become even more important.

 

Toad insights allow for object column names to be displayed following the entry of a ‘.’. Enter the table name followed by a ‘.’ or a table alias followed by a ‘.’.

Double-click on a column name to automatically paste it into the Editor at the position of the cursor. Select multiple column names using the shift and click and ctrl and click keys, then hit return and all the selected columns are put into the Editor along with commas between the fields.

There are more ways of producing SQL without typing, such as Auto Replace and Code Templates. Watch for future blog postings on these features and how they can help you personalize Toad for your programming and data research needs.

Dan Hotka

Author/Instructor/Oracle Expert