ThatJeffSmith

Toad 11 Sneak Peek Part Deux

I had some time to beta test this morning, and I thought I would share some of the new tweeks and features that will be available in version 11.0 of Toad for Oracle. I spent some time previously showing off a new feature for you PL/SQL geeks out there, Code Analysis.

If you are a current user of Toad, and are current on your maintenance contract, then you are welcome to play with a beta copy of Toad too. Go here to get started.

In no particular order, here’s a few nuggets:

Drag Columns Off of Grids

In the current version of Toad, there is a control at the upper-left hand corner of each grid. Clicking it allows you to toggle off columns that are shown in the grid. A very powerful feature, this allows you to to exports of just the data you want to send to Excel for example. However, this can be a bit annoying, having to come back to the control over and over again, when you just want to drag off the column while you are looking at it.

Now you can!

Drag a Column Header to Remove a Column from a Data Set

Just take your mouse cursor, click and hold on the column, and drag down into the dataset.

No worries if you still like the ‘old’ way of doing this, it’s still there. They’ve made the widget a bit more prominent so hopefully more users will see it.

This Way Works in v10 and v11

Better Substitution Variable Support

What are substitution variables? Go read this, then come back.

Sometimes you want to be prompted for subs, sometimes you don’t. Here’s an example of when you do:

select &col1, &col2
from beer
order by &col1 asc;

Here’s an example of when you don’t.

select &col1, &col2
from beer
--this sets up our query for stuff&such
order by &col1 asc; 

If I run this in SQL*Plus -

SQL*Plus will prompt for a value for '&such'

If you don’t want Toad to prompt for subs (& or &&) in comments, just set the option in the main options dialog or in the editor popup section:

If Toad had a middle name, it would be 'Options'

Auto-Calculated Counts, Sums, and Averages in Data Grids

Too lazy to write a more selective query, then add your sum() function? No fear, Toad is here! Just select your values in the grid, and Toad will compute those values for you.

I'm still trying to figure out how to copy these computed values out to the clipboard...

You can tell Toad what you want calculated and you can also compute these values across multiple contiguous columns.

Greedy people asked for more and more, so we gave it to them.

Execute Embedded SQL in Your PL/SQL

Need to test or tune a query inside your PL/SQL program? Too lazy to copy it out, code in your variables and constant values? Toad can do that for you now! Just place your cursor in the statement and hit Ctrl+Enter. Toad will extract the code, let you preview it, then execute it. Time saver!

Auto Extract and Execute Embedded SQL statements in your PL/SQL Code

Auto Extract and Execute Embedded SQL Statements with Toad – detailed post

Ctrl+Click In the Editor Enhanced

This has been enhanced to provide more detailed resolution for declared identifiers. CTRL+Click on package.procedure will still take you to that procedure declaration, but within that procedure if you CTRL+Click on ‘X’ then you will jump to the declaration of ‘X’ whether it’s a spec constant, a parameter, local variable, etc. The option to jump to body which has always been there will still jump to the body or spec accordingly, but only in cases where an identifier exists in both the spec and the body. If an identifier is resolved to only the spec or only the body then that option is ignored.

Put your mouse on a variable, Ctrl+Click will take you to the declaration of that variable in your program

Cached Plan Support for Queries Ran in Editor

Now when you ask for an Explain in the editor, Toad will load the cached plan if possible. There is a new mouse right click option: “Load cached plan if possible”. When checked, Toad will use dbms_utility.get_sql_hash to determine the hash value for your query, then attempt to locate a cached plan for that sql statement. If found, that plan will be displayed. If not found, an ‘explain plan’ statement will be issued and the result of that will be displayed.

See the Actual vs the Theoretical Plan for your Executed Queries

Detail Sets in Schema Browser Table Data Grids

I’ve saved the best for last! This feature is pretty simple, but could be a huge productivity enhancer. I’m very excited to start showing it to people once v11 is released. It’s these little things that really keeps Toad above the free the $10 database tools out there.

Ok, so I’m looking at some data in a table, and I know it’s related to data in another table. If I’m looking at a child record, I might want to get a sneak peek at it’s parent row for additional information. Now in version 11 I can define those relationships and have the data displayed below. As you click on a row in the table, Toad auto-retrieves the related row(s) from the other table. This feature does NOT require any foreign keys. If foreign keys are there, it’s much more automatic process. Go play with HR.EMP to see for yourself.

As an example I’m asking Toad to show me records from a related table that I have from the TPC-E OLTP benchmark implementation. TPC-E has no foreign keys, so I had to define this manually.

Step 1: Activate the feature. Click on the ‘Show Detail Dataset’ button in the datagrid toolbar.
Step 2: Below, click on the View/Edit Query button to define the query used to retrieve the related data.

Here is how it looks after you have everything setup:

See related rows in other tables directly in the Schema Browser Data Grids

You can even create multiple ‘detail’ views to different tables. Here’s an example of what I built using a simple SELECT:

Tell Toad how to find the related data

Wait, There’s More!

With more than 50 builds so far in this beta cycle containing new features, it’s really not possible to document everything. That’s what the Release Notes are for after all. So if you are disappointed that the feature you requested isn’t listed here. It’s very possible it’s already there in Toad. Let me know, and I can look it up for you!