I have long been attracted to the idea and process of refactoring (explained below). It is now very exciting to see that automated refactoring features are working their way into tools for PL/SQL development. I will analyze these features over the next few entries in my ToadWorld blog, starting with SQL Developer and then moving on to PL/SQL Developer, and finishing up with SQL Navigator and Toad.

 

Oracle recently released Version 1.5.1 of SQL Developer, its free development tool for PL/SQL developers. I have been watching SQL Developer since Oracle first brought it into the world, seeing as (a) I am obsessed with PL/SQL; and (b) Oracle has had a checkered history when it comes to producing decent development tools for us PL/SQL developers; and (c) I work for Quest Software and of course we are keen to see how SQL Developer stacks up with Toad and SQL Navigator.

 

My overall assessment of SQL Developer has been: it's a fine, clean, light development environment – with lots of room for growth and catching up. That is, for a free product that is relatively new, it is a nice effort. Not too surprising, since it leverages the JDeveloper framework, into which Oracle has invested lots and lots of resources.

 

Would I use it instead of Toad or SQL Navigator or a number of the other tools that have been out there for awhile? Not unless my budget consisted of $0 for PL/SQL development tools. As with any piece of software, it will take time for SQL Developer to mature and fill out its feature set.

 

The SQL Developer team is certainly not standing still, however, and with Version 1.5, they have added several new features. One feature that caught my eye in particular was support for automated refactoring.

 

" Refactoring? What's that?" some of you may be wondering. You can see lots of information at http://www.refactoring.com/, but briefly (and from that site):

 

" Refactoring is a disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior. Its heart is a series of small behavior preserving transformations. Each transformation (called a 'refactoring') does little, but a sequence of transformations can produce a significant restructuring. Since each refactoring is small, it's less likely to go wrong. The system is also kept fully working after each small refactoring, reducing the chances that a system can get seriously broken during the restructuring."

 

The bottom line is that it is a highly structured methodology for going through clearly defined steps (each ideally verified by a pre-defined regression test) to make your code more maintainable.

 

Automated refactoring is a process that applies well-established and accepted refactorings automatically to your code. IDEs for object oriented languages have offered automated refactorings, albeit very simple ones, for a while. Now, SQL Developer offers this capability.

 

Exciting! So I decided to take a look. After installing SQL Developer 1.5, I opened up a program definition in an editor window, highlighted some code and checked out the right click menu. There it is!

Interesting....but what are they? I dipped into Help just to see what I could find about these and found....very little. Just a window on Extract Procedure. So let's start with that first. SQL Developer tells me:

 

"This dialog box is displayed if you are editing a procedure, select one or more PL/SQL statements, right-click, and select Refactoring, then Extract Procedure. The selected statements are encapsulated into the procedure to be created.

 

"Defined Locally: For a standalone procedure, defines the newly refactored code in the definition section of the original procedure.

 

"Stored: For a standalone procedure, defines the newly refactored code in a new standalone procedure.

 

"Name: Name of the procedure to encapsulate the selected statements. For a packaged procedure, the newly extracted procedure text is placed immediately after the current procedure."

 

Now, I really like this. I do. I believe that encapsulation or information hiding is one of the most important factors in writing maintainable code. And I believe in particular that we should make extensive use of locally-declared modules to do this.

 

So I highlight the line of code I want to encapsulate (the open cursor statement). I then, for starters, ask to create it as a schema-level procedure: 

Type in the name, press OK, and I see this:

So far...OK, but just OK. Notice that it does not recognize that my line of code references a parameter from the main program. It would seem to me that the refactoring process should automatically create an argument for this procedure. This code isn't even valid. But I press Yes, and the box goes away, fine, but then I see that the original code is still present in my procedure:

That's no good – I wanted to replace it with the new stored procedure. I check the browser and find that this procedure has not been created in my schema:

 

Now maybe that's because there was an error in the code (un-declared referenced to security_level_in), but I didn't get any notification. OK, I will try it again with code that will compile, but still I see nothing. Well, I have no idea what is going on here, but really what I want to do is create a local module, so let's try that.

 

I highlight the code, choose my refactoring option, choose Local:

I type in the name of my new local module and press OK. I then see this:

Ugh. That's no good. It's supposed to be a local module. SQL Developer has placed this code above and outside of the use_cursor procedure. I then have to move it inside to make it work.

 

Bottom line, SQL Developer developers: nice idea, but your implementation leaves much to be desired. I don't see how I can use this to help me develop more modular, maintainable code any more easily than if I cut and paste myself.

Let's check out the other refactoring options. "Rename Local Variable..."

Hey, I like the sound of that! All too often we come up initially (in the heat of development) with bad variable names like "d" or "l_integer." Yuch! So the ability to be able to provide a new name and have the product sweep through my code and replace all instances...well, that would be pretty darned cool. Let's give it a try; in the program below I reference l_cursor four times:

There's no help for this feature, so I figure I will highlight the variable name and then specify a new one that indicates that this is a dynamic cursor:

Press OK, and it worked very nicely:

Excellent! Now I will add a reference to a package variable with the same name:

and try to do another renaming. This last addition should not  be changed, as it is not the local variable. OK, I highlight the variable inside the code and try to change it, but I get this message:

Hmmm. I guess I need to highlight the declaration itself, though that shouldn't be necessary. OK, I do that, ask to rename variables to abc and sadly I see this:

So the rename feature is nice enough, but if it is not scope-aware (that is, if it cannot distinguish between locals, globals and even program names – yes, in another test, it even replaced the name of a packaged program that happened to have the same name as the local variable), then it really isn't doing anything better than a search and replace.

 

It also lets me change the variable name to something that is not a valid identifier, such as 123. If this feature is really directed at changing variable names, it should be aware of the rules and at a minimum put invalid names inside double quotes.

Finally, I tried to change a local variable's name from abc to something with about 100 characters in it and got this message:

Not sure what this means, but it certainly isn't helpful.

 

So I have to rate this feature a marketing-inflated misnomer. Sorry, SQL Developer developers!

And the third refactoring:

In conclusion, I am very pleased that the SQL Developer has introduced automated refactoring to the PL/SQL community. This is a concept and capability which is badly needed. The implementation in Version 1.5, however, leaves much to be desired.

Next/soon, I will take a look at PL/SQL Developer's refactoring capabilities.

1 comment(s) so far...

Re: Automatic refactoring in PL/SQL tools – Part 1 I have checked refactoring in PL/SQL Developer. I wrote a blog about it, which is avalable at http://bar-solutions.com/wordpress/?p=302 By Patch on   Sunday, December 14, 2008 8:35 AM

Login to Add Your Comment