Jeff Smith

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!