Devin Gallagher's Blog

Toad for SQL Server, DB2, MySQL and Toad for Data Analysts Configuration and Options

Devin Gallagher — Wed, 09 Sep 2009 13:54:00 GMT

Like most applications, Preferences or Options enable the user to make changes to the default configuration to suit the way they work. The same is true for Toad but there are so many, for some it can be a bit of a challenge finding what they are looking for.

The objective of this blog is to help you get a handle on what the main options are that you probably need to know about. Note that the screen captures were done using Toad for SQL Server, but these techniques apply to Toad for DB2, Toad for MySQL and Toad for Data Analysts, too.

Some Toad Options are set during the Toad installation and others via the Configuration Wizard. The Configuration Wizard can be reconfigured at any time and is found under the main menu, Tools | Configuration Wizard.

The first screen will let you pick one of several preconfigured layouts. For instance, if you have used Toad for Oracle in the past and prefer the layout (workflow) of that tool, you can simply select it here and you are done. This will make Toad mimic the layout of that tool (keyboard mappings included). However, if you prefer to customize the layout, choose “Custom” and click next.

On the following screen you will be presented with several choices to configure the look and feel of the windows and grids. Here you can change colors, contrast and more. The Configuration Wizard preview window will update with your changes to give you a glimpse of what each style really looks like.

The next screen in the wizard is where you can choose a default style for your Database Explorer and Object Explorer. The default is to divide objects by their type and display them on separate tabs; if you prefer a tree-list or stacked tabs, this is where you can change that.

The final configuration page controls which window automatically opens when connections are made: Database Explorer, Editor, or Query Builder.

The next portion of this document will focus on Toad Options, found under the main menu selection, Tools | Options, and the following folders: Environment, Explorer, Editor, and Database.

Environment-General - This page of options influences how Toad opens. Users can select the default web browser home page. Croak options allow users to mute or enable the sound Toad makes when starting, or after completing certain tasks. There is also an option to Show the connection dialog on startup. If a user has marked the ‘Connect on Startup’ selection in the Connection Manager, then having this option unchecked will skip the connection window during startup.

Environment-Grid - This page of options influences the data grid display for data grids that exist throughout Toad. Many of these options are selected by default such as allowing filtering, sorting, and grouping. Line numbers is not checked by default but a helpful option to have selected. There is also a Read-Only option that would prevent data changes even if you had permissions to update data. At the top right of this page, there are different choices for the format display for DateTime, Number, Integer, and Money.

Explorer-Objects - This page allows users to customize the object types displayed in the Object Explorer. The check box in the Visible column will hide or unhide that object type from the Navigation Tree. If a user does not work with certain object types, they can hide them in the Object Explorer. The display order of the objects can also be customized, with the up and down arrows on the right side.

Editor- General - This folder has a few pages worth exploring, since most users will spend a lot of time in the Editor. The General Page has settings for Font, Show Line Numbers, Color Coding for modifications, and Word Wrap settings.

Editor-Tools- A popular Toad Editor feature is the SQL Command Recall (Editor Main Menu/ Add to SQL Recall). The number of SQL saved by Toad is controlled by this Options setting. By default it is at 100, which can be increased or decreased with the SQL Recall setting. (A helpful tip within the Editor: Ctrl + up or down arrow, will scroll through the SQL Recall list). There is also a drop down window for the display preference for SQL execution plans.

Database-General- This page has setting for the number of rows initially brought back in the data tab. Notice there is also a check box to ‘Apply to Editor’. A user can always retrieve more data, or the whole data set, after the initial data fetch. There is a setting for reopening lost connections. In the Configuration Wizard, there was a choice for ‘Open on New Connection’, and this is also controlled in this window.

Database-Script Result- This page has an option that controls how multiple data sets are displayed in the bottom half of the editor, with choices for ‘All in one tab’, or individual tabs for each result set.

Exploring these settings should make your experience using Toad more beneficial. For more detailed information on every Toad Option, please see Help Menu | Contents-Configure Options.

Toad for Oracle SQL Editor

Devin Gallagher — Thu, 06 Aug 2009 12:35:00 GMT

This blog explains specific areas of the editor in Toad^® for Oracle 9.x that provide “built-in” SQL code, re-usable code, areas to customize your own code, and immediate access to SQL transactions you have saved or previously executed. Nearly all of these code areas can be accessed from within the Editor, while other windows allow drag and drop functionality into the Editor.

If you use the Toad Editor every day, these tips should save you some time.

Column Selection

Figure 1: Example of table name. displaying available columns

View Menu

The View menu has many “dock-able” windows that you can pin or un-pin and quickly access from the left or right hand side of your desktop. There are also “drag and drop” capabilities from these windows, into the Editor.

Figure 2: View menu options

The Object Palette, Code Snippets, and the Project Manager allow quick access to objects or code that you might currently be accessing from the Schema Browser. The Object Palette shows Tables, View, Synonyms, as well as column names. Quickly view table structure without leaving the Editor. Code Snippets has many sub-groupings of types of Oracle code (functions, hints, exceptions, etc). This provides extensive and customizable “out of the box” SQL code, as well as descriptions describing that code or command. Object names, column names, and code snippets can be dragged from these windows into the Editor.

The Project Manager also has drag and drop capability into the Editor, as well as many other benefits such as links to files, network drives, websites, and of course database objects. There is detailed information on Toad Projects under Help/Contents/Managing Projects/ Project Manager.

Figure 3: Code Snippets window

SQL Recall

The SQL Command Recall window is found under the View Menu. This has always been a favorite Toad feature (previously accessed usinng the F8 key), but there have been some new enhancements.

The SQL Command Recall window has 3 tabs: History, Personal, and Named. Users can quickly populate the Editor with previously executed SQL, or saved SQL, by clicking or dragging and dropping statements into the Editor. No more searching for that helpful statement you had executed last week, but may have forgotten.

Every statement executed in the Editor is added to the History list. The number of statements saved is an option found in View+Toad Options+Editor+Code Assistant.

Figure 4: SQL Recall History tab

TIP: While in the Editor, ALT+ up arrow and ALT+ down arrow will scroll through your previously executed SQL from the SQL Command Recall list.

The Named SQL and Personal SQL, allow users to save SQL transactions to a separate tab within the SQL Recall window. This is similar to the History tab, but the statements need to be added manually, and there is no limit.

Figure 5: SQL Recall - Named SQL example

To populate the Named SQL or Personal SQL lists from the Editor, use the Editor Menu from the main toolbar.

Figure 6: Editor Menu - Add to Named SQL example

TIP: While in the Editor, + N will list your Named SQL….this is a benefit of Named SQL rather than Personal SQL.

Figure 7: Example of using +N in SQL Editor to display list of Named SQL

TIP: The SQL Command Recall information, is saved in Toad for Oracle\User Files\SAVEDSQL.dat. This will contain History, Personal, and Named SQL. Users can copy this file to a new machine, or new installation of Toad, to migrate a history of transactions.

Customizing Code Assistants

Let’s look at the Toad Options for customizing Toad’s Editor. View+Toad Options, brings up the Options window in Toad.

Code Snippets

Let’s go to the Editor page, and start with Code Snippets (under Code Assist in the Navigation tree).

Figure 8: Toad Options - Code Snippets within Code Assist page

TIP: On the top right portion of the window, you can see where you can ADD or EDIT the Code Snippets.

Auto Replace

Auto Replace lets you specify what keystrokes should be replaced by what text. For example, you can choose to replace all instances of "teh" with "the" automatically. Toad comes populated with a long list of common typographical errors, but you can add your own common typing mistakes, too.

You can customize the keystroke replacements from the Options page. Select Editor+Behavior in the navigation tree and then click on the Auto Replace button.

Figure 9: Toad Options - Editor Behavior window

Figure 10: Auto Replace grid lists keystrokes and their replacement text

NOTE: It is the list of characters under Activation Expression that will replace the code for you.

Code Templates

Code Completion Templates use a manual keystroke () to perform substitutions in the Toad Editor. Code Templates are more than a single phrase and can contain line feeds, substitution variables and a cursor position indicator. These templates extend much further than Auto Replace or Code Snippets.

Click on the Code Templates button to view/edit the templates.

Figure 11: Toad Options - Editor Behavior page Code Templates button

Toad comes with an extensive list of completion templates:

Figure 12: List of provided Code Templates

TIP: Typing from within the Editor displays the list of Code Completion Templates.

Figure 13: Example of using to view Code Templates list

1 Company, 3 Different Toad Demonstrations

Devin Gallagher — Thu, 14 May 2009 14:16:00 GMT

I recently performed 3 separate Toad for Oracle web demonstrations for a large company in the payroll and human resources industry. Rather than a general overview of all Toad features, these 3 demos were customized for 3 different types of Toad users: DBA's, Analysts, and Developers.

This group of DBA's had used Toad for years, but had not upgraded recently. As I have mentioned in previous posts, there has been a substantial amount of functionality for DBA's added to Toad in recent releases - just download the latest version. Johannes' Toadworld blog has an excellent 3 part series on the Toad DBA Suite.

My demonstrations for DBA's begin with the Database Browser, which now contains the Schema Browser, and access to many of the DB Admin components. I usually right click on the instance in the Database Browser, right click on an object in the Schema Objects and then have some questions and answers with the DBA's on the phone. I ask what they use now and walk through a Toad DBA work flow for performing DBA tasks such as monitoring performance, managing users and objects, and database maintenance. After about an hour, this group was ready to download the latest version with the DBA components.

Database/Monitor/Database Browser, Oracle Instance right-click menu

Database Browser/Schema Objects or Schema Browser , database object right-click menu

Analysts are another group of Toad users that have benefited recently from additional functionality for easy query generation and data reporting, data manipulation (import, export, select, update, delete), and multiple data source connectivity (Oracle, SQL, Sybase, DB2, MySQL, ODBC, EXCEL, ACCESS, etc.). Some of the attendees for this demonstration were new to Toad, and a bit less technical than other Toad users, so we started slowly and covered how to connect, some of the user layout options and, of course, organizing Toad Projects to group specific objects together for quick navigation.

Toad for Oracle, Database/Report Menu, is where analysts find many popular features for query building and reporting.

Toad for Data Analysts is now available for any Toad Oracle 9.7 user and this provides analyst features across any database platform. There is also unique functionality in Toad for Data Analysts. The “heterogeneous query”, provides SQL generation capabilities and allow users to drag and drop a database table from one database, then drag another table from another database or even an excel spreadsheet, join the data sources, and build and execute SQL to retrieve one dataset. Another helpful area is the ability to compare and synchronize data across multiple tables.

The 3rd demonstration for this company was for both application and PL/SQL developers. They were very interested in visiting Toadworld.com, particularly Steven Feuerstein’s Pl/SQL Obsession pages. The PL/SQL CodeXpert was also something this group had not used, and the integration with Toad Projects. Debugging is now available in all versions of Toad and there is now SQL script debugging as well as DBMS debugging.

The Application developers were very interested in the AppDesigner and automating some of the tasks they perform in Toad on a regular basis. There are a number of areas on Toadworld with more information on the AppDesigner and most of that functionality is new to version 9.7.

These types of diverse Toad demonstrations are very common with Toad customers across different industries. Please let us know if you would be interested in a similar demonstration for your team or organization. The link below will allow you to download the latest version of Toad and provide a Quest representative with a way to contact you to arrange for a Toad presentation.

http://www.quest.com/toad-for-oracle/software-downloads.aspx

Toad and Team Collaboration

Devin Gallagher — Wed, 25 Mar 2009 16:33:00 GMT

I had a very productive visit with an information management company, demonstrating the complete range of Toad solutions, including the Toad Development and DBA Suite for Oracle, Toad for SQL Server, as well as Toad for Data Analysts. The presentation took place in Boston, and dialed in through a webcast were attendees in Massachusetts, Pennsylvania, Belfast, and Bangalore. This visit was scheduled well in advance, and we were able to spend over 2 hours together. We discussed where Toad currently is being used within their organization and then displayed how the latest versions of Toad have evolved and improved and been integrated…….and the wheels start turning.

If you have not upgraded Toad recently, especially for DBAs using the DB Admin module, and now the Toad DBA Suite, you will find a tremendous amount of new functionality. Johannes’ blog about Toad and OEM goes through this in detail. With this group of DBAs, we introduced the Database Browser with the embedded Schema Browser -- really the best starting point for a DBA using Toad 9.7.

The Project Manager and Script Manager were very appealing features that were unfamiliar to these users, but with the diverse locations of this team, they immediately saw value. They realized this would be something to discuss as a team, and perhaps create standard directories, for collaboration. We began to talk further about the Script Manager.

I just did a Toad World search on the Script Manager, and last summer, Bert posted a couple of great blogs on it:

http://www.toadworld.com/Community/QuestExpertsBlogs/tabid/67/EntryID/257/Default.aspx

http://www.toadworld.com/Community/Blogs/tabid/67/EntryID/259/Default.aspx

Collaboration was a goal for this team. Some of us need to run the same scripts, can Toad make that easier? As you see from Bert’s blog, the Script Manager allows you to create groupings of scripts, execute against multiple environments at once, and create shortcuts to execute from the toolbar. Another benefit is that the Script Manager files are saved as .SDF files recognized by Toad. If you work as a team, and have access to the same directories as your team members, take advantage of a Script Manager .SDF file when the same script is needed to be executed by different users and different locations. Of course you may have your own scripts, and those could be maintained in a separate .SDF file that you do not share.

One user could create a Script Manager .SDF file, with paths to a shared directory, and the scripts that need to be executed. As long as the Toad users have access to those directories, they could load that .SDF file, and execute the same scripts. To load that .sdf file, a Toad user just needs to open that .sdf file from the Script Manager.

This group immediately saw value in setting up .SDF files. One of them even mentioned how useful this will be for one of the SQL Server DBA’s, now helping out with some of the Oracle database tasks, and how this functionality would reduce the learning curve significantly. Project Manager was also a conversation point, with the flexibility and benefit of Toad projects instantly recognizable. More on Project Manager soon.

New Toad blog

Devin Gallagher — Tue, 24 Mar 2009 15:03:00 GMT

I am an SC for Quest Software, and very fortunate to be able to demonstrate the solutions that make up the Toad product family. My initial goals for this blog will be to share some of the interesting conversations and discussions that arise from Toad demonstrations. Rather than screen shots of a certain feature, I will try and relate Toad functionality to real life scenarios and examples.

To start and end my demonstrations, I now mention ”3 ways to get better using Toad”:

One of them you have already heard of………visit Toadworld.com! It’s amazing how much this site has grown, and how much valuable content is here. I was considering some topics and features to discuss, but tried a Toadworld.com search before hand, and usually find some great information already posted.
Toad's Help /Contents and the F1 key. The Help system in Toad is always a great starting point for understanding a Toad window or feature. Hit F1, and Toad's Help opens for the window you are using.
Set up Toad Projects. This is a topic that I think will be continuously updated. This is one of the most powerful features in Toad in terms of organizing a Toad user’s database responsibilities and tasks, grouping of database objects and code, paths to mapped drives, unix platforms, team directories and weblinks, and management of Toad specific files.

Talk with you soon.