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.
- 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