Toad: powerful IDE for SQL & PL/SQL code development

Toad IDE for SQL & PL/SQL codingWelcome to the wild and wonderful word of Toad. There are a lot of things you’ll need to know before installing and using the product. Even though Toad is just a Microsoft Windows application with a standard Windows installer, there are some significant, non-obvious things to appreciate before beginning your journey.

While some experienced Microsoft Windows and Oracle database users might already know all the issues covered in this section, nonetheless it is worth reviewing to prevent any potential headaches or gotchas. For those not quite as comfortable with Microsoft Windows or the Oracle database, this section will be absolutely critical for your success and even satisfaction. Many Toad users have initially found the product difficult due to a lack of this knowledge. Most have eventually said “Wow, that was really easy once someone explained it!”


Toad Release History

Toad has been around for almost two decades. It started as freeware built by one lone Oracle user who hated using the command line in SQL*Plus - and no graphical tools were available. Around 1997 Toad and that developer were acquired by Quest Software (now Dell Software). Thus began its commercial journey.

During those two decades there have been many Toad, as well as Oracle, database releases. Some important interdependencies exist among all of those releases. Knowing what version of Toad works with which version(s) of the Oracle database is one example - that is, compatibility. Then there’s what version of Toad supports which Oracle database version’s new features—that is, support. Figure 1.1 shows the history timeline for both products’ versions. You can learn a lot from this diagram.

Toad and Oracle release histories

FIGURE 1 Toad and Oracle release histories

Note that Figure 1.1 entirely answers the support question. If the Toad release precedes the Oracle release in question, then obviously there’s no way that version of Toad can support the new database features. So, for example, a fair number of people are still using Toad 9.7, mostly because they did not pay for maintenance (that is, support) and thus were not entitled to updates. However, the same people also tend to be using Oracle 11g R2 or 12c. Because Toad 9.7 was released before either of those Oracle versions, such users won’t be able to use Toad to leverage 11g R2 or 12c features. In general, Toad support for new database version features will be either the first or second Toad release after the database release. In fact, Toad 10.1 supports Oracle 11g R2, whereas Toad 12.1 supports Oracle 12c (i.e.,

However, Figure 1.1 does not answer the compatibility question. Other mitigating factors come into play; specifically, the local PC’s installed Oracle client version(s) (covered later in this chapter) and Toad’s data access component, which communicates to the database via that client. So, for example, Toad 9.7 may yield an error about an unrecognized client version if your Oracle client is 12c, which the old data access component won’t recognize. Likewise, Toad 12.6 may yield an error about a deprecated client version if your Oracle client is 9i, which the database no longer supports. The first is a Toad error and the second is an Oracle error.

So here’s some advice about compatibility. If you’re using a really old version of Toad, then you’ll need an Oracle 9i, 10g, or 11g client (which can be recognized by the data layer component). However, then connecting to newer database versions with deprecated 9i or 10g clients may fail. If instead you’re using a newer version of Toad and newer database versions (that is, 11g or 12c), then you should use the latest and greatest Oracle client (that is, 12c, but 11g will work as well). But if you’re using a newer version of Toad with older versions of Oracle database, then you’ll need an older Oracle client. Finally, if you’re using a newer version of Toad with both current and older versions of Oracle database, you may need two Oracle clients—one for old versus new database versions. This is all rather complex, so Table 1.1 better illustrates these rather intricate and obtuse interdependencies.

 Toad versus Oracle Compatibility


Finally, note back on Figure 1 that 64-bit support was added beginning with Toad 11.6.


Toad Editions and Suites

One quite common issue that confuses most new (and even long-time) Toad users is knowing what edition they have purchased—and thus what features they have. Many people seem to wrongly assume that there is just one form of Toad that costs $975, and that singular Toad gives you every feature possible. In reality a great many Toad offerings called editions and suites are available. They include the following:

  • Toad for Oracle Base Edition
  • Toad for Oracle Professional Edition
  • Toad for Oracle Xpert Edition
  • Toad Developer Suite for Oracle
  • Toad DBA Suite for Oracle
  • Toad DBA Suite for Oracle—RAC Edition
  • Toad DBA Suite for Oracle—Exadata Edition

The Base edition is the foundation for all other editions and suites. It generally possesses just the basic, core features and functionality. That’s not to say that it’s crippleware. Many people find the Base edition more than sufficient for most normal database tasks, such as working with SQL or PL/SQL and browsing their database objects. Naturally, due to the lowest cost, this is a popular edition.

The Professional edition includes all the features in the Base edition, plus adds database import/export support and Code Analysis (formerly called CodeXpert). Years ago Professional also added the PL/SQL Debugger, but that feature has since moved down into the Base edition. Code Analysis is the big item here. It enables automated PL/SQL code reviews and rates the code using well-established software engineering metrics. Many larger shops use this to reduce coding defects.

The Xpert edition includes all the features in the Professional edition, plus adds integrated, push-button SQL tuning and the standalone, full-featured SQL Optimizer product. The integrated tuning within Toad’s SQL Editor is sufficient for most basic needs. When the SQL is more complex or you want far more control (that is, options) while tuning, you can invoke SQL Optimizer, which finds every combination of SQL variations yielding the same results but with different and hopefully better run times.

For each of these three editions, there is also an optional DB Admin Module that costs extra and adds numerous DBA features. Some people attempt to administer their database using just the Base edition, and for some it’s sufficient. For more complex DBA tasks users have two options: Oracle Enterprise Manager (OEM) or Toad’s DB Admin Module. Many choose the latter so they can perform all their tasks using just the one tool. Just the additional database object types supported, database browser, database health check, compare schema with full sync capability, and multi-schema compare/sync warrant the extra cost.

As for the various Toad Suites, they are an entirely different animal altogether. They bundle in other standalone tools along a theme: code developer versus DBA. The Developer suite contains Toad Xpert plus Code Tester for automated unit testing and Benchmark Factory for stress testing. The DBA suite contains Toad Xpert plus DB Admin Module, Spotlight for diagnostics, and Benchmark Factory for workload replay. Finally, the DBA suites for RAC and Exadata merely add Spotlight diagnostic support for their namesakes. Many people find Spotlight alone worth the price of the DBA suite because it is licensed by seat and not database server, enabling you to monitor any database having performance problems.

Finally, people often say, “I don’t know which Toad edition or suite we bought. Is there a way to find out?” For suites the answer is to look on your Windows Start menu for entries named Dell and/or Quest Software. Under those you will find entries for the names of the products that have been installed. If you see Code Tester, then you have the Developer suite. If you see Spotlight, then you have the DBA suite. If all you have is Toad and SQL Optimizer, then you have the Xpert edition. If all you see is Toad, then simply invoke Toad’s Main Menu → Help → About to display the splash screen, which displays your version and edition licensing information as shown in Figure 2.

Toad’s About screen 

FIGURE 2 Toad’s About screen

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 551 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 330 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 560 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 563 views Гвен Sun, 03 Jun 2018, 11:54:40