Automatic refactoring in PL/SQL tools – Part 2

Steven Feuerstein — Tue, 06 Jan 2009 16:30:00 GMT

In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. Now I will check out PL/SQL Developer (version 7.1.5)'s refactoring features.

To access these features, I highlight some code in a procedure edit window and then choose Refactoring from the right click menu. I see these options:

I explore each of these below.

Rename item

If I highlight the name of identifier in its declaration section, the Rename item option is enabled:

I specified "abc" as the new name for this variable and PL/SQL Developer correctly substituted all instances of that identifier:

What if I have another variable with the same name in a different scope? PL/SQL Developer does a nice job of recognizing that it should not change it:

Sadly, it's not yet working at genius level. If I include in the subprogram a reference to the outer l_cursor variable, it does not change that name:

Conclusion: Works quite well, though it would be nice if I could highlight an identifier name anywhere in the program and have it do a rename (not only in the declaration statement for that variable). Could be a bit smarter, but for most "normal" scenarios, it should be fine.

Extract procedure

This is one of the most important refactorings: take logic out of your executable section and "hide" it inside a local procedure – or in a schema-level procedure. So I highlighted a line of code and choose this option. I was then asked to provide the new procedure name:

After providing that name and pressing OK, I see the following:

Well...that didn't go very well. Notice that it grabbed a part of the next line (DBMS_SQL.PARSE), even though I didn't highlight it. Ugh. OK, I will do it again, with just the code from that single open_cursor line. The result looks much better; notice that the tool correctly extracted all local variables and arguments referenced in the code and now passes them in as arguments:

There is one big problem, however: the code will not compile, because the subprogram was inserted before the l_cursor declaration and that is invalid:

Here's a nice touch, though: if your highlighted code references local variable that are used only in that highlighted code, PL/SQL Developer will move the declaration for that variable out of the main program and into the local module. In other words, from this:

to:

Conclusion: Disappointing overall. It's got the right idea and has been pretty-well thought through, but has at least two fairly serious flaws. Also, SQL Developer offers the option to define locally or store as a separate subprogram, which PL/SQL Dev does not.

Extract local constant

Here's what the help doc says about this one:

"If a certain expression should be converted to a local constant, you can select it and provide the constant name. A local constant will be created within the current subprogram, of a type that is determined from the expression. All occurrences of the expression in the current subprogram will be replaced by the constant name."

All righty – let's try it out. I will create a constant for the call to the open_cursor function:

And the result:

PL/SQL Dev did a good job of not including the ; from the highlighted text in the value assigned to the constant. It correctly replaced the call to the function with the named constant. It did not, however, come up with the right datatype for the constant. Instead, it seems to have simply added up the number of characters in the selected text. That's just weird!

Next I will highlight the local variable and change it to a constant:

The result is not great:

It seems to me that it should have left the original declaration of l_cursor alone. The result is code that will not compile.

Conclusion: marginally useful refactoring.

Extract global constant

PL/SQL Dev help tells us the following about this feature:

"If a certain expression should be converted to a global constant, you can select it and provide the constant name. A global constant will be created within the current package, of a type that is determined from the expression. All occurrences of the expression in the current package will be replaced by the constant name."

So what does this do? Well, if I use it inside my procedure, it acts the same as with a local constant extraction. I would have thought it should prompt me for the package name and either move the variable to that package or create a new package with that name.

Next I tried in a package:

and it did the right thing, sort of:

It still does not work properly with datatypes, though. It just defines them as VARCHAR2 all the time.

Conclusion: same as for local constant....minimally useful.

Swap assignment

A very minor "refactoring". Use it to change an assignment of this form:

a := b;

b := a;

It's fairly smart. You don't have to highlight the entire assignment statement, just a part of it, and PL/SQL Dev will move everything on the right to the left.

Conclusion: not really a refactoring, but it could be a useful accelerator on occasion.

Replace assignment with initialization

The help doc says:

"If a local variable assignment is purely for initialization, you can move it to the declaration of the variable. You can right-click on a statement or select multiple statements."

So I started with this code:

When I clicked on the variable name, the identifiers lit up:

I then chose my refactoring option

and ended up with this:

That's nice...though I have one concern and one desire:

Concern: the change is valid, but it could cause problems. When an exception raised in the declaration section, it cannot be handled within that block's exception section. The exception always propagates unhandled to the outer block. So making this change could affect program behavior.
Desire: This would be even nicer if it was combined with the option to move the assignment to an initialization procedure not simply to the declaration statement.

Conclusion: mildly useful, but sometimes not desireable due to the way exception propagation works in PL/SQL.

Overall Conclusion

Refactoring in PL/SQL Developer is minimal, but useful. It's done in a fairly smart way. Now if they would only give me a key stroke to pop up the refactoring menu, rather than having to make my way down through two levels of right click menu options.

Steven Feuerstein's Blog

Automatic refactoring in PL/SQL tools – Part 2