Best developer tool: An Oracle Ace's favorite things in Toad for Oracle Base

I believe that the Quest® Toad® for Oracle Base Edition is the best SQL and PL/SQL development tool in the business because it has the following features:

The best code templates complete with variable input.
Robust data export features.
Symbolic PL/SQL debugger.
A multi-function SQL editor. (My recent blog, “Why an Oracle Ace thinks Toad® has the best SQL editor in the business,” goes into great detail on editing SQL within the Toad Editor. This blog post contains many of my favorite features of Toad and these features are also in the Base edition of Toad for Oracle. These features include Code Insights, Auto Replace, and code templates.)

Why is Toad for Oracle Base Edition the best developer tool?

In my opinion, the Toad for Oracle Base edition is a perfect SQL and PL/SQL development tool. This edition has all the key features a developer should need to create and maintain quality application code. Other editions of Toad for Oracle contain specific and advanced features like Code Analysis, SQL Optimizer, Benchmark Factory, Code Tester, Toad Data Modeler, DB Admin Module and Spotlight on Oracle), but this Base version tool will get you the core development tools needed to create and maintain good Oracle SQL code and PL/SQL code, procedures, functions, and packages.

Toad for Oracle has one editor across its editions and this editor handles both SQL and PL/SQL with the same code assistants and ease of use features for any type of Oracle coding.

Toad for Oracle Base Edition is a developer tool that will enable you to automate tasks, collaborate development, debug and standardize code. It has the following features:

Database browser
SQL editor
Debugger
Team coding
Formatting
Query Builder
Automation

While all of the above features help you get more done faster, each DBA or developer might use some features more than others. I’m going to review my favorite three features of this developer tool below: code templates, data export features, and PL/SQL debugger.

Best code templates

The code templates feature is really intended for use with PL/SQL. I will go into a bit more depth here.

The Toad for Oracle Base Edition contains over 40 interactive PL/SQL code templates. The templates in this developer tool range from cursor loop code to complex IF-THEN-ELSE and CASE Statements. These templates include looping structures, complex Bulk Collect and FORALL syntax, and UTL File syntax.

Many of these templates will prompt you for substitution variables.

My favorite code to show this feature is the ‘crloop’, a signal to pull in the cursor loop syntax and populate the table names appropriately.

These developer tool templates are signaled in the editor by using the key combination <ctrl> + <space bar>. IF you put in the short cut name, it will access that particular template. If you give just ‘cr’ + <ctrl> + <space bar>, Toad for Oracle will bring up all the templates that start with ‘cr’. Just entering <ctrl> + <space bar> will bring up a list of all the templates.

Toad for Oracle PL/SQL template example.

Toad for Oracle PL/SQL template example

For this example, enter ‘crloop’ followed by <ctrl> + <space bar>. This will bring in the template and prompt you for any substitution variables.

See the developer tool, Toad for Oracle, PL/SQL template in action.

Toad for Oracle PL/SQL template in action.

Notice in this example that we can see the &&c1 substitution variable and I have given the value of "EMP" to use here.

Toad for Oracle PL/SQL Template with variable substitution.

Notice the PL/SQL code came in, nicely formatted and the cursor is on line 3 after the EMP cursor name, ready for additional syntax from the Toad user.

Toad for Oracle PL/SQL internal template syntax.

Toad for Oracle PL/SQL internal template syntax.

In Toad Options/Editor/Code Templates, we can see the actual code template. The substitution variables have the double ‘&’. The cursor will be placed where you see the vertical bar.

You can easily add your own templates using this developer tool. This feature is nice since Oracle really doesn’t have the concept of a copy library so maybe you could put in your common Exception clauses, or any other syntax you commonly use. You can also change any of the existing templates to better fit your needs.

The next two images contain all of the code templates available in this developer tool, as well as any version of Toad for Oracle.

Toad for Oracle Base Edition PL/SQL available templates.

Toad for Oracle Base Edition PL/SQL additional available templates.

Data export features

This feature is really intended for use with PL/SQL. I will go into a bit more depth here about this developer tool feature.

You right mouse click in Toad for Oracle Base Edition on about anything, and you get a variety of options.

Right mouse click on any data grid and you get these options:

Toad for Oracle Base Edition, Data Grid, right mouse click options.

These options give you a variety of features from finding certain data items, sorting the grid, filtering data out, and more.

The reports part in this developer tool is a full-featured report writer called Fast Reports. It builds custom reports and passes the data grid data to it. There is a report manager that allows for these reports to be easily run as needed.

I’d like to focus on the Export Data Set here.

Toad for Oracle Export Data Set output options.

Toad for Oracle Export Data Set output options.

Notice all the different formats that this data can exported to. If you are a big fan of Microsoft Office, you can export to Access, but most users export to Excel. There are 2 options here…a new spreadsheet or Toad for Oracle will add a sheet to an open Excel spreadsheet. There is complete control over the export, like path and name (including a timestamp), using the database column headers and column headers in the output, etc.

Notice you can export the data into about any format you need using this Toad developer tool. Newer features including exporting to a JSON document, ODBC database, and XML format. I have used the Insert Statement options. This feature allows you to fully qualify the table names and more.

PL/SQL debugger

The developer tool has a PL/SQL debugger, which is a full featured symbolic debugger. Symbolic debuggers allows you to set stop points (called break points) then you can walk thru the code one line at a time. This is very useful in debugging problem code such as loops and called routines. Symbolic debuggers also allow for variables to be monitored and changed.

Toad for Oracle PL/SQL debugger options.

Toad for Oracle Base Edition includes the PL/SQL Debugger that has been around for a long time and is an essential tool for PL/SQL development.

The drop-down menu debugging options are illustrated here. In this developer tool there is also a tool bar button for each option that will automatically appear as does some additional tabs for the break points and watched data. The Call Stack tab is quite handy to help you monitor how and where this code was accessed, something I could have used back in Oracle8!

This feature allows you to run the code in debug mode, stop the code at any given point, execute code up to the line the cursor is on, trace into a called routine (I call this the ”next step” button…this is also used to walk thru the code one line at a time), step over a called routine (the routine will be executed but the debugger will not follow the code into the called routine). The break points will stop the code. These breakpoints can also have conditions such as how many times thru a loop the break point was passed or when a specific data item is reached/accessed. This is called conditional break points. Data can be watched, data from cursors can be watched as well as the cursor status…a feature I have not noticed in other PL/SQL debuggers.

Watches allows for data to be viewed and changed on the fly (ie: change the data items to what is breaking the code, then use trace into to walk thru the code to see where the problem occurs), an excellent feature in this developer tool.

The Toad for Oracle debugger can attach (thru SQL*Net) to a program running and drop into debug mode with live executing code! This is the Attach External Session feature.

I have used the original DBMS debugger for years and years. There is a newer JDWP debugger now available and this same debugger can symbolically debug scripts as well.

Toad for Oracle Base edition is a developer tool that is rich with features that assist its users in creating and maintaining good SQL and PL/SQL code. If you're interested, get a free 30-day trial, no credit card required.

Watch the video version of this blog: