Jeff Smith's Blog

A New Way to Debug PL/SQL

Jeff Smith — Tue, 16 Mar 2010 07:00:00 GMT

This may be the most misleading blog title of all time. This ‘new’ way of debugging is actually the oldest and true-est form of debugging. What’s new is that Toad now allows you to run your programs and inspect the variables post-execution via DBMS_OUTPUT without coding any of the DBMS_OUTPUT.PUT_LINE () statements.

It’s the Little Things, Re-Visited

Jeff Smith — Thu, 11 Feb 2010 08:00:00 GMT

Once again I was reminded how seemingly simple and common-sense features in Toad are frequently overlooked and yet also provide huge differentiation from other database tools out there. If this topic sounds familiar, it’s because I covered it back in 2008 for Toad for Data Analysts.

While doing a customer visit locally here in the Triangle at a local university, the audience I was presenting to was predominately using a competitor’s product. I won’t mention them by name, but let’s just say the audience was using the tool, but maybe saw room for improvement. The challenge thrown down to me was simple:

“Show us something compelling to think about moving to Toad”

I love the opportunity to wow people with Toad, and I had my entire favorite tricks lined-up and ready to go. Before starting though I asked a very important question – “What’s stressing you out lately in the database?” They thought for a minute, and then their manager talked about how recently they were frequently needing to take all of their foreign key constraints and triggers in several schemas and disabling them for some major data loads. Apparently this was a one item at a time task, e.g. select constraint, right-click > disable, go to next index, and repeat.

So with the table set, I knew I had them right where Toad wanted them!

The Toad Way

What you can do with one object in Toad, you can easily do for one or more. Let’s take care of the foreign keys first.

Open the Schema Browser to the Constraints tab
Set the filter appropriately
Multi-select and hit the ‘Disable’ button

This took me about 60 seconds to demo and honestly only took that long because I talk too much. I was not prepared for their reaction. You would have thought we had normalized their database to 4NF or something.

Apparently they had a hard time believing it should be this easy, because they insisted on seeing it done for the indexes as well. We had only used 1 out of our 90 minutes so I decided to humor them.

The full power of the Schema Browser is unleashed with multi-select and creative use of the filters.

Same basic process:

Go to the triggers tab
Set filter up appropriately
Multi-select, hit the button.

Same reaction, same surprised Jeff.

Apparently the tool they had been using didn’t allow you to perform actions against multiple objects in a single swoop. The filtering capability was also lacking so they weren’t able to get a list of objects from multiple schemas into a single list for performing their maintenance and data clean up tasks. These very two simple tasks that most Toad users take for granted were major items for this audience.

We ended up taking the full 90 minutes they gave us, but I think I could have stopped after the first 10 minutes and left with a completely satisfied and sold group of users.

Here’s a parting shot of operations you can launch against a list of selected tables in the browser:

Coming down the pipe, sneak peak at Toad for Oracle v10.5

Jeff Smith — Tue, 26 Jan 2010 15:20:00 GMT

A full preview is in the works, but sometimes a new feature is so compelling you just want to start telling strangers on the street about it right away. Rather than make you wait till tomorrow to hear all about Apple’s new tablet device, here’s some juicy Toad gossip right now!

DESCribe

Perhaps one of the most productivity-enhancing features in Toad, the DESC (F4) command allows the user to get access to all guts of any database object from the editor. Perhaps the best way to think of it is a miniature schema browser dedicated to a single object. You can have multiple DESC windows open at any given time, and again, Toad supports it for ANY object in the database – not just tables and views like SQL*Plus supports.

How it works now

From the editor, type in the name of the object, and hit F4.

So this works great now, how could it get any better?

You Ask, You Get

I frequently let customers know that one of the beauties of Toad is that it is a user-driven application. The bulk of the features developed and enhanced are directly influenced by our 2 million (and growing!) user community.

A few weeks ago someone in the Beta community suggested we make it possible to invoke the DESC functionality on demand while ANYWHERE in Toad. At the moment a user must open an editor and type the name of the object to get a DESC window. I’ve found myself several times doing just this. And it takes a few seconds and clutters up my Toad desktop. Surely there is a better way?

Now there is!

In Toad 10.5, you can invoke a DESC at anytime! Just hit CTRL+D (D for DESC) and you get this window:

In this example whilst looking at a list of objects my session has accessed, I wanted to know a bit more about DBMS_JOB. So I just hit CTRL+D and up pops the ‘Quick Describe’ window. From here I can just input the name of the object and let Toad do the rest.

This is admittedly NOT a new mind-blowing feature in terms of engineering or technical marvel. However, I’d argue this will fundamentally change the way I currently use the tool, and it will save me a few seconds of time every time I use it. When Toad can make my day easier or scream by just a bit quicker, then I’m a happy user.

Just a little more on v10.5

You can play with this version yourself, assuming you are current on your maintenance contract
It’s due early Spring 2010
We have LOTS of new toys, especially for our developer friends
Don’t worry DBAs, toys for you too including an Alert Log Viewer that’s bound to save you tons of time

Book Review: Toad Handbook, 2nd Edition

Jeff Smith — Thu, 31 Dec 2009 01:13:00 GMT

An exhaustive look at Toad, that doesn’t take that long to read and see immediate improvement in your day-to-day use of the tool. If you are new to Toad, this will shorten the learning curve. If you have been using Toad for 10 years, this will ensure you are not overlooking features or workflows that have snuck in while you weren’t looking.

Why Toad?

Jeff Smith — Wed, 09 Dec 2009 14:39:00 GMT

Toad for Oracle is a world-class enterprise query-develop-tune-administration tool for the Oracle database platform. This document attempts to differentiate Toad from the average Oracle IDE or query tool so that the reader can make an informed decision to invest in the Toad product family.

Following is a list of features and concepts that most commercial and even freeware offerings strive to deliver. Each feature will demonstrate clearly how Toad rises above the competition. If you can live with only the lowest-common-denominator features, then Toad may not be an easy sell. If, however, you need a tool to rescue from hairy situations, tune your systems, and automate those painful daily tasks, then Toad should be a clear winner.

Download Jeff's Toad Tech Brief: Why Toad? (PDF document, 1.2MB)

De-cluttering Your Toad Desktop

Jeff Smith — Wed, 11 Nov 2009 15:42:00 GMT

Quick, what’s the number one complaint about Toad for Oracle?

“The User Interface is waaaaaaaaay too complicated!”

Out of the box, Toad’s setup to pretty much have everything enabled. If we hid stuff, there’s a good chance users would assume the functionality just wasn’t there to be taken advantage of. And, as John so elegantly put it in his latest whitepaper on QuestDotCom, it’s easy for a tool to be elegant when the tool only does one or two things.

Toad does just about everything, so it’s likely the features you do not need or want may get in your way. Fortunately there’s a very easy solution to this: turn off or disable the features you do not use!

In about 5 minutes I setup this Toad desktop:

I disabled most of the toolbars
I removed buttons from the remaining toolbars I don’t ever use
I converted most of the buttons from esoteric graphics to just plain text buttons like so

vs

I closed the desktop panels I never use
- SQL Recall (I use the keyboard shortcuts instead, faster plus less real estate)
- Object Palette (I’m faster on the keyboard with F4 and Ctr+Period)
In the Editor, I turned off the output panels I use infrequently and limited it to the Data Grid, Explain Plan, Script Output, and DBMS_Output

Moving Into the Schema Browser

There is a tremendous amount of information Oracle has to share with the Toad user. We can turn most of it off very quickly!

Schema List – use the Session > Oracle User List dialog to hide schemas you don’t support or interact with, makes the dropdowns a LOT more navigable
Use the DropDown view for the objects – most amount of real estate.
Turn off support of the Object types you don’t work with
Turn off the right-hand-side tabs for each object you don’t work with
Filter, filter, filter!

Remember, if there’s something in Toad you do not like, you can turn it off!

If you decide you made a mistake, and you want to get it back, you can always reset your Toad to look like it did on day one.

Toad for Oracle & XML

Jeff Smith — Thu, 29 Oct 2009 19:00:00 GMT

Oracle introduced support for XML in the relational database going back at least to about the 8i release. Toad for Oracle introduced formal support for XML in the 8.0 release.

XML and Toad v8.0 – v9.7

As you browse or query an object, if it contains an XML

via XML data type
via VARCHAR2/CLOB with XML header to start the string

Toad offers to display and edit the data with the XML editor as shown below.

You invoke the editor by double-clicking on the cell containing the XML string.

XML and Toad v10+

One of the ‘victims’ of Toad’s overhaul to support Unicode for the v10 release was the XML editor. The component was not able to support our requirement for Unicode. Rather than risk data corruption, it was decided to deprecate the feature (for more information, please consult the v10 Release Notes found under the Help menu.) Does this mean you can no longer use Toad to interact with and manage your XML data in the database? The answer is ‘No, no, no, no, no, no, no!’

Out of the box, when you attempt to edit XML data in Toad, it will be displayed in a standard text pop-up editor.

But that’s an icky way to edit and maintain my XML, Jeff!

No arguments here, but Toad gives you an excellent option going forward. I think we can all agree that Toad will never be the best word processor or FTP client available (although our FTP is pretty darn sweet!). The same can probably be said for our older XML editor. It pales in comparison to some of the free and pay editors out there. Check out Altova’s XMLSpy for starters. Toad now allows you to define an external editor for working with XML.

View – Options – Executables – XML Editor

Now when I invoke the editor by double-clicking on an XML cell, Toad sends the XML text directly to my editor of choice. When I’m done, I can send the data back to Toad to COMMIT my work.

I’ve chosen a tool called XML Marker. I don’t endorse it, and please read the ULA before choosing it or any other product for your organization. Microsoft also has something that takes a Notepad approach to XML. Check them out here.

I must admit I was a bit taken aback when this change occurred. But now that I’ve had a chance to digest it, and investigate some of the XML editors available out there, I’m pretty happy with how it’s turned out. I’m pretty sure the existing XML editor that Toad was using hadn’t been enhanced since it made its debut in v8. Now letting Toad tackle the database stuff and having a best-of-breed XML editor handle the XML stuff that Toad can talk to, I have the perfect combination!

More Toad Tips and Tricks

Jeff Smith — Mon, 26 Oct 2009 20:48:00 GMT

So with Toad v10 being released, my Tips & Tricks guide became a bit out of date. You can find an updated copy, as well as our v9 copy, here. All of your favorite keyboard shortcuts and workflows remain largely unchanged, so this is more of an update for making sure screenshots and naming conventions are correct for our newer users.

For our Xpert edition users, you will see significant changes in the SQL Optimization interface and workflow. Be sure to investigate the 4 different SQL tuning methods, in particular ‘Optimize’ and ‘Rewrite.’ The nature of your query (how long it takes to run, how complex is it?) will determine the method that’s best for you.

Feel free to host your own internal Toad seminars using any of our content. If you need assistance or want some custom content developed, just let us know!

SQL Navigator behavior in Toad for Oracle

Jeff Smith — Mon, 20 Jul 2009 15:03:00 GMT

We got an interesting question into our sales support queue this week. A new Toad user was frustrated because they weren’t seeing the type of feedback they were used to seeing in SQL Navigator.

Toad Tips ‘N’ Tricks

Jeff Smith — Mon, 13 Jul 2009 16:59:00 GMT

This blog recaps a few of the major highlights that Jeff covers in a typical two hour Toad for Oracle tips and tricks session.

Dev Tips/Tricks Cheat Sheet

Jeff Smith — Wed, 10 Jun 2009 14:15:00 GMT

So if you’re reading this blog, hopefully that means you’ve just finished watching my ‘Discover the Hidden Treasures of Toad for Oracle, Developers’ presentation at our 2009 Toad Virtual Expo. If not, then you can still watch this 45 minute presentation for the immediate 60 days after the conference.

What I wanted to do here is go into a little bit more detail than what I could deliver in a powerpoint presentation. I’ll try to add a little flavor to the ‘Hidden Treasures’ we exposed for developers and point you to some other resources on ToadWorld that might help you on your way.

PL/SQL Profiler

This is a feature that has been included with the Standard edition of Toad for about the last ten years. What does it do though? Well, if you want to know where the majority of time being spent executing your code is, the profiler will shine a very bright light in this area. If you have a performance bottleneck in your PL/SQL, the Profiler breaks down execution times at the LINE level of your program(s).

To invoke the profiler you need to:

Ensure the DBMS_PROFILER package is available
Ensure the Toad profiler runs table is configured
Toggle on the profiler
Execute your program

Once you have performed these steps, you can now see exactly how and why your programs are taking so long to run. If performance tuning falls into your job description, this is a ‘MUST KNOW’ feature of Toad!

Use the Toad Server Side Object Wizard under the ‘Database’ – ‘Administer’ menu navigation to setup the Profiler in your database.

To toggle on the Profiler, make sure the ‘Stopwatch’ button is depressed on your Toad toolbar.

Code Quality Reviews, aka ‘Code Xpert’

The first topic we covered was around code reviews. While everyone might not have the time or resources to allow for developers to go over each others code and provide feedback, most can agree that it it usually a worthwhile exercise. So, how can Toad help? Well, we have been working with Steven Feuerstein for several years now on making Toad the ideal PL/SQL IDE. One place we have spent significant time is code reviews.

Some of the things you may have read in Steven’s Best Practices book on PL/SQL have been translated to rules in Toad. You can run these rules against any or all of your PL/SQL – whether it is in the database, or if it is in your file-based source control system.

With any PL/SQL source loaded into Toad’s editor, you can activate the ‘CodeXpert’ tab.

Or, you can open the CodeXpert console via the Database – Diagnose – CodeXPert menu navigation.

The output of the report falls under these categories:

Rule violations
Program Properties Documentation
Embedded SQL execution plan analysis

The rule violations are fairly straightforward. Clicking on a rule violation will take you to the offending piece of code. Double-clicking on the rule will open the documentation on the rule. If you choose to disagree, you can disable the rule.

The program property reports include information detailing things like your Oracle version dependencies (using Timestamp?), cursor analysis, and DML analysis. You also get a full CRUD Matrix for each program so you know exactly what tables and views are being updated or queried.

The Execution plan analysis will alert you or your developers to queries that may be in need of tuning.

All of this information can be stored in a repository. This allows you to build delta reports and track progress on any projects you currently have in development.

Make/Strip Code

Small feature, big impact. This feature has been around almost since the very beginning of Toad. What does it do?

If you are an applications developer and you need to embed SQL into your program, then most likely you are typing things like:

SQL = " select * from scott.emp;SELECT   COUNTRIES.*, "
SQL = SQL & "         REGIONS.*, "
SQL = SQL & "         LOCATIONS.*, "
SQL = SQL & "         DEPARTMENTS.* "
SQL = SQL & " FROM   HR.COUNTRIES, "…

Sounds tedious, right? Well, Toad can take your existing SQL statements and have them automatically embedded into the following programming language declarations:

C#, C++, Delphi, Java, Perl, VB, & VB.NET.

These are configurable in the options:

Did you know you could even create your own code templates? I’m still waiting for a PeopleSoft expert to help me build one for SQR’s

Here are the two buttons you want to look for in the editor to invoke the ‘Make Code’ or ‘Strip Code’ features:

Code Templates

Not much to add here honestly. You basically have a series of text files mapped to PL/SQL objects in your options:

You invoke them here:

Which opens this dialog:

Filling out this information allows you to generate all of the basic program, documentation, and exception blocks of your PL/SQL programs. You can create multiple templates for each object type.

Putting these on a network share and encouraging your co-workers is a good way to promote coding standards. Plus if you can save your co-workers some time, then that’s a great example of how being a ‘Toad Master’ can help you stand out as an employee and as a developer.

You can extend this concept from PL/SQL templates to basic code templates. For example, instead of having a full package body template, what if you just wanted to pop out a quick If…then…elsif block? These are invoked with the CTRL+SPACEBAR keystroke.

Notice how each of those code blocks have a bolded name associated with them? If you simply type the name, e.g. ‘crloop CTRL+SPACEBAR’, that will create a cursor loop statement for you. Three big reasons why I L-O-V-E this feature:

Completely customizable (see screenshot below)
You can create variables to make these statements dynamic
You can tell Toad where to put the cursor post-replacement so you can start typing right away

Use the ‘|’ to denote cursor placement, and use ‘&’ to denote variable replacement in your templates.

ER Diagramming and Code Road Maps

It should be noted that the ER Diagram feature has been given a MAJOR facelift for v10 of Toad which is currently under development and available as a beta release.

Toad v10’s version of an ER Diagram.

A few tips:

Invoke from a right-click on a table or view in the Schema Browser
Use the ‘SQL’ button to build a query statement with all of the joins defined

For Code Road Maps, keep the following in mind:

Invoke from a PL/SQL object in the Schema Browser (mouse-right-click)
Identifies ALL objects in the database that are required for the code to compile
Can be used to build a sandbox DDL script to create a test environment of JUST that specific piece of PL/SQL (versus reproducing the entire schema)

Followup Links/Resources

You might be a Toad user if…

Jeff Smith — Thu, 28 May 2009 00:45:00 GMT

In humble tribute to the great southern comedian, Jeff Foxworthy, I thought I’d try a send up on this popular theme as it pertains to Toad. So here goes. If you have your own suggestions, just leave them as comments at the end of the blog!

The ‘ribbet’ sound announces to your cube-mates what time you’ve made it into work.
Did you know you can disable the ‘ribbet’ and customize Toad to play any .WAV file you want?
Your mouse’s right-clicker tends to wear abnormally fast
Everyone knows that Toad’s best features are just a right-click away!
You take more than 2 minutes to deliver a report or data export
Unfortunately you’ve built quite the expectation of being super-speedy with your Oracle data.
Your reports tend to show up at 2AM the day they’re expected
Of course you’re not working that late, but Toad’s task scheduler is working overtime!
Your PL/SQL and SQL looks pristine
You’d sooner give up Word’s spell-checker than forgo Toad’s formatter.
You’re not used to hearing DBA’s gripe about supporting your code
Tuning, debugging, unit-testing, and load-testing do not make you nervous.
You routinely have a string of people lined up outside your office
You’re known as the person who can make magic happen in the Oracle database.
You’re comfortable working with Oracle versions 7, 11, and everything in-between
Find another tool that supports Oracle better, I dare you.
You’ve had at least 3 different job titles around the Oracle domain
Toad loves you whether you’re a developer, DBA, support person and facilitates transitions.
You have little problem finding that NEXT Oracle job
Lucky for you that ‘Toad’ is listed as a requirement for the most serious Oracle job postings on Monster, Dice, and Twitter.

Customizing Data in your Toad Explain Plans

Jeff Smith — Thu, 14 May 2009 14:17:00 GMT

When you ask Oracle for an execution plan with Toad, we insert the plan into a plan table (configurable by the user.) Wow, that’s 36 pieces of information that could be included in every single plan step. Now, when Toad shows you a plan for a query, it chooses to show only the most common pieces of information.

Sneak Peak at Toad v10

Jeff Smith — Thu, 07 May 2009 13:24:00 GMT

While we are still quite a few months away from officially releasing the next version of Toad for Oracle, there are a few exciting developments to share with you today. Of course, you can follow the development of the next version of Toad yourself via the beta program, but we realize not everyone has the bandwidth to do so. Most years will see at least 2 releases of Toad. However, so much is going into Toad v10 that we will only have one release for 2009. That hopefully gives you an idea of just how much work is going into making the world’s #1 tool for Oracle an even better experience for analysts, developers, and DBA’s everywhere.

Here’s what you can get a gander at now:

Unicode support
New Data Grids
New ER Diagrammer

Unicode Support

Toad has offered partial support for Unicode via several different avenues over the past several years. It should be noted that the current version of Toad can be made to display non-English characters and that we in fact have a huge Toad user base in places like India and South Korea where this is a requirement. Toad users have come to expect more than just ‘partial’ support for anything, and we at Quest take pride in responding to input from the field.

The final solution – a version of Toad for Oracle that offers full support for Unicode throughout the product – is now taking shape. I know that the development team has put a lot of their own sweat and blood into this work and I think that our users worldwide will benefit from it greatly.

So, regardless of the data you are working with, Toad can now display it, modify it, and export it to files with Unicode without fear of corruption either on the client or server. Below are a few screenshots of Toad interacting with Chinese, Korean, and Japanese as an example of this.

The editor can now both input and display Unicode data. The data grids can now display both Unicode data and Unicode encoded column names from both single statement executions and Script Output. If you have need of SQL*Plus output commands and Unicode, then Toad’s F5 scripting engine would be the ideal solution.

A DESC (F4) popup window of a Chinese table name with Chinese named columns.

A Korean named table and its Korean named Index.

In the editor, you can now save files with various encodings.

New Data Grids

While the product was being revamped to support the new version of Delphi to accommodate Unicode, the other components ‘under the hood’ also got a chance to get their tune-ups. The most noticeable for the end user will most likely be the Data Grids. This includes of course the grids to show query results and table contents. But, it also includes every single place in Toad where data is being displayed to the user including the ‘Columns’ tab for the Schema Browser ‘Tables’ section.

The grids have some new functionality like the ability to auto-group by specific columns. The grid interface has also been revamped. The grid toolbar and right-hand-mouse popup menus have also gotten a thorough scrubbing. Here are a few examples:

We can see a few new things here:

Multi-column grouping, 1^st level by Department_ID, 2^nd level by Job_ID
Grid cells and headers are now able to display in different fonts and in different colors
The record navigation has been moved to the bottom and the toolbar has been simplified

The right-hand-mouse popup menu has also been cleaned up. ‘SaveAs’ as been renamed to the more descriptive ‘Export Dataset’.

Note that ‘Select Columns’ feature has been moved from the popup menu to the upper left-hand corner of each data grid

New ER Diagrammer

You have been able to build simple entity-relationship diagrams based off a single table in the Schema Browser. This feature has been given a major overhaul and now uses the same visual components as our world-class data modeling tool, Toad Data Modeler. While you will not be able to do full schema database reverse-engineers, if you need to build reports for developers to go against subsets of the database, you will not want to miss out on this feature!

Step One: select the objects you want modeled. Note that you can add objects from ANY schema to the model.

A physical model diagram of the selected objects.

You can now export your models to HTML reports!

When can we expect the next version to be officially released?

We can’t promise an official date, but I would start looking for announcements starting early this fall, or if you’re address falls below the equator, this spring. Again, if you participate in our beta program, you will get updates weekly with new features and news on what to expect.

How do I move more than 1 table at a time in Toad?

Jeff Smith — Tue, 31 Mar 2009 13:20:00 GMT

This was a question I got this morning from a customer. I showed them how to do this with the ‘Alter’ dialog in Toad’s Schema Browser. However, they wanted to do it for more than one table at a time, and apparently some OTHER tool could do so. Why couldn’t Toad?

Great question. It turns out that IT CAN INDEED do so. Here’s how:

Step 0: Open the ‘Rebuild Multiple Objects’ window

Step 1: Load and Check tables

Step 2: Select new tablespace (by size)

Step 3: View Script (or just execute)

Here is what is generated and ran on your behalf:

So, we can do anything ‘your other tool’ can do and more (if not better)!

Data Grid Quiz

Jeff Smith — Wed, 25 Mar 2009 17:45:00 GMT

I think we can all agree that of ALL the features in Toad that impact the most number of users is the data grid. Whether investigating the contents of a table, running queries, or monitoring connected sessions in the Session Browser, the data grids deliver information to the user. How well you know the data grid and all of its embedded features will impact how useful and productive you are for your employer.

We often say that most Toad users only take advantage of about 10% of the products functionality. I think this applies equally to the data grids. Let’s take a little quiz. How many of the following data grid features can you honestly say you are both aware of and are comfortable using?

I will post an in-depth follow-up blog shortly that goes over each of the following features. You can also look forward to a more complete tutorial available in our upcoming Toad for Oracle eBook.

Column Re-ordering
Fixed or Pinned Columns
Column Selection
Multi-select
Filtering and Named Filters where available
Single Record View
Report
Grid Menu

You’ll notice I didn’t mention ‘SaveAs.’ I think we can also all agree that this is the one feature everyone knows and understands. However, let’s make a bonus section quiz that JUST covers SaveAs.

Excel File vs Excel Instance
Selected Rows
Recording for Replay
Invoking for multiple tables simultaneously in a single click (actually it would be at least 2 clicks)

If you can honestly say that you are comfortable with all of these features, then I think it’s time you step up and volunteer to become your local officially recognized ‘Toad Expert’, available for taking questions from your coworkers and even organzing regular Oracle or Toad-themed lunch-and-learns.

If you can only admit to knowing or using only a few of the above mentioned features, then don’t feel bad. You are firmly in the middle of the pack, but with just a few steps can put yourself clearly at the front. I think we finally all agree that this is especially a good time to be considered indispensible.

As always, Happy Toading!

Toad Data Grid Trick?

Jeff Smith — Tue, 24 Mar 2009 15:53:00 GMT

While co-presenting with Bert at the Rocky Mountain Oracle User group, Bert yells out “Hey, show that cool FK lookup thingie in the data grids.” I was like, ‘huh?’ Surely Bert just didn’t call me out in front of 100 or so people on a Toad feature that I did not fully understand?

Well, I got around to figuring out just what Bert was talking about, and NOW I remember what all the fuss was about. This feature was added in the Schema Browser table data grids waaaaaaay back in the V8 code base I think. What it offers is a chance to pull up ‘lookup fields’ for any foreign key-controlled record.

For example, if you have a table column whose value correlates to another table, i.e. referential integrity anyone?, then Toad can in a single click show you the related record.

If you click INTO the column cell, you will notice an ellipsis button pop up.

Clicking on the ellipsis, will pop-up all the ‘master records’ from the ‘lookup table.’

Caveats

As you might expect, this feature could get to be a bit annoying, especially on extremely large lookup tables. And of course, Toad has to query the data dictionary to see if the table being displayed HAS any foreign key controlled columns, so that could theoretically cause a performance hiccup.

To see if your Toad has this feature enabled, take a look here:

So let this be a lesson to ya! Well, make that two lessons:

Be on your toes when presenting with Bert
When you think you’ve mastered everything there is to know about Toad, you’re just setting yourself up for embarrassment later. There’s ALWAYS something new to pick up and increase your productivity.

Customizing Toad Code Templates

Jeff Smith — Tue, 24 Feb 2009 23:01:00 GMT

I recently got an email from a user, and after I replied figured that it might be helpful for others to see as well.

The Question:

“I was at the ROUMG conference and have a question. You guys showed how to setup a short cut in the options. Like to do a select statement – you can type in a short cut word and it will do the put the script in. Like autofinish.

I know I need to build these shortcuts, but where and how again????

I hope I am making sense. “

The Answer:

Perfect sense! Toad has so many useful features in the editor, it can be hard even for us to remember what they are all called sometimes and how to invoke them. What you are referring to in Toad is known as the ‘Code Templates.’ Code Templates allow for predefined snippets of code to be inserted into the editor on demand by name, or by manually choosing from a list. They can be extremely powerful because of how flexible they are and because you can customize them to fit any need you may have, whether that be coding in SQL, PL/SQL, HTML, XML, etc.

To see what you already have at your disposal, in the Toad editor, just press CTRL+SPACE

With nothing typed at the cursor, it will bring up the entire list.

If I were to type “bcquery”, it would replace the ‘bcquery’ text with the text programmed for that ‘macro.’

To set these code templates up, you need to get to your editor options.

Depending on your version of Toad, these can be in several different places.

In the latest version the developers have made it VERY easy to find these shortcuts

View > Toad Options

This will popup this dialog

You would start by clicking the ‘Add’ button. Fill out the name and description of your new ‘script.’ This is what you will see on the dialog in the editor.

Below that, you need to type in the text you want dynamically placed into the editor.

You’ll see on my example, I have used the ‘|’ and the ‘&’ characters –

| - this tells Toad where to put the cursor after the text replacement.

& - this indicates a variable that will be used to prompt you for a value before replacement. You can use as many as you need.

I hope this helps!

Happy Holidays: A Toad AppsDesigner App Just for You!

Jeff Smith — Fri, 05 Dec 2008 18:46:00 GMT

For most users, the significant new feature for v9.7 will be the AppsDesigner. This technology allows a Toad user to take a collection of Toad commands or Actions and create a miniature Toad App or macro that you can then replay on demand or schedule to run when needed.

Like most new features, you generally will learn how to use them when you are faced with a task that requires said feature. I’d like to share an example that I created. I hope that it gives you an idea of how Toad’s continual evolution keeps it far and away the very best Oracle IDE available – free or otherwise!

A Few Reminders

This functionality is part of Toad Standard v9.7 and higher. A similar technology is available in v9.5 and v9.6. Prior to v9.5 you can do similar work via the Toad.exe command-line but it is not nearly as elegant or user-friendly.
The feature is available under the ‘Utilities’ menu (AppsDesigner)
You can create an action on the fly by clicking on the ‘Save/Load snapshot’ button in the Toad windows that support this technology.

Notice the little ‘camera’ button at the bottom-left-hand corner of the window? Look for this button on your favorite Toad windows!

So just what does this thing do, anyway?

I created a Toad application that creates a copy table, then loads the data by doing an Excel export from the original table, and then executes an Excel import. It also creates/updates a ZIP file that contains a running log of all of my excel files, and then sends an email with the report file attached to the recipients that need it. If a problem occurs, then I have an email notification sent to the creator of the report (me) and another sent to the recipient letting them know there is a problem and we are working on it.

Sounds simple enough, right? And this is something that I bet many of you are doing already with Toad, albeit via clicking through screens and launching your email and WinZip clients as you need it. Well, now with Toad v9.7, you can simply program all these steps via Toad and then have them run with the click of a single button.

Voila, the finished product!

The Steps or How It Works

You can walk through these manually and test them step-by-step, or you can cheat and go to the end of this blog and ‘steal’ the source that has all of this done for you.

Open the AppsDesigner
Create a new ‘App’ (fourth button on the AppsDesigner toolbar) – make sure to give it a good name!
I used the Schema Browser to create the copy of my table
1. Select table, mouse-right-click > Create Like
2. After it’s finished, go the Scripts tab and copy the DDL (we need it later)
Add our first action – ‘Execute Script text’
1. I supply the database connection
2. Choose ‘text’ for script source
3. Copy the DDL from 3b.
4. Modify the script to your liking, but it basically drops the table if it already exists and creates a new one, plus a truncate for extra measure.
Generate the data from the source table
1. I used a named filter from the Schema Browser data tab that grabs just the data I want to move over, you can of course use any query you choose
2. From the data grid, open the ‘Save As’ dialog, fill out the details for the excel file to be created, and create an ‘Action’ based on that snapshot
3. Add it to our App as named in Step 2
Now we need to create an action that will take the data from step 5 and load it to table created in step 4
1. On the ‘Import/Export’ tab in the AppsDesigner, add a new ‘Import Table Data’ action
2. Step through the wizard and supply the field mappings appropriately
3. I chose ‘Commit’ at end of transaction so I don’t need to be there when this runs
Now that the table is populated, I want to create a report based off this data
1. I chose to use the ‘Export Dataset’ action
2. Choose your report format (I did Excel) and give it a file name
Now I want to keep a running log of these reports, in case I ever need to go ‘back in time’
1. On the ‘Utilties’ tab, create a new ‘Archive’ action
2. Fill in the details, the ZIP file to be created, the files to be added, whether you want them replace or not
3. Notice this Action can also be used to extract files from an archive – way cool!
Ok, now I’m ready to deliver the report via Email (I could also use FTP if appropriate)
1. Create a new ‘Email’ action
2. Fill out the necessary properties (you’ll need a valid SMTP server)
3. Make sure to include the attachment information necessary with file created in step 7
4. Note you can supply the file name manually if it does not exist yet
OK, so we COULD end it here, but I want to be extra diligent and do some basic error-checking
1. On the ‘Control’ tab, I added an ‘If..Then..Else’ action
2. If output files do not exist, email me and recipients letting them know there’s a problem
3. Otherwise, add a note to the log that everything ran as expected

Ok, if you’ve made it THIS far, then Congratulations!

You should now have an above average understanding of the AppsDesigner and a number of its more interesting Actions. Or, if you cheated like I told you to, you can now import my App that I created. You’ll just need to:

Import the App by using the ‘Import app from file’ button
Modify the connection properties (db name and user/pwd)
Change the table names as appropriate
Update the email information, please do or I’ll be getting your reports!
Test, test, test before you roll into production

F-what?

Jeff Smith — Thu, 20 Nov 2008 17:10:00 GMT

After working with Toad for almost a decade (I think I first saw it as a new grad in ’99…), I tend to start taking things for granted when I do demonstrations for customers or present at conferences on the beauty of Toad. It’s very easy to get excited about CRUD Matrices and the beauty of PL/SQL debuggers, but overlook the critical core features that users often miss out on.

Well, ‘overlook’ is such a critical, judgmental word. Instead, let’s say that users often don’t realize the power that lies at their hands via the keyboard shortcuts in Toad. I want to take 20 minutes or so to write about my favorite ‘F’ key: F4 DESC.

After doing the ‘Toad-demo-thing’ for some time now, I’ve come to discover that most of our users are not aware of this feature. In fact, many people are not even aware of the SQL*Plus ‘DESC’ or ‘DESCRIBE’ command. Let’s take a quick look at it in action:

SQL*Plus shows the table structure – Column Names, Nullability, and Data type.

I know, this isn’t exactly earth-shattering. Toad has had such a feature for quite a long time. You can either type ‘DESC [object]’ in the editor or execute with F9 (using F5 will show the above information), or you can simply put your mouse-cursor on the object you want described and hit ‘F4’.

Toad’s ‘DESC’ popup window for a table object.

A few key observations:

You can ‘DESC’ ANY object or object type in the database – users, roles, tablespaces, stored procedures, directories, etc. SQL*Plus only allows you to ‘DESCRIBE’ data objects like tables and views.
The popup describe window is a miniature Schema Browser window that just contains the information for that object. So not only do you get the column information, you get everything else too!
The full power of the Schema Browser is available here. Try mouse-right-clicking in the popup window.
You can have more than one open at a time, so if you want help with a query, like looking at opposing data or even updating the data as you write the selects in the data grids, you can easily do so.
You can drag table/view column headers into the editor space (just like the Object Palette)
This is the most frequently borrowed feature in the database IDE world. Try using ‘F4’ in another database tool to see if they too have seen the wisdom of the Toad J
If you want to see something even cooler, instead of pressing ‘F4’, try ‘SHIFT’+’F4’

Enjoy!

It’s the Little Things…

Jeff Smith — Fri, 03 Oct 2008 15:00:00 GMT

We often spend a lot of time trumpeting the sexy little widgets in our database tools and overlook the little things that really aid in the development of our applications or even just writing the mundane queries that fill up our day.

I’d like to just spend a quick 10 minutes on letting you know about a cool little feature that Toad for Data Analysis offers for writing your queries in the editor. The technology is usually referred to as one of the following:

Code Insight
Code Assist
Type-ahead.

Basically it boils down to the Toad editor offering to complete the clause or phrase you’re currently working on. It looks at what you’ve typed already and uses its powerful SQL parsing technology (available for Oracle, DB2, MySQL, SQL Server, and now Sybase!) to figure out what should logically come next so you can just select it with your mouse versus wasting another dozen or so key-strokes.

Let’s take a look at 2 very simple examples now.

SELECTs

As you’re working on a SELECT clause:

Notice that Toad for Data Analysis brings up the columns for both tables from the FROM clause. As you mouse over a column it also shows you the underlying datatype!

WHEREs

As you’re working on a WHERE clause:

Notice how we again list the columns available for building out your filtering clause. What’s even more exciting, notice how we are offering to auto-complete the JOIN clause for the two tables listed in the FROM section.

Here’s a more complex example:

Compelling Features for Upgrading to Toad v9.7

Jeff Smith — Thu, 25 Sep 2008 13:32:00 GMT

So, another version of Toad has been released. It happens a few times a year. If you’re lucky it’s up to you if and when you want to upgrade to get your bug fixes or new features. If you’re in a larger organization, there’s a standards committee that decides who gets what when. I thought I’d spend a few minutes to document what I see to be the key features available in v9.7 that would give someone serious reason to upgrade today versus later. I’ll break this down by our standard user personas:

Analysts (People who go into the database that don’t fall into the other 2 categories)
Developers (Applications or PL/SQL)
Administrators (System or Oracle)

Here are a few reasons that apply to everyone equally:

66 bug fixes since the 9.6.1 release
Additional support for 11g
You want to stay current on support (we now officially support v9.7, 9.6.1, and 9.5).

Now, for the fun stuff J

Analysts

Access to Access, Excel, MS SQL Server, DB2, and Sybase ASE
Easily schedule your queries, generate spreadsheets, email them to your loyal users

Access to Access, Excel, MS SQL Server, DB2, and Sybase ASE

Chances are you have critical business information stored somewhere other than Oracle. If you love Toad for its ease of use to getting your data in and out of the database, then you need to upgrade to v9.7 right away. Toad now ships with our Toad for Data Analysis product which extends connectivity to ANY database platform. You can read more about this here.

Easily schedule your queries, generate spreadsheets, email them to your loyal users

Toad’s automation technology has taken a quantum leap with v9.7. If you can figure out how to point and click, then you can easily schedule Toad to run just about any task you would normally do ad-hoc. This includes, but is not limited to,

Connect and execute one or more queries or scripts
Export results to the format (file) of your choice
Email them to colleagues
FTP them to remote destinations
Add them to Archives (ZIP) for historical purposes

If you’re in the editor, you can take any query result set and click on the ‘Schedule Action’ button to tell Toad to make this available as a macro. You can additionally open the Toad AppsDesigner window and manually build out your automation tasks.

Look for the ‘Schedule’ button your favorite screens to immediately setup that operation as a Toad macro for replay on demand or as a scheduled event.

Toad’s AppsDesigner empowers the Toad user to take their favorite Toad utilities and invoke them for specific tasks. These can be put together to build very powerful little programs or Toad applets. This blog doesn’t provide the space necessary to give a full walk-through, but I do not think it is an understatement to say that this is the single biggest improvement to the product for v9.7.

Create new ‘Applets’, see their run history, schedule them to run whenever you want. Toad can ‘wake up’ your machine and run your program while you are away from your desk.

Developers

Full support for Subversion source control (file-based AND via Team Coding)
Formatter updated to recognize and support 10 and 11g syntax
Generate test data for your tables on the fly

Full support for Subversion

This has already been covered in an excellent blog by Toad’s Product Manager, John Pocknell. I just wanted to make sure it’s not overlooked!

Updated Formatter

You will want to read the release notes for information of formatting styles that have been changed to reflect the new options dialog. A big improvement also includes the ability to preview your syntax change directly in the formatter options dialog so you can see exactly who your code will look before and after a format.

No more guessing how your code will look based on a particular formatting option. The best formatting engine for Oracle just got a LOT better folks!

Generate Test Data

Available in the Professional edition or with the DB Admin module, Toad’s ‘Generate Data’ feature allows a user to take one or more tables and generate large amounts of test data. The engine will even obey the RI constraints and generate PK/FK key values between parent and child tables. A large number of datatypes are followed and this feature should continue to grow even more powerful based on feedback from our Toad users.

Right-click anywhere you see a Table in Toad to access the ‘Generate Data’ feature.

Administrators

Trace File Browser
Improvements to Compare Schemas and Health Check
RMAN interface

Trace File Browser

Tired of TKProf? Want to easily SEE your queries with execution time breakdowns, waits, and bind variable values all in context with execution plans? This is all available with Toad’s DB Admin module under Database > Diagnose > Trace File Browser.

This feature will change how you treat and view Trace files. Seriously. Upgrade. Now.

Improvements to Compare Schemas and DB Health Check

For Compare Schemas, you can now easily compare 1 source schema to MULTIPLE destination schemas. Want to see how PRODUCTION differs from TEST and DEVELOPMENT? It’s a single compare now. And, you can easily generate a baseline definition of your schema to be used later as a point-in-time comparison versus going against the schema source live.

Database Health Check’s interface has been streamlined and should just be easier to run in general. It has also been hooked up to Toad’s automation engine (as was Compare Schemas), so you can easily schedule it to run various checks against various instances whenever you want and email you the results. Want an email every 12hrs with updates on ORA-00600 alerts? Want a list of tables missing indexes every week? This is now a trivial task with Toad v9.7. You can additionally tell Toad to save the results in a repository so you can see the differences in your database since the last Health Check.

Summary

There are many new cool features in Toad. You can see a more comprehensive discussion in the release notes and in the ‘Get To Know’ document series on ToadWorld. I also want to throw a quick note out there on the installer. It should do a much better job on detecting older copies of Toad installed on your machine and offering to upgrade them.

10 Things You Must Know about Toad’s Editor (Part 2)

Jeff Smith — Mon, 08 Sep 2008 18:25:00 GMT

The editor window is probably where most users spend a majority of their time. In this blog, I’ll continue a conversation we had a few weeks ago. Please note that all of these features are available in the standard edition of Toad for Oracle, version 9.6.

6. Editor Options

Toad has many options for configuring the look and feel of the application. This definitely includes the editor. Here are the most common settings you will want to address before you get very far with Toad.

Editor Fonts

If you need a larger FONT size, or want to use a FONT that has non-English character support, you will need to go to the Editor options. If you are using a version of Toad older than 9.6, you will need to consult the help as these instructions have recently changed for the better.

Go the ‘Editor > Behavior’ page in Toad options. Push the ‘Editor font’ button.

Formatting Options

Toad’s SQL and PL/SQL formatter is completely configurable. For example, tell Toad when you want a series of 5 space characters converted to a single Tab character, or specify when to wrap lines in a sQL statement. To set this, you need to open the Formatting Options dialog. Once your options have been set, use the ‘Save’ button. You can then share your Formatting options file with other Toad users to encourage similar code formatting styles.

Please note the Formatter is being updated for Toad v9.7. Whenever possible your existing Formatter options will be honored when you upgrade.
Please read the v9.7 Release Notes for more information.

7. Reverse-Engineer SQL statements to a Visual Query Builder session

Have you ever tried to make sense of a query someone else has written? How about a query you yourself wrote a few months ago? Did you know that Toad can ‘visualize’ your problematic SQL queries in the Query Builder? Simply highlight your query in the editor, mouse-right-click, and choose ‘Send to Query Builder.’

One button click to convert Oracle Join Syntax to ANSI Join Syntax and so much more!

8. Auto Generation of Package Body from Existing Package Spec

Did you know that Toad could look at an existing PL/SQL Package Specification and auto-generate the complimentary Package Body source? Simply load your Package SPEC into the editor, and use the SHIFT+CTRL+C keyboard shortcut. Toad will parse the editor for the SPEC and paste the new BODY into a new tab on the Editor desktop.

The code is generated using the template you have defined in your Toad options. To learn more about this feature, read the Help topic titled ‘Using Templates within Packages.’

Never underestimate the power of the keyboard!

9. F9 versus F5 Statement Execution

Statement execution in Toad is pretty straightforward. If you want to execute a single statement, always use F9. If your editor has multiple statements and you are not using statement delimiters (; or /), then you will need to use SHIFT+F9 or highlight the statement you want to execute and use F9. If you have multiple statements and you want to execute them all, then use F5. Be sure to read Bert’s blog series on Toad’s script execution to learn more about this powerful Toad feature.

10. F2 and SHIFT+F2

If you have ever accidently hidden your results panel or your editor panel in the editor, then simply try using F2 or SHIFT+F2 to get back. F2 will toggle you to full screen editor mode. Hitting F2 again will send you back. Using SHIFT+F2 will send you to full screen result set mode. Hitting SHIFT+F2 again will send you back.

Writing SQL in Toad for Data Analysis

Jeff Smith — Mon, 11 Aug 2008 16:28:00 GMT

In my previous blog, I covered things that you should know about Toad for Oracle’s editor. I thought for today we could spend a few minutes on what users may benefit the most from Toad’s cousin, Toad for Data Analysis. This blog will concentrate on working with SQL.

1. SQL Recall

The time you’ve spent engineering the SQL that will one day show up on your resume will continue to pay dividends for as long as you use Toad. Every statement you’ve ever written is remembered by Toad for Data Analysis and is ready for instant recall.

The basics:

Every statement you execute gets recorded
By default we remember the last 100 executed (configurable with no upper-limit)
SHIFT+F8 opens the SQL Statement Recall panel (also available under the View menu)
ALT+PgUp/Dn will cycle through the most/least recently executed statements

2. Code Snippets

When you need to make a call to an database provided function, you can waste a lot of time going over your existing code looking to copy/paste or even more time trying to find examples on the Internet. In Toad for Data Analysis, I suggest you start using CTRL+SPACEBAR. This keystroke will activate Toad’s Code Snippets.

Toad will allow you to progressively drill down into a category of database function, complete with documentation on how to use them!

The real power here lies in the ‘Favorites.’ We can take a SQL query we’ve stored to the SQL Recall and convert it to a ‘Favorite’ code snippet. Or, you can choose to hand code your collection of frequently used snippets of Code. Just use CTRL+N to pull up your list of favorite snippets of code.

3. Code Insight (Dot Lookups)

When you want to write a SQL query or make a PL/SQL call, you probably spend a bit of time manually looking up the column names or command arguments required in the Database Browser. Stop doing this right now! From now on, let Toad figure this out for you automatically in the editor.

The basics:

schema. – this will pop up a list of all objects in the current schema you may want to type
schema.table. – this will pop up a list of columns for a given table. You don’t need to prefix the schema name if the table is in your current schema.
schema.[A-Z]+CTRL. – ok, this sounds more difficult than necessary . If you start typing your object name and hit CTRL. (CTRL key with a period), Toad will pop up a list of objects that match that pattern.
Toad waits .5 seconds after seeing the ‘.’ or ‘(‘ character before popping up the list of items available for that object. This is configurable down to the millisecond for optimal usage. You can also configure Toad to list the column names in the order they appear in the table, or alphabetize them

You can use aliases for your view/table names and the code insight feature will automatically include the alias when bringing back a list of columns. Toad will even show you the column comments so you know exactly what you’re looking at!

4. F4 DESC

DESC, or Describe, is a Oracle command that will display the table or view structure, e.g. a list of columns and their datatypes with size and precision. Toad for Data Analysis has its own DESC command available for all of the supported database vendors (Oracle, SQL Server, MySQL, DB2, and for v2.0 – Sybase!) You can invoke it immediately for ANY object in the database by typing that object in the editor, putting you cursor on it, and hitting ‘F4’. You can type away in the editor with the DESC dialogs available to you. Ever find yourself working on a query only to find out you need to consult the existing columns or indexes? Or maybe you just need to see the existing data or even update a row to get your report query to work properly? Don’t go to the Database Browser anymore, just use ‘F4’ for instant gratification.

Get instant access to any database object with the power of the
Database Browser immediately available.

5. Make/Strip Code

Have you ever asked a developer for help on a query to only get a jumbled mess of Java of Perl script? Sure, it has the SQL in it that you need, but you spend as much time trimming off the quotes and other junk characters than you would have just writing the query from scratch, right? From now on, just let Toad for Data Analysis do this work for you! Take any embedded SQL statement and strip away the non-SQL syntax for instant execution in the Editor. This is one of the most over-looked features in Toad. No more REGEX search and replace to get your well-crafted SQL statement ready for your Eclipse or Visual Studio application.

To activate the feature, just use the ‘Editor’ toolbar menu:

Presto-change-O! Your SQL is now ready to be put into your custom report or spreadsheet.

Configure Toad to use the programming language of your choice!

6. Reverse Engineer your SQL to a Query Builder Model

Ok, I’m cheating a bit here. This feature has been available in Toad for Oracle for awhile now, and has been frequently requested as a feature in Toad for Data Analysis. Well, the waiting is almost over! Toad for Data Analysis users will be able to take advantage of this feature in v2.0 of the product which is due in a few weeks. Can’t wait? Then go here to download the beta and give it a whirl!

Summary

Writing SQL by hand can be a pain, whether you’re using Notepad or a visual query tool application like Toad for Data Analysis if you’re not taking advantage of all the features. In a future blog I’ll discuss how to develop your SQL more efficiently by using the visual Query Builder.

10 Things You Must Know about Toad’s Editor

Jeff Smith — Tue, 22 Jul 2008 15:06:00 GMT

The editor window is probably where most users spend a majority of their time. In this blog, I’ll try to highlight the 10 features that will save you the most time when it comes to writing your SQL queries, SQL*Plus scripts, anonymous blocks, and PL/SQL stored procedures. Please note that all of these features are available in the standard edition of Toad for Oracle, version 9.6.

1. SQL Recall

By far one of the most popular features in Toad, but do you know how to take FULL advantage of this feature?

The basics:

Every statement you execute gets recorded
By default we remember the last 100 executed (configurable up to 999)
F8 opens the SQL Statement Recall panel (also available under the View menu)
ALT+PgUp/Dn will cycle through the most/least recently executed statements
You can name your SQL for instant recall using the CTRL+N keystroke

Give your queries a name so you remember why you wrote them in the first place!

2. Code Templates

Ever experience déjà vu while typing out your code? This is your cue to save it as a template for use later. Toad gives you access to several dozen of these out of the box. You can also add your own or customize the existing ones. Use the CTRL+Spacebar keystroke to see the code you no longer have to type out manually.

Remember the code snippet names for instant recall, try typing ‘anon CTRL+Spacebar’ right now!

3. Code Insight (Dot Lookups)

When you want to write a SQL query or make a PL/SQL call, you probably spend a bit of time manually looking up the column names or command arguments required in the Schema Browser. Stop doing this right now! From now on, let Toad figure this out for you automatically in the editor.

The basics:

Schema. – this will pop up a list of all objects in the current schema you may want to type
Schema.Table. – this will pop up a list of columns for a given table. You don’t need to prefix the schema name if the table is in your current schema. Multi-select columns to get more than one, and we’ll comma separate the list for you automatically
Package. – this will pop up a list of procedures, functions, variables, etc for that package.
Stored Procedure( - this will pop up the list of input parameters to make the call. You can type while you reference the list. Entering the ‘)’ character will close the hint.
Toad waits 1.5 seconds after seeing the ‘.’ or ‘(‘ character before popping up the list of items available for that object. This is configurable down to the millisecond for optimal usage.
You can use aliases for your view/table names and the code insight feature will automatically include the alias when bringing back a list of columns.

4. F4 DESC

DESC, or Describe, is a SQL*Plus command that will display the table or view structure, e.g. a list of columns and their datatypes with size and precision. Toad has its own DESC command. You can invoke it immediately for ANY object in the database by typing that object in the editor, putting you cursor on it, and hitting ‘F4’. This is one of the most ‘borrowed’ features in an Oracle IDE. Do you find it curious that it’s always activated with ‘F4’? Imitation is surely flattering, even if it is poorly executed J

You can have multiple F4 dialogs open at a time. You can type away in the editor with the DESC dialogs available to you. Ever working on a query only to find out you need to add a column, update a row, or change the permissions on a table? Don’t go to the Schema Browser anymore, just use ‘F4’ for instant gratification.

Get instant access to any database object with the power of the Schema Browser immediately available.

5. Make/Strip Code

Turn any SQL statement into a Java, C++, Delphi, Perl, etc command in a single button click. Even better, take any embedded SQL statement and strip away the non-SQL syntax for instant execution in the Editor. This is one of the most over-looked features in Toad. No more REGEX search and replace to get your well-crafted SQL statement ready for your Eclipse or Visual Studio application.

Configure Toad to use the programming language of your choice!

Summary

Ok, I know we only covered five things, but my fingers are tired and I need to get back to my ‘real’ job. Look for the next five things you MUST know about Toad’s editor in an upcoming blog. And as always, feel free to share your own tips and questions using the blog comments feedback section.

Read Jeff's next five must know items

Still Testing Your PL/SQL Manually? STOP-RIGHT-NOW.

Jeff Smith — Mon, 30 Jun 2008 14:51:00 GMT

Anyone who develops, supports, or uses PL/SQL at some level also tests the PL/SQL. It may not be a formal testing environment, but there is a time where you will probably ask yourself, “Does this program actually do what it is supposed to?” I think this is something we can all relate to as computer users.

This type of testing probably involves clicking a few buttons, then poking around the database or file system after the fact to make sure what you expect to see exists. There of course are other types of testing that generally happens inside the development team responsible for the code in question. You have developers writing the code and doing their testing before sending it to the QA group for verification, and then you have the QA group that has a very defined and rigid set of functionality tests that ensure the application does what it is supposed to do. These defined tests eventually get converted into Regression scripts and then are ran as needed to ensure any work done on the code after it has been released does not break anything that was previously working.

Ok, so none of this should be news to you, the Oracle enthusiast who is also working with PL/SQL. What may be news to you is that for the past year now Quest has had a technology that can automate this causal to rigid testing methodology for PL/SQL. What’s even more exciting is that in the past release of Toad, this technology has been directly incorporated into your PL/SQL IDE, i.e. Toad for Oracle.

One-button-press testing, courtesy of Quest.

Shenanigans You Say?

Ok, I need to come clean just a bit I suppose. Toad cannot look at your code and automatically compose tests for you on the fly and then just run them and let you know how they went. This would be an awesome technology if it existed, wouldn’t it? So what exactly can Toad do for you here?

What if you just described in plain terms what your program should be doing, say in terms of:

My table(s) should be updated/deleted/inserted appropriately and look like this
My function should return this cursor and/or collection
These OS files should be updated/added/deleted
My program should run in less than X seconds
My program should always raise this exception under these circumstances.

If you take the few minutes to do this, we can turn these expectations into a testing program that can ensure your code is doing what is supposed to be doing today, and will continue to do so for its entire life expectancy.

One other little disclaimer, this technology is actually a stand-alone technology called Quest Code Tester for Oracle. But, if you buy it with Toad, it’s basically half-price, and then you have the instant integration.

So if I were to click the ‘magic button’, after I had made a change to my program I would instantly know that all things are not as expected according to my pre-defined tests.

In about 5 seconds, Toad was able to report back that 1 of our 4 test cases failed. It also was able to show me the actual versus the expected results.

How Does it Work?

When you click the Code Tester button, Toad will open Quest's "integrated testing environment." This is where you will describe your test cases and maintain the test code.

In about 2 minutes I was able to define 5 test cases that actually run through about 20 different scenarios using the random test input data management piece included in the product. Now, as long as I don’t add new features or drastically change the behavior of the program, I can rely on this test suite to let me know if something happens to break my program. If someone inherits my program later and needs to enhance it, they simply need to take an extra minute to enhance the test suite as well.

How Does it REALLY Work?

Have you ever heard the saying ‘Good developers write good programs, but great developers write programs to write good programs for them?’ That’s the essence of what’s happening under the covers hear. Quest has a great developer who had dedicated the last five years or so of his professional career at making this technology available for PL/SQL professionals everywhere.

By taking the inputs and scenarios defined in the test IDE, he can transform that into a PL/SQL test package that automates the outcome checks, the running of the tests, and the evaluation of the results so all you have to worry about is ‘Yes this worked’ or ‘No this did not work and here is why’. When you get the ‘No’ answer, don’t despair, just jump into the debugger and run your scenario manually so you can see which line of code is causing the problem according to your expected set of outcomes.

Why write test code when Quest will do it for you?

Start Using This TODAY

Here’s your official Toad World homework assignment:

Go to our PL/SQL testing community and watch some videos. If it looks as easy as I said it was, then proceed to assignment #2
Download a trial, or if you must the freeware J
Install it and try it out.
Questions? Want to call shenanigans for real? Reply to this Blog or even better yet, talk to Steven here.

Code Insight in 9.6 – Not seeing your Data Dictionary Views?

Jeff Smith — Thu, 22 May 2008 13:48:00 GMT

The Code Insight feature rewrite for version 9.6 is perhaps the most dramatic change as it will affect every Toad user who writes SQL or PL/SQL.

In a nutshell, for the 9.6 release, users can now start at the schema level, and use the ‘Dot Lookup’ feature to see all tables, views, synonyms, snapshots, PL/SQL objects etc. You can then drill down to the column level where possible.

Here’s what it looks like now:

You can configure the delay time for Toad to query the object details in the Options Dialog.

In previous versions of Toad, users could only use the ‘Dot Lookup’ Code Insight feature to pull up column names. Toad continues to be able to resolve aliased objects in the editor.

Using the options control button in the ‘Dot Lookup’ window, you can configure which object types to query. In large databases where the number of Public Synonyms can be quite high, this can have a negative performance impact. For this reason, this object type is DISABLED.

Turning this on can increase the amount of time and memory required for this feature.

If you are working on a database that does not have thousands of Public Synonyms, then you will want to enable this feature. Now you can do cool things like:

Toad supports object aliasing for its ‘Dot Lookup’ feature.

An Additional Performance Tip

Make sure that ‘Cache Code Insight Results’ is enabled. For each user you do a lookup feature for, those entities will be cached into memory. With this disabled, Toad will re-query the data dictionary every time you do a ‘Dot Lookup’ call.

Toad will let you know if one of these options is causing you problems via the Toad Advisor feature (available under the ‘Help’ menu.)

Is Toad running slowly for you? Use the Toad Advisor to discover configuration issues.

Need Help Fixing Tables with Duplicate Values?

Jeff Smith — Thu, 24 Apr 2008 14:45:00 GMT

Despite the best intentions of the DBA or the person who designed the data model, the collection of primary keys, unique indexes, constraints, and triggers are not a foolproof method of keeping duplicate records creeping into your tables and views. Any business rule in the database can be disabled. An even more likely scenario is the business rule changing after the fact. So what’s a person to do when they need to clean up their records?

Cue Toad’s ‘Data Duplicates’ feature.

Simply point to a table, choose the columns you want to use as an identifier, then Toad will find each group of rows with duplicate values. Toad then allows you to delete or modify the records in context on the fly! Let’s take a quick look to see just how easy this can be.

Step 1: Select your table and identifying fields

We’ll work with the HR.EMPLOYEES table and pretend that some silly person decided that a manager could only hire 1 employee per day, and now we need to go fire some people. Silly? Yes, but I bet some of you have seen similar ‘silly’ rules be implemented in the database without much forethought and as a consequence you’ve been stuck doing the cleanup work.

You see on this screen you have the option to treat all columns as identifiers or selected ones. Choose the option that fits your needs.

Once selected, Toad can show you the fields that have duplicates, the number of duplicates, and retrieve the selected rows.

Step 2: Analyze the Results

After selecting the fields on the first tab, simply click on the ‘Duplicate Data (Summary)’ tab. Now Toad shows us the HIRE_DATE || MANAGER_ID matching pairs and a count of each. In this case, our table has 3 duplicate records where these 2 column fields are not unique. So we have 2 people that either has to have to be fired or have their records changed to protect the innocent.

You may wonder, how is Toad finding these records? Let’s look at a simpler example of where we choose the HIRE_DATE as the new unique field. Again, I’m not using real world examples that make sense here, but it does give you a good idea of how the screen works.

Toad found the 7 groups of HIRE_DATES that have duplicate values attached. Or put in another way, Toad found 11 rows that would prevent a UNIQUE constraint being applied to the HIRE_DATE column. Here is the SQL that Toad generated to find these records for you:

Select HIRE_DATE , count(*) as Occurrences
from HR.EMPLOYEES
HAVING count(*) > 1
group by HIRE_DATE;

As you can see, this is a very simple operation that you could do manually. What Toad does here for you is to easily switch back and forth from different tables and different groups of identifying fields to see the data in a very plain and straightforward way. The real value I think comes in Step 3 though.

Step 3: Remove the duplicates

Proceeding to the ‘Duplicate Data ‘Editable’ tab, we can now update or delete each of these rows to ‘trim’ the table down to the state we desire. I could on the fly change the HIRE_DATE or MANAGER_ID for each of the offending records. Instead of figuring out ANOTHER identifying field to use as a limiter in a WHERE clause for a manual DELETE or UPDATE, Toad does the heavy lifting for me by using the ROWID for each of the edits. I can now concentrate on the ‘3^rd’ employee and change her MANAGER_ID on the fly and let Toad worry about how to figure out which record to issue the UPDATE for.

UPDATE HR.EMPLOYEES 
SET
  MANAGER_ID = :MANAGER_ID
WHERE
  ROWID = :Old_ROWID
  MANAGER_ID = 102
  Old_ROWID = 'AAAMhEAAFAAAABYAAH'

What if you only want to see the ‘first’ or ‘last’ duplicate row for each identifying field? If you’ll notice on the toolbar, the first button labeled ‘View/Edit Query’ allows you to specify what rows you want to look at.

We can change the query that Toad issues to display the duplicate records by simply changing the ‘<>’ to a ‘>’ or ‘<’.

If you choose to do so, the button will be highlighted to let you know you have filtered the result set to not display all of the offending records. The concept of ‘First’ and ‘Last’ and how it applies to ROWIDs in a table is generally appropriate, but be aware an older record’s ROWID can change and cause it to appear as a ‘Newer’ record, so beware!

Conclusions

Despite all precautions and best intentions, duplicate records WILL find their way into your database tables.
Finding these can be fairly simple using some straightforward SQL statements.
Toad will write these SQL statements for you and present the findings in a very easy to understand manner.
Once found, Toad also will allow you to manually ‘fix’ each record without worrying about how to distinguish them if you were to write your own INSERT/UPDATE/DELETE statements.

Flood of Coffee Break Byte Videos, Thanks Gretchen!

Jeff Smith — Mon, 31 Mar 2008 14:22:00 GMT

The Toad World visitors may have noticed a recent surge in the number of videos available for viewing in the past few days. Toad World will debut a collection of 12 or so Coffee Break Bytes (video tutorials) to highlight the new features for the 9.6 release due out on April 1.

I think these videos very nicely compliment John’s 9.6 3-part blog series. However, while the written word can be very compelling, watching the videos of the technology in play can have a much larger impact.

So, if you have a spare 5 minutes and want to get a head-start on v9.6 and impress your colleagues, read on! Click the topic title to be taken to the associated Coffee Break Byte immediately.

Toad Toolbar Customization

Toad has about a dozen toolbars and at least 2 different sets of desktops. In addition, it’s possible to customize the menu bars (File, Database, Utilities, Help, etc.) Up until v9.6, actually getting Toad configured to allow for user customization was quite awkward. I once wrote a 8 page white paper just to show how to add back the ‘Execute Current Statement’ button for users upgrading to v9 and who wanted it back. Needless to say, it shouldn’t take much to figure out how to customize a toolbar in a windows application. Version 9.6 operates just as any user would expect in this regard. It will be much more effortless to maintain your toolbar edits as newer versions of Toad are coming out.

Ever wonder why the ‘Customize’ selection is greyed out? No longer a problem for v9.6!

Toad Hierarchical Profiler

Toad has had an interface to Oracle’s DBMS_PROFILER package for a long time now. A great way to find execution bottlenecks in your stored PL/SQL programs, it will show exactly how many times each line of code is executed, for how long, and more. Oracle updated this technology in the 11g RDBMS, calling it a Hierarchical Profiler and delivering it via DBMS_HPROF.

The Hierarchical Profiler extends the power of DBMS_PROFILER by including a much deeper level of information in terms of which programs or which program sections are CALLING the sections of your code that are taking a long time to run. So not only is line 12 the most expensive in total run time, but lines 4-7 in program XYZ is responsible for invoking that code in the first place. It’s also much better at separating SQL and PL/SQL execution times. For a more complete review of this new Oracle technology, please go read this.

Available since v9.5, this information is available directly in the editor while you work on your PL/SQL.

Toad Line Item Profiler

If you aren’t on 11g, then you will need to use the ‘classic’ DBMS_PROFILER interface. Version 9.6 of Toad has made this information MUCH easier to access. In the Profiler tab in the Editor, not only can you see each of your execution runs for your PL/SQL unit, there is now a toolbar button to load up the profiler analysis window where you can graphically interpret the data. This can be much friendlier on the eyes when your program has more than 20 lines of code.

Click on the details tab to load the graphical reports.

Gretchen has many more videos planned. We’ll try to keep you posted so you’re sure not to miss out on anything! If you have any suggestion for video tutorial topics you’d like to see covered, please let us know!

Debugging PL/SQL, now available for everyone!

Jeff Smith — Tue, 25 Mar 2008 18:25:00 GMT

As of April 1, 2008, if you are a current on maintenance and are using at least version 9.6 of Toad for Oracle, you will have complete access to our PL/SQL, Stored Java Source, and SQL*Plus script debugger regardless of your edition of Toad. Since the debugger made its debut in version 6.3 released all the way back in February of 2000, the functionality has been offered as a premium in Toad for Oracle.

Well, a few things have changed in the Oracle IDE marketplace since then. The most significant of these to impact a Toad user is that a PL/SQL debugger – no matter how powerful it is, is now considered a basic feature. Quest has decided that it’s time for ALL Toad users to benefit from our industry-leading debugger. I’d like to take this opportunity to give Toad’s debugger a quick ‘101 tutorial’ treatment. Whether you have been using the debugger for several years, or you are just getting around to looking into it, here is a chance for you to fine tune your PL/SQL development skills.

Click here to download my tech brief, "Debugging PL/SQL"
(Microsoft Word Doc, 323kb)

Also, be sure to check out the PL/SQL Debugging Coffee Break Byte to learn more about PL/SQL Debugging features in Toad.

Excel and Oracle – 2 Choices for the Query Writer

Jeff Smith — Thu, 06 Mar 2008 20:46:00 GMT

If you’re a loyal reader, you may remember a blog I published WAAAAAAAY back in July of last year. A few things have changed since then. Users now have 2 different Toad tools to help them get data from Excel merged into the data they already have access to in the Oracle database.

A Brief Problem Description

You have a boat-load of information in Oracle
You have additional information in one or more spreadsheets
You need to create a report/query that pulls data in from one or more sources

So what’s changed since July of last year? Well, 2 significant things have or are about to occur:

Toad for Data Analysis has been released and will allow you to query Excel and Oracle via a single SQL statement.
Toad for Oracle v9.6 will now allow you to automate Excel to Oracle data loads.

You now have to make a decision based on your needs at the moment. Do you need to make the data in those spreadsheets permanently available to the superior data management technology that Oracle provides for future use, or is it likely to be used for only a query or two and safe to leave in Excel?

A Possible Solution

Scenario 1: Let’s get the data into the database where it belongs

Toad’s "Import Table Data" feature available off of the ‘Database>> Import’ menu fly-out has been around for quite some time. It has undergone a significant update for version 9.6. Does everyone remember seeing our new automation technology added in Toad v9.1 called Action Recall? This architecture allows Toad to record what you are doing in a macro of sorts that you can then replay ad hoc on demand or use to schedule to run on an automated basis.

With this technology in place for the Import Table Data Wizard, we were able to remove a lot of the confusing pieces. For starters, you don’t need to manually save and then re-open your settings files. This is done automatically for you now as an Action Recall item. Even better, the table truncation, trigger and constraint disabling can all be included in the scripted process so you can much more easily get your tables updated with as little human interaction as possible.

To use this feature, do the following:

Load the Screen
Choose your Table to do the import (if the table doesn’t exist yet, make sure to read this.

Notice the new ‘lightning bolt’ button in the Title Bar – this will allow you to define this work as an explicit Toad Action.
Now step through the wizard dialogs and fill our your information

The wizard has been re-done to accommodate the nature of scheduling this kind of database activity, notice the ‘Before Import’ and ‘After Import’ sections
Either RUN the job, or click on the new ‘Action’ Button in the title bar of the window to explicitly save the information as a Toad macro
If you just run the import, you can then see your Toad Action show up in the ‘Vault’ tab on the ‘Action Palette’ (available off the View menu.)

I saved mine, but if I had just ran the wizard normally, the action would have still been recorded and saved in the ‘Vault’ tab.
You can now run the import on demand by hitting the play button in the Action Palette or you can schedule it to run and add an e-mail notification using it’s built-in scheduling interface. No more need to use the dreaded Toad.exe command-line interface!

Scenario 2: I need a quick-and-dirty query, leave the Excel stuff in Excel

Toad for Data Analysis’s latest release now allows an Oracle user to query data from Excel and Oracle in the same SQL statement! This means if you ‘draw’ your query, then you can get the data out without having to worry about how to get the data into Oracle first. Let’s take a quick look.

Steps for Querying Oracle and Excel together:

Open Toad for Data Analysis
Connect to your spreadsheet(s) and Oracle database(s)
Select the ‘table’ (Excel tables in TDA represent Named Regions in your spreadsheet)
Mouse-Right-Click > Send to Query Builder
Repeat as necessary until you have all of your spreadsheet ‘tables’ in the Query Builder
You’ll notice that the word ‘Heterogeneous Query’ is watermarked in the screen, this indicates you are querying between multiple data sources
Do the same for Oracle, e.g. Connect to Oracle, select table, mouse-right-click > Send to Query Builder
‘Draw’ the links from the excel source to the oracle tables so the query will know which columns to join on
1. In this case I have chosen to join DPT_NAME in Excel to DEPARTMENT_ID in Oracle
2. The default join is an EQUALITY one
3. I want to see what’s different, so I double-click on the join to make the necessary change
  
  Notice instead of saying LEFT OUTER, I can specify the data I want returned in plain English
Select the columns you want included (the default is * for all tables)
Add your WHERE conditions if necessary
Run the Query!

Now I can verify the results and publish to the format I want! I can also make changes to my query and re-run it to see the query results side-by-side for comparison.

Now, if I save this Query and add it to my Toad Project, I can open or run this whenever I want in the future assuming my spreadsheet is still around.

This technology can be applied to just Oracle, too. Let’s assume you have 2 Oracle databases that share information and you need to query both. Before, this would have required a DB_LINK to be created, maintained, and granted. Now, instead of pestering your DBA, you can simply use the heterogeneous query feature in Toad for Data Analysis to get your reports ready.

We have seen upwards of 7 different data sources successfully included in a single query, so make sure to give it a try!

Conclusion

Hopefully copying & pasting data from Toad grids into Excel and then pulling your hair out will be a thing of the past. Now you can take advantage of your knowledge of SQL, the power of Oracle, and flexibility of Toad to answer your pressing business questions.

Toad for Data Analysis v1.1.1 was released a few weeks ago and added the ability to do heterogeneous queries with Excel. Older versions would allow you to join Oracle and Access, so if you need to use Excel, make sure to upgrade to the latest and greatest.

Toad for Oracle v9.6 is scheduled to be released at the end of March, so keep your eyes open!

A Complete Solution for Oracle DBAs

Jeff Smith — Mon, 21 Jan 2008 17:08:00 GMT

How often are you offered less work or fewer responsibilities? Rarely does that ever happen, and if it does, it could be a bad sign! Well, as a database administrator, your job gets more and more complex every day even as RDBMS vendors keep adding ‘automated management’ features. On top of all your day-to-day tasks and preventive maintenance you perform, your most pressing tasks are playing firefighter with the proverbial fires in the database.

The number of problems you face is only matched by the number of tools and ‘solutions’ to choose from that offer to address these problems. Of course many of you have already found Toad. I’m going to guess that you use Toad in concert with the utilities that Oracle provides to help you accomplish your job. Whether you choose to go exclusive 3^rd party, exclusive vendor toolset, or a mishmash, the important thing to remember is that you feel comfortable and have a tool at hand to handle every scenario when it rears its ugly head.

Quest has just released a new exciting package for Oracle Database Administrators, and I wanted to take a few minutes (I’m a fast typer!) to make sure you realize what you now have available: a seat-based, low priced toolset that helps you Administer, Diagnose, and Tune your Oracle 7 – 11g databases.

Here are the Highlights

A much improved Database Administration module for Toad with version 9.6
For the first time ever, Spotlight for Oracle available for diagnosing database performance against unlimited number of databases for one low price
A modeling and reporting tool for reverse-engineers, HTML reporting, and creating and managing physical and logical data models
A database load testing tool to answer your pressing questions about database reliability and performance

Toad 9.6

Bert Scalzo has already blogged a bit about this topic. Toad 9.6 will for the first time have a screen that can show you your database objects for multiple databases AND allow you to drill down to the specific schema objects for each database. There are a few other big improvements coming and John will be talking about them shortly. One last thing to mention about Toad is the incredibly useful and new ‘Security’ section for the Database Health Check Be sure to check this out if you’re already participating in the Beta program.

Spotlight on Oracle

Spotlight is one of the most well known products here at Quest, and for good reason! As a DBA you need to be able to detect and diagnose problems with your database as fast as possible to minimize downtime and delays. Now available with Toad, you can use it to watch all of your production databases, have it send you email alerts, replay activity to see what happened while you were away, and much more. Each ‘pretty’ visual indicator in the product can impart a ton of information at a quick glance. The tool is able to observe your database activity to set a series of internal benchmarks. When this activity increases or decreases, you get an instant visual cue via the product. When a problem is detected, you can immediately drill down into the warning and see the raw data causing the problem, e.g. a database lock caused by a rogue process belonging to a program running when it shouldn’t be. In addition, we provide a ton of Oracle specific documentation and notes so you can learn more about the problem and how to address it now and prevent it in the future.

Diagnose issues on multiple instances with a simple click!

I used to live in the command-line world, and I had my set of scripts that kept me warm at night. I still have and use a lot of these, but I’m also glad I don’t have to work in the ‘real world’ anymore. My scripts we’re often invalidated with every database upgrade, and often times didn’t work the way I intended them to. Of course Toad helped me a lot by letting me organize them and run them fairly quickly, but I can’t imagine having to do so for upwards of 20 production databases versus the 1-2 I had to deal with back then. Spotlight lets me concentrate on the task at hand and get quick and easy access to the data I need to make decisions.

Perhaps the best thing going for Spotlight is the resources and brains Quest is investing in the technology. Guy Harrison has been doing database optimization and tuning in the Oracle space for the last 15 years. You can read his dedicated series here on ToadWorld!

Toad Data Modeler

Ever inherit a poorly documented database or application? I’m sure that’s RARELY the case, but if it ever happens to you, it’s very handy to have a tool that can completely reverse engineer the database into a physical data model and publish it to HTML so it can be viewed by you or your team.

What’s that? You actually get a vote when it comes to designing the data model for your in-house applications? That’s great! If you don’t have access to a modeling tool, and have been resorting to use MS Visio or a whiteboard, it’s time to take advantage of Toad Data Modeler. You can:

Define your entities and their relationships LOGICALLY
Transform that to a Physical data model
Generate the DDL to create the objects in the database
Make changes to the model and have the alter script generated for you automatically
Save your revisions to a repository
Compare versions for instant verification of changes made
Generate rich and easily read HTML reports

Perfect WYSIWYG HTML reports in a flash!

Benchmark Factory for Oracle

This tool allows you to make apples-to-apples comparisons to changes in your database setup. Want to patch to the latest version of 10gR2? Run a test prior to and after the upgrade and know right away what to expect in terms of response time and maximum user load on the system before your performance SLAs are invalidated.

Establishing the robustness of your database is as easy as:

Defining the workload
1. Actual production database workload replay with Trace Files
2. Predefined industry standard benchmark tests (TPC)
3. Your own custom scripts
4. Individual SQL statements or PL/SQL stored procedures
Defining the number of users to run (1 – tens of thousands)
Determining how long to run the test (static or goal-based)
Viewing your results
Making a proposed change to the system (database, hardware, network, application)
Running the test again
Comparing the results and making an informed decision!

Quest SQL Optimizer for Oracle

Have you found a problematic SQL statement adversely affecting performance in your Oracle database? If you’re using the tools mentioned above, then you should already be aware that Quest can identify these queries from scanning your database objects and development source code BEFORE it reaches production. If you are suffering in a production environment, then we can also analyze your SGA. Unfortunately finding the problem is only the first step. You must now fix the problem with minimal impact to the rest of the system as soon as possible.

Quest SQL Optimizer for Oracle can accept tuning jobs from Toad and Spotlight with a single click. Sit back and relax while it tackles all of your problematic SQL with its SQL Tuning Queue. We’ll let you know which statements were able to be re-written with better execution plans, but more importantly with better execution times. We can also analyze your existing indexes and propose replacements or addition ones.

Adding indexes is never a trivial task for any database. We can create multiple indexes, test the affected SQL, then come back with the index set that makes the most sense for your scenario. You can publish the index with confidence using our Impact Analysis feature. We tell you WHICH database objects are positively, negatively, or NOT AT ALL impacted by your proposed database plan change. One last thing – don’t forget to update your data model with Toad Data Modeler!

We find the better SQL scenario for you and then prove it won’t break anything else!

Join the Quest Community today!

When you invest in a Quest solution, you get much more than the products listed above. You also invest in Quest’s 20+ years long experience in the database tools market, its industry experts, its 95% Customer Satisfaction-rated Support organization, and the thousands of fellow users. When you encounter a problem, you will NOT be alone. Each product in the Toad DBA Suite for Oracle enjoys its own dedicated community forum where users can interact with the Quest developers and fellow users.

Are you interested in learning more about this solution? Please visit our website to browse the product literature, attend a public webcast, or download a trial.

Toad for Data Analysis – Tips ‘N’ Tricks

Jeff Smith — Fri, 11 Jan 2008 20:41:00 GMT

One might be tempted to think that with such a young tool like Toad for Data Analysis (TDA), that it would be a bit early to start trumpeting its advanced features and ‘hidden’ productivity boosters. In this case, one would be very wrong! Although Toad for Data Analysis is only up to release 1.1, it is built on top of Toad for SQL Server and Toad for DB2’s award winning platform. The development team has also included a couple of powerful new widgets just for TDA.

A Few of My Favorite Things…in No Particular Order

Searchable Code Snippets

TDA comes with references to ALL of the database provided functions you can use when building your own SQL queries – regardless of database provider. To access this list, go to View – Code Snippets. Three things to try:

Do a keyword search. It will then filter the list and only show you the entries of interest.
Try the online reference lookups to see the vendor supplied documentation on that particular function call.
Double-Mouse-Click the entry to have the code appended to your Editor for immediate use.

You can dock the Code Snippets panel to your place of choice and un-pin it so it remains out of your way when you don’t need it!

Dynamic Date Filtering for your Queries

I want to create a report that queries the database for employees for a certain list of departments and were also hired in the last month. I want to schedule this report to run every 3^rd Wednesday. Believe it or not, I can do all of this with TDA with a few point-and-clicks.

Step 1: Open the Query Builder

Step 2: Add the tables that contain the data you need.

Step 3: For the ‘last month’ filter, you COULD write a for SYSDATE – 30, or you can use TDA’s supplied Date Filters. Just click on the ellipsis button on your DATE column, and you’ll see this:

TDA will take your selection here and put a custom TDA hint into your query:

SELECT ...
 WHERE ("EMP".HIREDATE = '' /*Current week*/ )
      AND ("DEPT".DEPTNO ...

When TDA reads this hint, it will dynamically replace that text with the appropriate SQL call.

View Tables and their Related Data by Writing ZERO SQL

The Master-Detail browser is probably one of the most well received features when I demonstrate our technology to the Analyst audience. I can simply add a few related tables to my modeling area, and Toad will automatically find the relationships and offer to display the data as it relates to each of the tables.

Let’s look at a small example with Oracle’s supplied HR schema.

Step 1: Pull up the proper schema in the Object Palette.

Step 2: Send the tables to the Master Detail Browser

It’s as simple as Multi-selecting your tables, and Mouse-Right-Clicking.

Step 3: View the relationships.

Step 4: If it looks OK, press the ‘Play’ button and start browsing!

I select a region, and then see all countries for that region. I can then select an address, and then browse the departments for just that address!

Do You Have Your Own Tips?

Please contribute to our TDA Community or post them as replies to this blog so that others can learn from your experience!

More Toys for DBAs

Jeff Smith — Wed, 12 Dec 2007 17:38:00 GMT

I’ve been promising to deliver a write-up on Toad’s new support for Statspack, and I’ve finally gotten around to doing it! Statspack has been around since the 8i release of Oracle. If you are unfamiliar with the technology, please read this. If you’re already a Statspack user and would like to see how Toad can help you take advantage of it, then this blog is for you.

Wait a sec, Statspack is sooooooo yesterday!

Many of you may be wondering why I am crowing about Toad’s support for Statspack when Oracle pretty much just replaced it with ADDM|AWR in the 10g Release. I have 2 pretty compelling reasons:

1. Not everyone is on Oracle 10g. Don’t laugh please. Many people will not leave 9i until Oracle quits selling extended support on it. This is a blog-worthy topic all by itself.

2. Oracle’s new automated monitoring and workload repository for 10g comes with a VERY big string. It is not included in the base licensing, whereas Statspack analysis always has been and will probably continue to be FREE.

So, you can continue to take advantage of the Statspack technology with no risk, or you can evaluate the benefit of licensing the newer AWR technology. The nice thing is that Toad’s DBA Module will empower you regardless of the direction you choose to follow.

How to start using Statspack with Toad

A few prerequisites:
1. You need Toad for Oracle version 9.5
2. You need the DBA module
3. The Statspack objects need to be installed
4. You need to create a job to collect the statistics on a regular basis so they can be queried.

If you have met objectives 1-2 above, you can see the Statspack feature here:

Once you go to open it, Toad will query for the Statspack objects. If it cannot find them, it will prompt you to run /rdms/admin/spcreate.sql as SYS while connected to the database server directly.

It will then offer to create this job for you:

Oracle’s AWR technology works in a similar way. It collects statistics by default every hour. These ‘snapshots’ can then be queried. The Statspack snapshots can be directly queried from the ‘PERFSTAT’ tables. Or, you can use Toad to generate the reports for you.

The Fun Stuff

Ok, enough about how to set it up, let’s walk through the screen and then a couple of more useful features the developer decided to throw in for your benefit.

You basically have a three step process for this screen:

1. Decide which period of time you want to report on
2. Decide which information you want included in the reports
3. View the results in the graphs

Choosing Your Time Period

When you open the screen for the first time, you may need to hit the ‘Refresh Snapshots” button. This will populate the list of snapshots you have available to query against. By selecting these, you are telling Toad which time period to include the results for. My favorite right-hand-mouse option is the ‘Check Last 24 Hours of Snapshots” which allows me to see what’s happened in the past day only. If you have JUST enabled Statspack in your database, you can use the ‘Find Collection Job in Schema Browser’ option to manually run the job a few times so you can see some snapshots to choose from.

You can also use the ‘options’ button to change the Statspack setup, including what thresholds and level of diagnostics to use. You can additionally mouse-right-click in the header (see the picture below to see what I am talking about) to add more detail information on the snapshot list. This allows you to know EXACTLY what snapshots you are looking at.

Choosing the Information to be Displayed

There are a few categories you can choose from:
1. Operating System
2. Database
3. IO
4. Session Activity
5. Ratios
6. Wait Events

These are all included as part of Statspack. You can also choose to create your own category and add any metric you want assigned to a custom chart to display that information.

As you select an item in this tree, you will see the appropriate graph show up on the right. If you already have a snapshot of time checked, it will query the data right away and populate the graph. If you check a snapshot afterwards, you can use the ‘Refresh RHS’ button to update the graphs to reflect what you have selected on the left.

View the Results

Depending on your monitor size and screen resolution, you may want to configure the graphs to show as few as 1 or as much as 3 graphs per row. You can tweak these settings so that the graphs are large enough to be readable, especially if they have a lot of data point lines.

Something you may not see right away – especially if you haven’t learned how right-mouse-click happy Toad is – is the ability to view a graph in a full-screen mode.

Learn to use the Mouse in Toad!

Once you have a full screen display of a graph, there are a few things you can do with the data contained.

If your graph contains a LOT of information and it is hard to see what is what, click on an item in the legend, and that line in the graph will be bolded, making it easier to distinguish!

You can also send the chart itself to Excel, or copy the data behind the graph to Excel so you can work the raw data on your own.

What’s Coming Up for Version 9.6

Toad’s DBA Module already has an interface that exposes the ADDM/ARW reports that Oracle provides. For version 9.6, we are rolling out an ‘AWR Browser’ which will work exactly like the Statspack feature documented above, but that will query the AWR snapshot repository. This way if you are happy with the Statspack information but want to take advantage of the AWR, you can have the best of both worlds.

If you want to see this feature before it’s commercially released, and/or have a say on what features are added or enhanced in the next version of Toad, just join the Beta Program!

The AWR Browser in version 9.6

Upgrading to 9.5? Read this first!

Jeff Smith — Thu, 29 Nov 2007 22:30:00 GMT

I was initially planning on writing up a review of the new Statspack support in Toad version 9.5 for my next blog, but I wanted to make sure all of our Toad users were aware of the new installation process first!

There are a few things you need to know before you go to upgrade your copy of Toad. Let’s go over them very quickly so you can start using the latest and greatest copy of your favorite software program.

Choices, choices, choices

You now have your choice of 6 different installers you can download from Quest. Let’s figure out which one is the best for you.

1. Commercial Installer
Download this one if you have Toad Xpert, Toad Professional, or if you have DBA Module. It also includes the .NET engine which is required for the Quest SQL Optimizer – not Toad.

2. Commercial Installer – w/o .NET
Same as choice #1, but does not include the .NET engine. If you already have the 2.0 version of this, you will be fine.

3. Development Suite
This bundle includes Toad Xpert plus Quest Code Tester for Oracle and Benchmark Factory for Databases.

4. Installer
Download this if you have Toad Standard.

5. Read Only Installer
Download this if you want to distribute a version of Toad that is HARD-CODED to only allow Read Only access to your Oracle databases.

6. Silent Installation Utility
If you want to push the Toad MSI silently across your organization, you need to download this utility to get the standalone Toad MSI out of choices 1-5.

First Time User, or Upgrading?

If you are installing Toad for the first time, then you can pretty much click through the screens and take the default options. Once you’re finished, you’ll need to start Toad and input your license key.

If you are upgrading, or if you are installing version 9.5 and you want to KEEP your older, existing version of Toad – PLEASE CONTINUE READING.

The MSI prompts you for a installation point (2^nd step, “Install Location”) that will be used as a base install folder for all of the products included in the installer, which could be as many as this:

1. Toad^® for Oracle*

2. Benchmark Factory^® for Oracle

3. Knowledge Xpert for PL/SQL

4. Knowledge Xpert for Oracle Administration

5. Quest^® Code Tester for Oracle

6. Quest SQL Optimizer for Oracle

* However, if you already have a copy of Toad installed on your machine, the MSI will detect it and automatically assign THAT FOLDER for the location for 9.5 when it is installed. If you want to keep your older copy of Toad and install version 9.5 into a new folder, please pay attention to the next step.

On the ‘Product Selection” step, make sure to click on the “Update” red hyperlinked text. This will show you the exact type of installation (Local, Network, Citrix) you are doing, AND it shows you the Installation Directory!

Since I already had version 9.1 installed, it by default will try to upgrade that copy of Toad. This is probably OK for most users, but it is something you should be aware of, especially since this COULD conflict with what you supplied for “Install Location” dialogue.

I generally like to keep a copy of every release of Toad ever put out, so this is important to me. For other users I generally recommend installing the new version of Toad beside their current version to evaluate it, and if they decide they like it, to then upgrade their older copy so they can keep their existing options and settings.

Don’t Forget to Read the Documentation!

How many of us techies read the instructions or user’s manuals for our new electronic toys or even our cars? I don’t. Well, I don’t until I run into something I can’t figure out. The Toad team puts SO MUCH into each version of Toad that you should really spend at least 5 minutes reading the Release Notes. This will show you what’s changed, what’s new, and what’s fixed for the latest version.

You’ll want to go to the Documentation section in Quest SupportLink to see all of the Toad documentation available. From here you can read a much more in-depth discussion of how to use the MSI, how to use the new Toad Policy Server, and get the product Getting Started and User’s Guides.

Questions I hear over and over and over…

Jeff Smith — Wed, 21 Nov 2007 05:38:00 GMT

I have worn many different hats here at Quest, and recently put on another in the Sales organization. Regardless of what my official title has been, talking to our Toad customers has always been one of the most rewarding aspects of my career. I decided a long time ago that I would do all I could to master Toad and share my findings with others. I hope to never stop learning or sharing!

One of the more intense experiences is probably working a Toad booth at the Oracle Open World event. I probably spoke to about 200 hundred people over 4 days this past week. Between delivering product demonstrations, answering quick questions, and everything else you can imagine, my frequently asked questions boiled down to the following:

Free tools versus commercial 3^rd party – please discuss why I should invest in Toad.
Great question. First of all, I doubt you would just take my word for it, but I am more than happy to share my experiences with you and we can take a look at how Toad (and our other tools) can help augment your experience with Oracle. I still use notepad everyday, but I couldn’t live without Word. Let me show you a few examples of how this extends to SQL*Plus and a tool like Toad…

Why did you change the way Toad works in the area of XYZ?
One of Toad’s biggest assets is the size of its user base. All users benefit from having the collective input and 10+ years of influence over the development of the product. Of course one of the inherent challenges is being able to create a Toad that is all things to all people. The Toad team wants to deliver a tool that gets out of the way and makes your professional life as productive as possible. That being said, something done to address a knock on Toad’s sharp learning curve for new users, e.g. combining the SQL and Procedure editor windows, inevitably causes consternation for a large percentage of the long term users. The question is one of action versus inertia: the dilemma we struggle with is the decision to do nothing to the product or to take a closer step to perfection with each release. The Toad road may be bumpy at times, but we still think it’s the best way out there!

Quest depends on the feedback of our customers. Tell Support what you think and be sure to report your problems. Join our user communities and take part in our beta programs. Read the release notes and help – it’s not just for noobs.

So what’s in the new version of Toad?
Wow, well that depends. What do you use the tool for, and what version of Toad are you on today? If you are a DBA, then I would make sure you are on version 9.5 so you can take advantage of our support for AWR and Statspack. If you are a developer, then you’ll want to take a look at our new 11g Profiler support and the latest Quest SQL Optimizer for your problematic SQL statements. I bet you have been using Toad for years though and have never noticed our Project Manager, Master Detail Browser, or Data Subset Wizards though. Go check those out when you get a chance!

Don’t miss the cool stuff just underneath the surface!

What is Toad World, and why should I care about it?
10+ years of product history + a million or so users + FAQs and Tips’N’Tricks = a plethora of information available to share with you! Toad World is the mechanism that allows us to share en masse with our loyal users everything we know about Toad. It’s free. It gives you the keys to the toad castle. You can read it when you have time, or choose to receive our monthly newsletter. Don’t allow yourself to get overwhelmed by Toad when there is so much help out there for just that is just a click away.

What is the deal with all of the different Toads?
In the beginning, there was T.O.A.D., and it was good. It was your Swiss army knife for Oracle, and was the coolest thing since sliced bread for developers and DBAs. As it got to be more popular, it was made available for people who work with MySQL, SQL Server, and DB2. Now you can get a best-of-breed IDE for each of the major RDBMS vendors. They are separate products, and are licensed separately (MySQL edition is free!).

We’ve also recently made 2 additions to the Toad family: Toad for Data Analysis (TDA) and Toad Data Modeler (TDM). TDA is for the non-developer/DBA who wants the power of Toad to work with their data but without the complexity of a full-borne IDE. TDM is a modeling tool that allows you to build logical and physical ERDs for most of your major RDMBS platforms, build visual reports, and reverse-engineer your existing databases. These are sold separately from Toad, and have multi-RDBMS connectivity and support at a single price point.

How does your Tuning technology interact with the Oracle Optimizer?
This is a question deserving its own blog, but here is the $2 answer: Quest SQL Optimizer analyzes a SQL statement based on its Execution Plan and advises you on whether it SHOULD be tuned or not. If you choose to tune it, then the program finds for you all of the equivalent SQL statements that have unique execution plans and return the EXACT same result set. We then execute these re-writes for you and let you know if we found one that works faster than your original problematic one. This only reflects about 10% of the functionality offered by this technology, but is generally seen as the primary feature of the product. Here is the question you should be asking yourself, “How much time do you spend trying to make your SQL run faster?” If you’re not comfortable with your answer, then take a look at our automated tuning solution.

Coming Soon…
An in-depth look at Statspack vs. AWR for our DBA users!

Tuning – Detection, Diagnosis, and Prevention

Jeff Smith — Fri, 26 Oct 2007 16:14:00 GMT

If you frequent Toad^® World, then you are probably aware of the fact that Quest has a technology that offers to automatically tune your poorly performing SQL statements. When I speak to customer or prospective clients, it is generally received extremely well. Why is that? Of ALL the problems that database professionals have in common, the biggest pain everyone can relate to is a SQL statement that just doesn’t run fast enough.

What I want to talk about today is not how to use the Quest SQL Optimizer to fix the problem, but rather:
1. How you can identify bad SQL before they cause problems
2. How to develop SQL to run fast out of the box
3. How to find bottlenecks in your code or database if it has slipped into production.

When is the WORST time to identify a bad SQL?

I asked this question yesterday, and a DBA replied “At 3AM!” I couldn’t help but chuckle, as I have been there many times before. What I had in mind was actually.

The worst time to identify a bug or performance problem is when a customer points it out to you.

It is EXTREMELY expensive to fix a problem when it’s already reached production. Even if you don’t have to pay your DBA comp time for waking up at 3AM, if this happens often enough I guarantee you will need to hire another production level DBA to replace your disgruntled employees. Imagine how expensive that is!

So how can we identify these bad SQL statements before they reach the customers? Toad has a feature called CodeXpert which offers the ability to automate code reviews for your PL/SQL and embedded SQL statements. If you point CodeXpert to your source code and/or database objects, it will scan for and identify your complex and problematic SQL for you. If you are doing this on a regular basis in your development cycle, you can address these issues while the developers are actively working on a project rather than come back to it later when the developer is working on something else.

Proactively scan for bad SQL statements before they make it to QA or Production!

How can I develop FAST SQL out of the box?

Toad is popular because it makes it extremely easy to build extremely complex SQL statements in a short amount of time. However, every time you hard code a SQL statement into your application or report, you could be inserting a time-bomb. One popular study cites that 80% of database application performance problems are a direct result of bad SQL statements. I ask customers every day if they find this to be true in the real world and I have yet to run into someone who has a dramatically different opinion.

So, how do we get people to write GOOD SQL? One of the challenges that we have little control over is that people who know little about relational databases or SQL are often placed in a position to write the query statements for their program. If you are a Java developer who needs to query the database, all you care about is if you get the right data back. If it runs slow, that’s the DBA’s problem! I’m not picking on Java developers – this is common for all programming disciplines.

When you write a Query statement in Toad, Toad is shouting to make it better for you right there and then. Listen to the Toad!

Whether you are writing queries ad hoc or are using the Query Builder, tune that
query so it runs correctly and runs fast!

Not so lucky? Already facing poorly performing applications? What do I do?!?
It’s easy for me to say, “Hey, you should be developing really good SQL queries out of the box so you don’t have to worry about them later.” But in the real world, this is frequently impossible to achieve 100% of the time. As a DBA, you are a part-time fire fighter. When something bad comes up, it’s your job to fix it. So how can Toad help you find the problem?

Perhaps you have an application session that’s hogging application resources. Or you have a call in from an internal user that is having a problem with their program running. “It’s hung!!!” – How many times have you heard this?

Toad’s Session Browser can help. Open it, and view the queries by program. Then you can click through them and peruse the current SQL that is being executed, check the execution plans, and send to the SQL Optimizer right away for and immediate fix.

Program not running fast enough? Maybe it’s the SQL?

Maybe your system is being pegged and you’re not sure which application is causing the problem. Or maybe you don’t have 3 sessions in your system like I do. Maybe you have 3,000 sessions and you need a quick way to figure out which one is the culprit! Toad’s Top Session Finder will help you out!

Which program is causing the most network traffic? Find out, and then
navigate to the Session Browser for further investigation.

Have a Stored Procedure not running so fast lately?

Toad gives you two options for diagnosis. You can look at the theoretical or the actual. CodeXpert gives you the theoretical analysis. For a look at how your code is actually performing when executed, you can do an execution profile.
Step1: Turn on the ‘Stopwatch’. It’s on the Toad toolbar.
Step2: Execute your stored procedure using the Editor or Schema Browser.
Step3: Open the Profiler Analysis window.
Step4: Look for the bottleneck. Which code line took the longest to run?

Takeaways
1. No one likes answering a beeper at 3AM.
2. Poorly written SQL can cripple your application’s performance.
3. Toad makes it easy to identify bad SQL in development.
4. Toad makes it easy to identify bad SQL in production.
5. Toad makes it easy to fix bad SQL not matter where it is.

A Sneak Peak

Jeff Smith — Mon, 15 Oct 2007 21:45:00 GMT

They say time flies when you’re having fun, and it seems like 2007 has gone by at a record pace. 2007 has seen quite a few exciting developments in the ‘Toad World’ arena. In addition to Toad World itself, we have seen a new unit testing tool for PL/SQL and a version of Toad made just for analysts released. In addition we’ve seen the industry leading Toad for Oracle, Toad for SQL Server, and Toad for DB2 products enhanced with new versions and new capabilities in the last few months.

The B-I-G event it seems in the Oracle spectrum each year is Oracle Open World. This year’s event seems to continue that trend with a big blowout series of talks, presentations, product debuts, and lounging in San Francisco scheduled for next month. At the same time, Quest will be putting out new versions of the Quest^® SQL Optimizer for Oracle, Toad^® for Oracle, Toad for Data Analysis, and Benchmark Factory^® for Databases. Let’s take a quick peak at what each has in the works.

Benchmark Factory for Databases, version 5.5
This award-winning tool has been helping IT professionals stress test their technology for the past decade. Version 5.5 will be plugged into the Quest SQL Optimizer so you can automatically stress test your batch optimized tuning scenario re-writes. In addition you will see:

an optimized TPC-C test for SQL Server
an Oracle repository option to store your benchmark runs and reports
enhanced SQL Trace file replay for Oracle, including date/timestamp bind variable support and better dependent SQL processing

Quest SQL Optimizer for Oracle, version 7.2
Quest’s SQL Tuning solution saw a major leap forward with the 7.0 release this year. It introduced the concept of ‘batch tuning’ to our Oracle users. Instead of manually tuning problematic SQL statements one at a time, users now have the option of creating a job that automatically scans multiple objects for SQL tuning candidates, automatically tuning them in the background, and viewing the ‘winning’ queries re-writes. Version 7.2 will be fully integrated within the Toad for Oracle version 9.5 Xpert and Development Suite editions. In addition you will see:

A Job Summary Report – automatically see the performance improvement and overall activity for all tuning jobs
SGA Inspector – option to automatically tune all SQL found in the SGA
Batch Optimizer Integration with Benchmark Factory for Oracle – automatically validate your query re-write candidates under varying load conditions

Toad for Data Analysis, version 1.1
The first Toad offering that allowed multiple connections to multiple database platforms in a single IDE, Toad for Data Analysis has been one of the more talked about product releases at Quest in recent memory. A frequent request from our users is scheduled to be implemented in version 1.1: the ability to simultaneously query from different data sources. Do you need to upload data from Excel to Oracle? Wouldn’t it be nice to query ‘SELECT * FROM ORACLE’ MINUS ‘SELECT * FROM EXCEL’ to see exactly what data needed to come over? And then take the results and save as INSERT statements so you can run a ‘migration’ script on the fly? You can take a sneak peak now by joining the community and trying out the beta. In addition to the heterogeneous database join support, version 1.1 will also introduce the following:

FROM clause Sub Query support in the Query Builder
Date Ranges – where conditions on columns of type ‘date’ will now activate a new ‘Date Range’ tab that will allow you to build date filters super quick

Version 1.1’s Query Builder Date Range support for Where Clauses will be
extremely useful when automating and scheduling reports as it will remove the need to hardcode your dates.

Toad for Oracle, version 9.5
Summing up all of the changes and new features for a Toad release can be quite a challenge. I’m not even going to try to do that in this forum, but I will share a few of my favorites:

1. Schema Browser Detail view for no objects selected shows ALL object details

2. StatsPack browser for DBA Module users, supports 8i and higher
3. Underlying parser updated to support newer 10g syntax – formatting support to follow
4. 11g connect support, and a decent smattering of 11g feature support including the new 11g partitioning and subpartitioning types AND virtual columns in Create/Alter Table wizards
5. Hierarchical PL/SQL Profiling is available for 11g database connections
6. Ability to save a file open in the editor to a FTP location
7. Stronger integration with Benchmark Factory, Quest SQL Optimizer and Quest Code Tester for Oracle

For a more thorough walk through of Toad’s new features for version 9.5, stay tuned to the Yahoo! Discussion board.

Speaking of Quest Code Tester…
No new release for Open World, but we did put out an EXTREMELY nice .1 patch for version 1.6 last week. If you’ve taken a look at Code Tester before and found its interface confusing or lacking for non-PL/SQL experts, I HIGHLY recommend giving it another shot. I was visiting a customer last week and they were thrilled of the prospect of no longer doing code reviews of their test code and eliminating 80%+ of their time dedicated to testing in development because of how completely Code Tester anticipated and transformed their testing needs and policies.

A Quick Note regarding product releases:
The contents of this blog should not be taken as a guarantee of what products will be released when and with what features. Product releases and features are likely to change without advance notice.

Team Coding???

Jeff Smith — Wed, 03 Oct 2007 21:01:00 GMT

Every software product has a feature or widget that is extremely powerful, but finds a way to confuse more people than it enlightens. That is not to say that the feature is poorly designed or implemented, but who can really say they understand and use 100% of an item’s potential?

A few of my personal examples would be Office macros, regular expressions in vi, or collections in Oracle’s PL/SQL language. For Toad^®, it seems that the one universal feature that eludes most users is the Team Coding feature. To be honest, it’s not that I am not smart enough to force my fingers to figure out how to use Regex in vi, it’s that I’m too lazy to take the time to figure it out. I can get away with not knowing that. However, if I DID understand it, how much more productive could I be? Let’s take a quick look at Team Coding together, and maybe I can convince you it’s worth a 2^nd or 3^rd look. John POcknell has already dedicated a blog to Team Coding, but I wanted to take a step back and look at it from a slightly different perspective.

Just What Exactly IS Team Coding?

Team Coding is a technology that was originally developed for SQL Navigator. It allows a developer to work on his or her stored procedure without worrying about a co-worker coming in and clobbering their work. This is such a common occurrence that the feature was frequently requested by our Nav friends when they started using Toad.

Most developers work in an environment where the programs they write are stored in a Source Control system. If a developer wants to work on program X, they login to the repository and ‘CHECK OUT’ program X. At this point, no one else can make changes to program X until the person who has it checked out is finished and checks it back in. This type of control is achieved by file locking.

The challenge for PL/SQL developers lies in the fact that the code for Oracle is evaluated at run-time and is stored in the database – not in a file. Team Coding was developed to achieve two things:

1. Allow Developers to control their PL/SQL objects in the database

2. Allow Developers to associate their controlled PL/SQL programs to their complimentary OS files in their Source Control system.

Controlling PL/SQL objects in the database

Team Coding can operate without a 3^rd party Source Control system. In its simplest form, Team Coding consists of a series of database objects which tell Toad which PL/SQL programs are available. When a Toad user opens a Function into the Editor, Toad will tell them if it is controlled and if it is available. If the object is controlled by Team Coding, and if it is available, the user can then check it out. Once checked out, Toad will allow the user to compile their changes to the database. If the object is already checked out, Toad will tell you so and prevent you from making changes to the object.

Notice the compile button is disabled and the status of the object is listed below.

Team Coding is much easier to use if you have enabled the Toolbar. Right-Mouse-Click in the main toolbar/menu area of Toad and toggle it on. Once opened, you will be able to tell if your session is being affected by Team Coding.

The ‘checkmark’ button on the toolbar will show you the status for your session. You can see for my connection that Team Coding is available AND enabled. I also have the ability to create code control groups AND access the object control.

Team Coding requires a series of Server Side Objects in the database that you are connected to. These can be created using Toad’s Server Side Object Wizard, which is located on the Database > Administer menu flyout.

Once these items are created, you will see the ‘Team Coding Support Installed’ item marked with a green arrow on the Team Coding Status window.

Before we can start using Team Coding, we need to create something called a ‘Code Control Group’ or CCG. You can think of a CCG as a category or a logical grouping of database objects. Defining one allows the Toad user to create define multiple groups of objects in the database that can be controlled in different ways. This is extremely important if you are using a 3^rd party file-based Source Control system. Having multiple CCGs allows you to assign multiple projects or workspaces (or different directories on your PC) that contain the files associated with your database objects.

Code Control Group (CCG) Diagram. Thanks for the awesome help topic Gretchen!

Creating a CCG is basically a 2-3 step process:

              1.       Create/Name the CCG
              2.       Define the mask properties
                           a.       Object Type
                           b.       Schema
                           c.       Define an optional filter based on object name
               3.       If you are using a file-based system, you can now export the
                     objects in the CCG as files to the defined workspace.

You can see all of the objects being controlled by using the Team Coding Viewer.

Team Coding records the user who last touched the object and allows the developer to add comments that describe the changes made.

A Quick Example
I have a stored procedure compiled with errors in our development environment. My Development Manager has assigned me the work order specifying what needs to be done.

Step1: Make sure the object is available. I can see this with the Team Coding Viewer. If ‘Available’ I can check it out and proceed with fixing the errors.
Step2: Load the object into the Editor.
Step3: Check Out the Object.

When you load the object to the editor, if the object is under control and available, then the Team Coding toolbar will activate the Check Out button.

While performing the Check Out operation, Toad will prompt me for a comment. Try to use good comments as this will help you later when you try to track down why a change was implemented for a particular revision. It will also prevent ‘Why do you have XYZ checked out?” emails from your co-workers J

Step4: Fix the code.
Step5: Compile. This will overwrite the source of the object in the database.
Step6: Check In. Once you have made a change to the database source, the Check In button will activate. Again, you will be prompted for a comment.

I tried to describe this process in as few words as possible, but I’ve already exceed 1,000 words! The steps involved for setting up Team Coding to work with a 3^rd party source control provider are a bit more involved. I’d like to propose the following: If you enjoyed this blog and are interested in implementing this in your environment with the file support enabled, then leave a comment on the blog. I’ll then follow-up this topic with a PTII that talks about the file support.

If you can’t wait that long, then I highly recommend reading the Help Topic. It’s quite substantial and it’s what I read when I have questions and don’t want to bother the poor developer.

SQL, Fast & Tastes Good

Jeff Smith — Thu, 27 Sep 2007 20:18:00 GMT

A US-based fast food chain apparently has a new advertising campaign running. I’m not exactly sure what it is supposed to be for, and most of their commercials are pretty confusing. They do have one that I like. Apparently their food is not only JUST fast, but also made to order, and tastes good. What does this have to do with Toad^®? Well, Toad allows you to compose fast AND good SQL queries.

Most of the database development IDE tools out there have a feature called ‘SQL Recall’ that they borrowed from Toad, but Toad has a certain way of going the extra dozen miles or so. Here are a few tips ‘n’ tricks for writing SQL statements at ludicrous speed.

Named SQL Recall
Everyone should already be aware of Toad’s SQL Recall feature. If this is new to you, open an editor and hit the ‘F8’ key. Now browse, search, or sort the last 100 (or 999) valid SQL statements that you’ve sent to the database. Ok, this is a nice feature, but it’s not what wakes me up in the morning. If I wrote something more than 10 minutes ago, I’ve probably forgotten the purpose of the statement in addition to the statement itself. That’s why I give every statement that I run more than once a name.

How do you name a statement? A couple of ways, but the easiest are either to:

1. Select the query in the editor, then Editor > Add to Named SQL. Give it a name. OR

2. In the SQL Recall panel, select the query you want to name. Mouse-Right-Click -> Change to -> Named.

That’s great, but what good does that do, you may be asking yourself. Let’s say that you wrote this AWESOME report for your boss a few minutes ago. You read this blog and then named the query you used to build the report. Now two months from now your boss asks for this report again. You slap yourself before remembering, ‘Hey, that weird Toad guy had me name my SQL.’ Now you can simply hit in your editor and voila:

You now have a list of Named SQL you can choose from to place in the editor.

SQL Recall – Scrolling Statement History

While I enjoy having the ability to see all of my previous SQL statements in the ‘F8’ docked panel, I also enjoy having as much space as possible allocated to my crucial Toad windows. So, I always keep it unpinned or even not open at all. If I know that I’ve written a SQL statement during my current session or in the last few days, then I can normally find my statement fairly quickly using the + keystroke.

While in the editor, I can at any time hit and either the up or down arrow key to cycle through my SQL recall list. When I find the one I want, I just release the key. An even neater feature that one of the Toad guys added for version 9.0 I believe was the ability to do this on selected text. So let’s say I’ve written a nice little SQL query, and I want to add a SUBQUERY for the WHERE clause. I just know that I wrote a query the other day that’s pretty close to what I need already for the subquery.

Now I can replace the selected text from a query that I have stored in my recall list. Using the Down arrow key will start with your youngest SQL statement and go down from the ‘top’, and the Up arrow key will start with your oldest SQL statement and go up from the ‘bottom.’

Make/Strip Code
One of the most powerful features of Toad is also one of its oldest ones. Make/Strip code has been around since AT LEAST the year 2000. However, whenever I go to visit customers I am astounded by how many people have never used or even seen this feature.

If you have a version 6.5 of Toad or higher, then you should keep reading!

Make/Strip code in Toad allows me to take a SQL statement and make it into a ‘Non-SQL’ statement. What does this mean? We’ll let’s assume that I am a very BAD Delphi developer (and even that’s really pushing it). I need to send a SQL query to the database from my Delphi application. I developed the nasty little SQL statement in Toad, but now I need to put it into Delphi. If I’m one of the few who know about Make Code in Toad, then it’s a simple matter of 1 button push to get the code wrapped in the Delphi syntax that I can paste write into Delphi. Otherwise, I get to start adding a bunch of variable value assignments with appends and tons of quoted statements. This is pretty error prone for even a great typist. Let’s see how Toad can help with this:

Step 1: Write a NASTY SQL Statement

Step 2: Configure Toad to use the desired Programming Language and Variable name for your statement.

Step3: Hit the Make Code button in your Editor Toolbar. You’ll now see this at the bottom of your editor panel:

Step 4: Paste your Query statement into Delphi!

Strip Code works the same way. It allows you to quickly change your existing SQL code statement from your custom application, ‘strip’ the non-SQL syntax from the statement, and run it in Toad. Then when you’re finished making your changes, you can just use the ‘Make Code’ button to send your statement back to your IDE.

Homework

If you liked the tips from this blog, then here are two homework assignments that will lead to an even better appreciation of Toad:

1. Auto Replace: Open the Editor Options from Toad and check out the Auto Replace feature.

2. Code Templates: Similar to Auto Replace, but WAAAAAY more powerful.

Feel free to post a comment if you need help.

Toad – not just for Oracle

Jeff Smith — Mon, 10 Sep 2007 22:46:00 GMT

Toad^® has been a popular Oracle development and administration tool for the past decade. In fact, the original trademark for our favorite little guy was ‘T.O.A.D’ – an acronym that stood for “the Tool for Oracle Application Developers.”

A couple of treats for our history buffs:
1. T.O.A.D. from 1998
2. Oracle Magazine story from 1998

I think we can all agree that Toad has been around for quite awhile now. Well, those thousands, if not millions, of Toad fans started to get the attention of people in the SQL Server, DB2, MySQL, and other RDBMS neighborhoods. They started asking, “Hey, when can we get our very own version of Toad?”

This led to an experiment where Quest put out a free, little-publicized Toad that was all about SQL Server on its original user community. Wouldn’t you know it, it experienced several thousand downloads right away, proving that there was a real demand out there for excellent database tools for ALL database platforms. The experiment for SQL Server turned into a full-blown development project to put out a fully-functional Toad that was JUST for SQL Server.

Well it became fairly obvious very quickly that we couldn’t call this new offering “T.O.A.D. for SQL Server.” From that point on, the product family would be known as “Toad” and the individual products would have a descriptive byline to differentiate them.

We now offer full-blown Toad solutions for 4 of the major RDMBS vendors. And now our newest Toad allows you to connect to Access, Terradata, Sybase, and more!

Well, what makes Toad, Toad?

This is an excellent question indeed – and one that dozens of other companies and products have been trying to answer for a long time now. I believe that database professionals need and demand at least the following from their database development toolkits:

1. A Database Browser
Toad’s award winning Schema Browser revolutionized the Oracle environment. Exploring and managing the database is as easy and point-and-click. And if you like tree views, then it’s as easy and point-click-click-click-and click :)

I don’t know DB2, but I do know Toad so I don’t have to fumble around looking for the right commands or queries.

2. A Query Builder
Why do we use tools in the first place? To save time is probably in the top five. When you need to query the database, using a powerful graphical query builder is guaranteed to save you precious minutes of copy and pasting and flailing about looking for the right object and/or column EVERY TIME. Better yet, it shortens the learning curve of actually learning the RDBMS-specific SQL syntax required to answer your pressing business questions.

Quick, join 7 tables, query 15 columns and make no Typos! Oh, and I need it formatted!

3. A Powerful Editor
Whether you are writing application code, designing database migration scripts, or just doing Ad Hoc querying, a proper editor can make this a painless exercise. Code Snippets, Code Templates, 3^rd party Source Code Control integration, SQL History Recall, object lookup helpers, and of course the Toad-original F4 object popup graphical DESCRIBE functionality all go into making Toad a VERY familiar place for a developer, even if they are not as comfortable with DB2 as they are with Oracle yet.

Manage multiple queries, multiple result-set grids, and transform your thoughts and imagination into real usable code quicker.

4. A Development and Management Team that LISTENS and DELIVERS
Many software products have their own user communities. Many even have their own discussion forums where users can get together to help solve each others problems. T.O.A.D. took this several steps forward by actually putting the people that make the product available on the front-line to listen to input. I have personally witnessed several times a user ask for a feature and have it validated and coded and into the current Beta for Toad within the same day for evaluation by the rest of the users. This commitment from Quest continues to evolve today and the future with the Toad Data Modeler, Toad for Data Analysis, and Toad for DB2, Toad for MySQL, and Toad for SQL Server products and their associated communities.

Have you been using Toad for more than 10 years? Have you leveraged your experience with Toad for Oracle into a faster deployment on a DB2 or SQL Server project?

If you can say yes to either of these questions, please share your experiences with our users on my blog today!

FT-Wheeeeeeeeeeeeeeeeee! Or, FTP with Toad

Jeff Smith — Fri, 31 Aug 2007 23:12:00 GMT

It’s no secret that I am a die hard Toad^® fanatic. There are many reasons for this, but the one I want to talk about today is one of Toad’s best kept secrets: a commercial-grade FTP client built right into Toad!

Older versions of Toad can find ‘FTP’ under the ‘File’ menu.

Yes, Toad DOES have its very own fully-functional FTP client. It’s included in the base version of Toad and has been around for a very long time, so there’s a good chance you have access to it. To get all of the cool features I’m going to talk about today, make sure you go get version 9.1 or join the beta program and get version 9.5.

One of my Top 10 Tips’n’Tricks for Toad is to make sure you try to solve your current challenge or problem WITH Toad before you jump into another program. The Toad Development Team has been helping users solve problems for over 10 years now, so there’s a good chance if you need to do something with Toad, someone else has already asked for it and had it put into Toad.

The File Transfer Protocol (FTP) allows a person to send or receive files to and from a server. Developers, DBAs, and analysts frequently need to share Oracle DMP files, SQL*Plus scripts, spreadsheets, and other files. When these files get too big for email and a shared network drive isn’t available, FTP is usually the answer. I frequently use FTP to make updates to Quest’s different community sites like Toadsoft. I NEVER have to leave Toad to do this. While there are many commercial (for pay) FTP clients out there, I would put Toad’s version against any of them.

Here are a few reasons why:

1. Connection history recall.
Automatically connect with one click.

2. Reconnect & ‘Keep Alive’
If you lose your connection, 1 button press to reconnect. Or, configure Toad to refresh the folder listing every X seconds to keep your connection alive.

3. Support for Secure FTP/SSH
Many sites have locked down access to the FTP server for security reasons. If this is the case for you, you can still probably connect with the Secure FTP feature. You can specify your Private Key File for each connection and keep on rolling!

4. Specify a default local and remote directory for each connection!
This is a HUGE timesaver for me. No more traversing endless directory trees. After the connection is made, Toad will automatically switch directories for your local and remote environments.

5. Execute commands against the server.
Need to change the file permissions for your UNIX file? Or rename the file? Or even execute the file? No worries, Toad CAN DO THAT.

Right-Mouse-Click on the Server files to access this feature.

6. Recursive Directory Copy/Delete
The Holy Grail of FTP features. I can select a parent directory and download it AND all of its subfolders with a single click! Many commercial FTP-only programs do not have this feature!

7. Integration with Toad’s other best-of-breed features.
It is one thing to have a FTP feature. It is another to actually use it to make your other features more powerful. Toad hooks up the FTP functionality with the Project Manager and the Action Palette to make things even easier for you.

That pretty much sums it up for why I like FTP in Toad. It’s one less program shortcut on my desktop, one less program to ALT-TAB through, one less program to keep updated J

If you like Toad’s FTP, then I recommend you check out the REXEC and TELNET features as well.

The Toad Family brings home a new Tadpole

Jeff Smith — Fri, 24 Aug 2007 16:00:00 GMT

For more than 10 years now, Toad has been THE tool for Oracle developers, DBAs, and analysts. A few years ago, Quest began introducing versions of Toad that brought native support to popular platforms such as DB2, SQL Server, and MySQL. Oracle enthusiasts were able to jump over to a new RDBMS technology without having to leave their favorite productivity tool behind.

While most features were implemented to make the lives of developers (debugger, profiler, code editors) and DBAs (session/user/database management), we found that about 30% of our users fell into the ‘other’ category known as analyst. We had ‘accidentally’ captured a portion of the market that was primarily interested in massaging data, creating reports, and answering key business questions for their organizations. Features such as the Schema Browser and Query Builder were quickly adopted by these analysts and surely made their jobs easier.

However, what would happen if Quest dedicated resources to making a version of Toad that explicitly addressed the day-to-day challenges of this analyst audience? The answer to this question is about debut in the form of Toad for Data Analysis. Normally Toad includes the database vendor supported via the name of the product. So, what database connections do we support for this product?

Quest is now happy to be releasing a new version of Toad that offers native connectivity to Oracle, DB2, SQL Server, & MySQL all via a single application. In addition, you can connect to Access databases AND Microsoft Excel spreadsheets and query these data sources just as if they were a real database J And even better, we offer connectivity to any other database vendor that supports ODBC. We have even spent quite a bit of time ensuring that vendors such as Sybase and Terradata will behave nicely.

Just what is Toad for Data Analysis?

Most of the problems and challenges we’ve heard from our customers regarding Toad generally boil down to this – “While we LOVE the productivity gains that Toad provides, it is generally much too powerful, and way too complicated for some of our biggest users. They just need to query data, write reports, and understand the relationship of the objects in the database.” With that in mind, we decided to discover the biggest problems this user encounters on a daily basis and deliver an easy-to-understand solution based on Toad around it. This centerpiece workflow makes its presence known immediately upon opening the product for the first time.

Just what can I do with this tool?

Users are prompted to ‘discover’ their database connections, and we’re off to the races! One of the nice features of Toad for Data Analysis is that is will automatically recognize any existing Toad for Oracle connections you have already defined on your computer.

This forum isn’t really the appropriate place to talk about EVERYTHING Toad for Data Analysis offers, so I am just going to go over a few of my favorite features. At the end, I’ll be sure to show you how to get a copy of this new tool right away!

Top 10 Reasons I Love Toad for Data Analysis (for now!)

1. I can now query ANY database or collection of data with a single tool.

2. I can use SQL to find the important data in my massive spreadsheets.

Toad’s grid allows for fast grouping and easy navigation

3. ER Diagrammer documents object relationships for easier querying

The ER Diagrammer allows me to visually see the relationships. I can then send the objects to the Query Builder so Toad can help me construct my SQL query!

4. ‘In Your Face’ filtering notification.

No more ‘losing’ tables! If you have a filter, you’ll know it

5. Spectacular BLOB support for Oracle

View images natively in Toad, or see their HEX equivalent! Save or Load images automatically by pointing to a file.

6. Multiple Table/Object comparison of data
Toad for Oracle has allowed you to compare the data of two tables for quite some time now. Toad for Data Analysis takes that a step further and allows you to select multiple objects between 2 different sources.

Select your objects, view the differences, and synchronize the sources!

7. Built-in tutorials and videos

Want to learn more about Toad’s support for Excel? Watch the movie!

8. Card or Grid view for Data

Think ‘Single Record View’ format for viewing multiple records at once.

9. Make Toad for Data Analysis look like your favorite IDE

Tell the Configuration Wizard which IDE you want to emulate.

10. Full support for Unicode
OK Oracle junkies, you now have a member of the Toad family that can view and update any Unicode based dataset.

Example with Diacritical Characters

Example with Korean Characters

So how do I get a copy?

Toad for Data Analysis is available for purchase on a per-seat basis. Additionally, for those of you who own Toad for Oracle, Toad for Data Analysis will be available for an extended evaluation period. Please visit www.quest.com/toad-for-data-analysis for more details or to download an evaluation copy.

Finessing Filters and other Fun Features - Part Two

Jeff Smith — Fri, 10 Aug 2007 21:10:00 GMT

In my last blog I attempted to cover all of the cool filters in Toad in a single posting. How silly of me! Let’s see if we can’t further down the road.

As a quick re-cap, we went over the first 3 filters available in the Schema Browser.

Schema Browser List Filters

We’ll pick back up with the data grid filters. As a quick note, these filters are generally available wherever you see a data grid in Toad – not just in the Schema Browser.

Add a SQL based filter for the data

When I select a table in the Schema Browser, Toad is very polite in telling me that it has returned the first 500 rows. Dealing with a large amount of data can be a pain, especially if you are only interested in a small part of it. This is an obvious place for a filter. We have 2 options, using the classic filter as highlighted below, or using the ‘Excel Style Filters.’ Let’s look at the classic filter first.

If you click on the funnel, you’ll be taken directly to filter dialog. You have the option of opening previous filters you’ve defined. This is a slightly newer feature, so if you don’t see it, it’s time to upgrade!

In the filter dialog, you have the ability to name your filter, select the columns you want filtered, and then Toad gives you several qualifiers that you can use to build compound conditions for.

One nice feature I enjoy is the ‘Data Select Dialog.’ Toad queries the distinct values and allows me to pick them for the filter condition so I don’t have to manually browse or query the table.

Let Toad show you valid values for your filter conditions.

When you’re finished defining the filter, we can then go back to browser our data. The where clause you’ve just built has been appended to the ‘SELECT * FROM schema.table_name’ query that Toad sends to Oracle to show you the data. You know the table has been filtered because the funnel has changed to red. Once you have defined a few of these, you can easily switch between them to get different views of your data with a single click!

Once you have defined a few of these, you can easily switch between them to get different views of your data with a single click!

Add an ‘Excel-Style’ Filter

What is an ‘Excel-Style’ filter? Excel style filtering lets you filter directly from the column headers on the results grid. Dropdown arrows attached to each heading bring up a filter dialog box. Up to two criteria can be used on any column. This must be enabled before you can use it. To do that, use the power of the Mouse Right Click!

Activate Excel-Style Filtering by toggling it on via a Right-Mouse Click

Once you have this on, you can define filters on the grid, just like you can in your favorite spreadsheet application. Hint: By the way, if you prefer Excel over Toad for working with your data, send the data directly to Excel via the SaveAs -> Excel Instance command.

You’ll know it’s activated when your grid column headers look like this:

To add a filter, just click on the column drop-down control and choose ‘Custom’. You can then define your filter.

The value boxes have a Calendar popup for dates and a Calculator popup for numbers for added convenience.

Whew! That was a lot of typing to JUST go over the types of filters available in just the Schema Browser.

There are many more places in Toad where you can find Filtering available in order to make your data more accessible. Here are a few places to go look to learn more:

1. Object Palette
2. View -> Options -> Data Types
3. Session > Schema Browser Filters…
4. Session > Oracle Users List

If you have a favorite filter in Toad that I didn’t cover, please add your comment to the blog so that others can learn from your experience.

Finessing Filters and other Fun Features - Part One

Jeff Smith — Wed, 01 Aug 2007 19:06:00 GMT

Having the world’s information at our fingertips via databases is an extremely powerful resource. It can also be quite daunting. Any Oracle user who has had to manage an APPS database knows this intuitively as those schemas consists of hundreds of thousands of objects. Or you could have only a solitary table, but the table has a thousand columns and a million rows. How do we digest this in a manner that’s somewhat edible? The lucky among us use Toad of course! Let’s talk about filters and how they can make your life much easier.

Just what is a filter? Well, the American Heritage Dictionary tells us:
fil·ter (fĭl'tər) n. Computer Science A program or routine that blocks access to data that meet a particular criterion: a Web filter that screens out vulgar sites.

In Toad’s case, a filter is a mechanism that allows us to limit or define more explicitly the information we want to view. Toad asks Oracle for information and then displays it back to the user in a manner that is more appealing than SQL*Plus. This is the beauty of Toad in a nutshell.

Toad has MANY filters available for making Oracle easier to work with. Here are my favorites, but if you see one you think deserves a shout-out, make sure to leave a comment at the end of the blog so everyone can learn from each other.

Schema Browser List Filters

There are at LEAST five filters in the Schema Browser alone. Here’s what each one allows us to do:

1.       Filter objects by Project
2.       ‘QuickFilter’ the object list
3.       Advanced filter for the object list
4.       Add a SQL based filter for the data
5.       Use ‘Excel’ type filters for the data.

Filter objects by Project

Our funnel has to be what looks like the ‘book’ image we see in our Project Manager window button graphic. This is not an accident as it allows the user to choose any project, or sub-project, to act as the filter for the objects that appear in the Schema Browser. If I have a project called ‘Test’ that has the test objects defined, only the objects defined in that project will show up in the Schema Browser. I know many database administrators who use projects to help organize their regularly scheduled tasks. A DBA who had to manage a development instance that consisted of many thousands of objects for different projects could use this to easily view project specific objects instead of having to pick and choose between all of them. You can easily switch from one project to another and have your Schema Browser automatically updated to only see the objects of interest.

‘QuickFilter’ the object list

We can also just quickly type in some pattern matching filters for the list of objects already retrieved by the Schema Browser. The splat, or “*”, in the edit box means, show everything. This window is powerful because it filters objects that have already been retrieved from the database, e.g. client-side filtering. I could use this in conjunction with the project filter. I could say, only show me objects from project ‘ABC’, but also further filter that list by only showing me objects that start with a character between A and C, or “[a-c]*”. If you have a slow database and you are tired of using the advanced filter, use the ‘QuickFilter’ as you won’t have to wait for the database to respond. This is also nice feature as Toad will remember your last 25 ‘QuickFilters.’

Advanced filter for the object list

This is by far the most powerful filter in Toad. This window allows the user to tell Toad EXACTLY which objects to bring back on the LHS of the SB. The options available on the filter dialog will vary depending on the type of object being filtered. In this case, it is for the tables. I can tell Toad to only show me tables with particularly named columns, tables of a certain type, or to order them by their tablespace versus their name. Let’s take a quick look at two things usually overlooked on this screen:

This check box tells Toad to read the options you’ve defined in the dialog, but before sending the SQL query to Toad, it will popup the SQL in an edit box that you can use to change the SQL. So for the filter defined above, Toad will show you this:

See how Toad finds our objects, or change the SQL to fit your needs!

This allows bypassing what SQL statement the genius Toad developer has defined for this TAB in the Schema Browser and permits the user to write their own. You are of course on your own if you decide to go this route J

This checkbox tells Toad to bring back EVERY table in the database, regardless of Schema. Rather than showing us less information, this filter object allows to remove any restriction from the list of objects in the Schema Browser. This is a fairly frequent question – “How can I get a list of all tables in my database?” This is probably the easiest way.

Hey, always show me all tables, regardless of the schema selected.

We’ve just gone through three of our filters, and that’s just for the Schema Browser! We’ll pick up where we left off in next week’s blog. Until then, here’s a parting tip to help prevent you from tripping over a Toad filter:

If you think you’ve accidentally dropped your tables or table data, don’t despair, you may have just forgotten to clear a filter. If you see Red, your data is being filtered.

References

American Psychological Association (APA):
filter. (n.d.). The American Heritage® Dictionary of the English Language, Fourth Edition. Retrieved July 11, 2007, from Dictionary.com website: http://dictionary.reference.com/browse/filter

The Quest Easy Button: Faster SQL = Faster Applications

Jeff Smith — Wed, 25 Jul 2007 16:07:00 GMT

Your Mission: Go fix ALL of the slow SQL in your application ASAP.

SQL is an extremely powerful language for us database geeks. Without completely understanding, or even caring how the database is going to resolve my query, SQL allows me to ask for data by describing it. For example, “Give me the employees by department, sorted by salary” equates to:

The database, in this case Oracle, then decides exactly how to get me that data. Therein lies the catch for SQL: while extremely powerful, it puts your application at the mercy of the database engine. Studies have determined that 80% or more of database application performance problems result from poorly performing SQL.

So HOW do we fix this? For several years now Quest customers have benefited greatly from the Quest SQL Optimizer technology via Toad or Quest Central. Users were able to take an identified ‘BAD’ SQL statement, and the SQL Optimizer would automatically find the best performing re-write for you to use in your application. However, how many applications use a single SQL statement? We now have a solution that allows you to point the Quest SQL Optimizer to a collection of SQL statements and have them all batch optimized, or tuned, in one session.

Now, it’s as simple as telling the SQL Optimizer where your SQL is located. This could be:
- database objects
- the source code files
- the SGA (Oracle)

The Quest SQL Optimizer will then examine the contents of these objects, take the risky SQL statements and auto-batch optimize them. The re-writes will then be available for you to automatically send back to your original objects – not including the SQA of course J

Step 1: Find the Pesky Ill-Performing SQL

Choose the instance and where to look for SQL tuning candidates.

Step 2: Press the Easy Button, in this ‘OK’!

The Optimizer will scan the objects, look for bad SQL, tune it, and present the results!

Step 3: Create Migration Script to implement the optimized SQL

Once you’ve examined the results, you can implement them for development or testing.

How to Get the new Quest SQL Optimizer.
This is available immediately for download from Quest.com to existing Toad for Oracle Xpert users or anyone else that has purchased the SQL Optimizer option for Toad or for anyone who has licensed the option from Quest Central. Just login to Support at Quest.com and go to the updates page for ‘Quest SQL Optimizer’.

Do you see ‘SQL Optimizer’ in your Help > About for Toad?

Are you not a current license-holder, but would like to download a copy for evaluation? You can get a trial standalone download.

For more information, try the following resources:
            ·          Quest SQL Optimizer User Community
         o         View a Flash Demo
            ·         Quest SQL Optimizer Product Page

Addicted to Toad

Jeff Smith — Mon, 09 Jul 2007 15:57:00 GMT

What the heck is that?

This is what my friend and co-worker, Dennis, supports on a daily basis! And yes, it is that confusing.

Dennis is an Applications Developer for our IS group here at Quest Software. I asked Dennis if he’d spend some time talking about what he thinks about Toad®.

Jeff: So Dennis, just what is that diagram?

Dennis: This is actually just a portion of an Oracle Apps 11i Order Management ER Diagram. I’m just glad I wasn’t the person that got assigned the task of diagramming this. This is only 1 module out of almost 200 that can be installed in an Oracle Applications installation.

Jeff: Wow. I’m betting that the complexity can really skyrocket depending on what you’re trying to achieve with Apps.

Dennis: Put Oracle Applications hardware, service components, forms, reports, development tools, setup modules, refreshes, database development/support, etc. on top of all this and it can be overwhelming to almost any programmer. I have worked with Oracle Applications and databases for nearly 15 years, and I have only been able to learn a fraction of all its components and functionality. I have been able to become efficient in a couple modules, and familiar with several more. The nice thing about working in an Oracle Apps environment is there is never a lack of something to learn. The down side is, well, there is never a lack of something to learn. I quickly contest anyone that believes they are an Oracle Apps expert, simply because of its shear size. I have had the pleasure to work with some of the most intelligent Oracle Apps gurus over my career, and they all have said the same thing: It’s impossible to know everything!

Jeff: So, how do you cope with such a daunting task?

Dennis: The answer is actually pretty simple – you can’t! But, there are things that can make your life a lot easier. One of them is using tools that ease the pain and suffering. One of those tools for me is Quest’s Toad for Oracle. This tool saves me several hours each day.

Jeff: How can it do that?

Dennis: The first thing you notice when you go into Toad is the easy to use GUI Interface. Ok, I know what your saying – a pretty front-end cannot save you time, right? Wrong! Perhaps, I’m a bit nit-picky about my code, but I want everything where I can find it, and most importantly, find it when I need it. Toad lets me do just that.

But, moving onto the real time savings has got to be all the different components within Toad. I can have numerous database connections open at once, so if I need to do something in one of my test environments, I can very easily port that change/code over to another environment in a matter of seconds.

Toad is designed to handle multiple Oracle connections with ease

Another key feature that I utilize a lot is the Schema Browser. How great is it that I don’t have to remember what all the different schema names are, and I can pick tables from a list, instead of trying to remember their spellings, like we had to do in the old days using SQL Plus. Oh, I can look at data in a table and filter it based on what I want to see – this is incredible! I don’t even need to write code to look at data. I can’t tell you how many times this has resolved issues quickly in my environment.

Toad’s Schema Browser revolutionized the Oracle IDE world.

The Project Manager is another big time saver. I can put all my projects in there and quickly and easily have access to all my code for a given project. I also have FTP sites setup to our servers so I can quickly FTP any code to our Oracle Apps server when needed. I even store links to some of my most commonly used files (Excel, Word, Text, etc.) in Project Manager. Why? Why not! I’m in Toad all day long, so there is no need to go to a different application to find what I want when I can have it in front of me already – nice!!

Jeff: What about when you to need to research a complex database topic or technology?

Dennis: If I ever have a question about some PL/SQL syntax or just need an example, I can simply go to the Knowledge Browser, and get the answer within seconds. I don’t need to try to navigate Metalink’s plethora of menus and websites to find a simple example of some code.

Most of your answers are generally only a click away.

Jeff: What about help with Oracle Applications itself? Is there anything Toad helps you with explicitly here?

Dennis: Of course, another great tool for any Oracle Apps Developer is the EBiz Browser. I can now see the actual applications setups without actually having to log into Oracle Apps at all.

Did you know that Toad had support for eBiz?

Jeff: Any last tips for someone who might just now be upgrading to the latest and greatest version of Toad (9.1)?

Dennis: Yes! Another cool new feature is the Action Palette. In here, I can create multiple steps of routine tasks that I have to do on a daily basis, such as create a report for a user, and then email it directly to them, with simply one touch of a button.

I could go on for days…but, from my perspective as an Oracle Apps Developer, there is no substitution for a great tool. I’m never going to know or learn everything I want to in my job area, but I can definitely try to make my life easier. Toad makes that easy and most of the time, even fun.

Jeff: Thanks so much Dennis for taking the time to share your thoughts with the rest of our Toad World users!

If you’re interested in learning more about the challenges faced by IT professionals and the Best Practices Quest advocates and USES to address them head-on, I recommend reading this white paper by Quest’s VP of Information services, Carol Fawcett. Tired of reading? Watch the video.

Toad for Oracle: Not Just an Oracle IDE

Jeff Smith — Mon, 02 Jul 2007 20:35:00 GMT

I’ve been working with Toad® for several years now, and even now that I’m NOT directly associated with the Toad development team, my Windows taskbar usually has at least 1 if not 2 Toad programs hanging around. To be honest, if someone were to take Toad away from me today, my productivity would definitely suffer.

While most users are introduced to Toad as the market’s leading Integrated Development Environment (IDE) tool for Oracle, I am betting that I am not the only one who has discovered that Toad is good for SO much more than simply querying data and managing Oracle database objects. What excites me the most is that after Toad has helped me accomplish the de facto Oracle task, it’s also ready to help me take it to the next level. I invite you to continue reading to see how I use Toad to get more done in less time and all within a single program.

8:00AM (ok, 8:17AM J)
My counterpart in the Finance department has emailed me the monthly sales data ready to be analyzed. My challenge is to take 58 thousand or so rows of information for a spread of several products and to turn it into a meaningful report for my boss.

8:19AM
Ok, now that I’ve got that data into my Oracle database, I can use Toad to…wait a sec, not sure how I got that information from Excel into Oracle so fast? Let me back up a few steps and take you through it.

Luckily, I get this report every month and it’s always in the same format. The first thing I did was to create a table in Oracle that matched the format from the spreadsheet. Thankfully Toad helped do this for me:

With Toad version 9.1, I can open the Create Table wizard, and it can dynamically read in column headers from Excel to create equivalent columns for my table. It’s even smart enough to know how to handle column headers that won’t play nice with Oracle object naming conventions.

Now that my table is created, all I have to do is move over the 58,000+ records. Thankfully, Toad has a screen for this too.

Since I created the table to have the same structure as the spreadsheet, importing the records is as simple as hitting ‘Next’ a few times. And once I have done this once, I can save the session particulars to a file so I never have to manually step through the screens and remembering my settings again.

It generally takes my Thinkpad running a 10gR2 instance about 7 minutes to process this data. Way faster than I can remember how to do pivot tables or setup the spreadsheet as an ODBC data source to query…ick!

8:25AM
Now that I have my information is an easily searched format, I can run my Named SQL queries. If you have not figured out how to name your statements from the SQL Recall wizard, I highly suggest reading the help topic titled, “Add to Named SQL” today. Once you have your favorite SQL statements named, you can bring them up with the CNTRL + N keystroke sequence.

8:30AM
Ok, now I have my results in the format I wanted…in this case as an Access Database File for my non-Oracle friends. You did know Toad added this feature in 9.1, right? I’ll even have Toad automatically zip it up for me. But now I need to FTP it up to our shared server so other people can access it. Here is what I used to do to accomplish this or before Toad v9.1 was released.

With the Project Manager I can simply drag and drop my files to my FTP(s) project item and Toad will automatically upload them to the server for me. It even supports SSH so I do not have to worry about security issues. This saves me the time of opening my ftp client, remembering the password, finding both the right local and remote directories, and moving the files.

If this interests you, please read more about it on Toad Soft. The process I just went through, however, is still a manual series of steps. This is nice, but what about if I have to do this over and over every day or every week? I shouldn’t have to do this manually anymore. Now I can ask Toad to do the entire series of steps (query to report to export to ftp) via the Action Palette with the touch of a single button.

John Pocknell has a great blog on this already. Make sure you’ve read the overview here.

9:00AM
Ok, I’m done with my normal database tasks for the morning and now I need to schedule some flights and put together some presentation materials including powerpoints, word docs, travel documents, database connection information, bad SQL that needs tuned for demonstrations, etc.

Thank goodness I have the Project Manager still open! All I need to is switch over to my travel/presentation project and add my new entries. I can cue up my presentation or auto-connect to my demonstration schemas all with the click of a button. Even better, when I’m on the plane or have a few extra minutes to unwind before getting ready to talk to a customer, I can watch a few videos or listen to my music, all straight from Toad.

So I hope you have seen a few things up Toad’s sleeve you were not aware of before waking up this morning. If I have accomplished nothing else, at least I’ve finally admitted to myself that my favorite window in Toad is the Project Manager!

I hope to spend some time in my future blogs to investigate how some of my other favorite products interact with each other to help me get the task at hand done.