In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. In part 2, I checked out PL/SQL Developer's refactoring features. Now, it's time for Toad.

 

Differently from both PL/SQL Developer and SQL Developer, Toad does not have a separate Refactoring right click menu. Instead, the main (and most important) refactoring is explicitly visible from the right click menu in the editor: Extract Procedure.

Let's dive right in and explore this feature. When I select Extract procedure...., I see the following window:

 

 

When I choose the default (and check the "With replace option" – which I would argue should be checked by default), Toad then opens another tab in my editor and fills it with this:

It did not automatically compile the procedure for me, which I actually like. I'd rather be responsible for the act of compiling - and perhaps over-writing an existing procedure of the same name, which in fact would have occurred here, as you can see from my list of existing procedures:

 

 

The extraction was smart; I ended up with a procedure that has two formal parameters, ensuring that it is completely independent of the original context (the use_cursor procedure). That original procedure is also changed appropriately:

If I choose the "local procedure" option, then Toad correctly creates a local subprogram for me:

 

 

Conclusion:  a very handy and competent refactoring. It performs correctly under all tested circumstances (PL/SQL Developer had a number of bugs, which I have been told is fixed in a later release than I used). My suggestions for improvement? Let me specify that I want the stored program to be not schema-level, but inside a package. And if I could choose an existing package to put it into, well, that would be fantastic! I could cut down on the number of independent stored program units (I try to avoid schema-level functions and procedures whenever possible), and increase the likelihood of code re-use.

 

That is the only truly automatic refactoring feature in Toad, at least from my perspective. But for the sake of performing a true apples-to-apples comparison, you may recall that SQL Developer offers these options on its refactoring menu:

I don't really consider case conversion to be refactoring, but in case you do, you can certainly do that in Toad, either under the Edit menu in Toad or  on Editor toolbar (or you can simply use the keyboard shortcuts):

 

 

Toad doesn't offer any kind of rename feature, as does SQL Developer and PL/SQL Developer, but both of those implementations were a bit hobbled in how they worked. I doubt they are used much on a day to day basis.

Overall Conclusion

All three tools examined (Toad, SQL Developer and PL/SQL Developer) offer competent Extract Procedure functionality, though at least in the versions I used, Toad had the fewest drawbacks in its implementation. Beyond that, Toad has fewer explicit refactoring features than PL/SQL Developer. My feeling, however, is that outside of Extract Procedure, the refactorings are minor and of limited importance.

 

I hope that with the introduction of PL/Scope and ALL_IDENTIFIERS in Oracle11g, we will see a major step forward by all IDE vendors as regards refactoring and code analysis.