Steven Feuerstein's Blog

Oracle PL/SQL Programming 5th Edition - "Early Warning"

Steven Feuerstein — Wed, 10 Jun 2009 18:19:00 GMT

I was on the phone with a Quest Code Tester user the other day and he asked me if I was working on the next edition of my original "magnum opus", Oracle PL/SQL Programming, which would include Oracle11g features.

I cannot describe to you how good it felt to say: "No, I'm not working on it. I'm finished working on it."

In fact, just last week, my editor (Debby Russell) sent all the chapters to the production department, so that they could begin the long process of converting Word docs into an actual, publishable book.

This fifth edition will cover all features of Oracle11g, through Release 2 - which means it will not be published until the fall, probably at the same time as Oracle Open World (week of October 12), or whenever Oracle formally announces availability of Release 2.

We have also added a single, very extensive chapter on optimizing performance of PL/SQL code.

You can see more information about the book and even pre-order the book at:

http://oreilly.com/catalog/9780596514464/#top

But you may want to hold off a little bit on that pre-order. I am considering offering the ability to order the book directly from me, and have it signed whatever personalized message you request. So....you will hopefully see an update about that soon!

Naming Conventions and Coding Standards, the Feuerstein Way

Steven Feuerstein — Thu, 28 May 2009 17:32:00 GMT

I am often asked about the naming conventions and coding standards that I use. My answer is usually a combination of muttering and vague statements and hand-waving.

That's because I have a confession: I don't have a single naming conventions and coding standards document that I use. Why not? Because I am a software developer! That is, I feel (well, I am) very busy, overwhelmed by deadlines. I don't feel like I have the time to stop and write down the few rules that I might actually follow more of less consistently. And it seems that a part of me is somewhat resistant to being accountable to a standard. Sound familiar?

Sigh...unfortunately (no, make that very fortunately), lots of other PL/SQL developers look to me for advice about how to "do things right." Ah, the pressure, the pressure!

Ah, the hypocrisy. That's what it really comes down to: I am a big hypocrite. I routinely violate just about every best practice I push on others. I sound high and mighty, but when I write code, I feel the same pull to do the quick and dirty.

So I've decided to take a few moments out of my various plane rides (I am now - when I started this document, anyway - flying from Manchester, UK, to Sweden to do a presentation to the Stockholm OUG, then a couple of days of customer visits in Stockholm and Milan, then two days of training for Oracle in Helsinki) to jot down what have become my de facto coding standards.

You see, I do have standards, and I even follow them, more or less. I just don't like to bother writing them down.

So that's what (finally) you'll find here. It's a combination of the naming conventions I follow, certain naming conventions with which I disagree, and a variety of other suggestions. I hope you find it useful. I'm not going to make much of an effort to justify my approach or explain away the inconsistencies. I'll just show you what I do and you can decide for yourself if you'd like to use my path as a model for your own journey.

I also hereby declare this to be a work in progress. So...

If you have disagreements with my approach, let me know. Perhaps I will agree with you, and change my document (you will get the credit).
If you have a suggestion for something to add to the document, send it to me. If I like it, I will put it into the document (and give you credit).
If you would like to offer your own set of naming conventions and coding standards to the world of PL/SQL developers, I will add it to the webpage.

The URL for my standards document is: www.ToadWorld.com/SF/standards

A Classic Error in My Monthly Puzzle!

Steven Feuerstein — Mon, 27 Apr 2009 15:47:00 GMT

Every month, a Toad World newsletter goes out to thousands of Toad users and it includes a monthly puzzle that I write. Last month's puzzle went like this:

Which of the following blocks does not contain an infinite loop?
A.
DECLARE
   l_line VARCHAR2(32767);
   l_file UTL_FILE.file_type :=
      UTL_FILE.fopen ('C:\temp', 'my_file.txt', 'R');
BEGIN
   LOOP
      UTL_FILE.get_line (l_file, l_line);
   END LOOP;
END;

B.
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
   END LOOP;
END;

C.
DECLARE
   l_keep_going BOOLEAN;
BEGIN
   WHILE (l_keep_going)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
      l_keep_going := SYSDATE IS NOT NULL;
   END LOOP;
END;

D.
BEGIN
   <>
   DBMS_OUTPUT.PUT_LINE ('abc');
   GOTO start_of_block;
END;

I told my friends at Toad World that the answer was (A), because UTL_FILE.GET_LINE raises the NO_DATA_FOUND exception when you read past the end of the file. Since files cannot be of infinite size, you will eventually read to the end of the file and then the loop will terminate.

And I wiped my hands of that puzzle. Unfortunately, it came back to haunt me in the form of a message from the Toad World editor-in-chief:

"Steven, normally I don't worry when I get just a few responses to the puzzler with answers different from what you’ve told us is the correct one, but this month I’m wondering if we missed something because we’ve had close to 50% of the respondents picking the wrong answer (C), and explaining their choice as follows: The value of l_keep_going is not initialized to TRUE and is therefore unknown. It will not enter the loop and therefore the loop is not infinite."

Oh my, oh my. They are so right. And I was so sloppy. And I was so over-confident, that I did not ask anyone with PL/SQL experience to proof my Q&A.

Let's take a closer look at choice C:

DECLARE
   l_keep_going BOOLEAN;
BEGIN
   WHILE (l_keep_going)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
      l_keep_going := SYSDATE IS NOT NULL;
   END LOOP;
END;

I declare a variable that I will use to determine termination of the loop, but I neglected to provide a non-null default value. As a result, the WHILE condition fails immediately, the loop body never executes even once – and certainly not an infinite number of times!

This kind of bug often manifests in this form as well:

DECLARE
   l_counter PLS_INTEGER;
BEGIN
   LOOP
      do_something;
      l_counter := l_counter + 1;
      EXIT WHEN l_counter = 100;
   END LOOP;
END;

Again, I have failed to initialize my counter (likely it would be set to 1) and in this case the result is an infinite loop.

Hey, that must have been what I meant to use as choice C! Yeah, right.

So the lessons to be learned are:

Anyone can and everyone does make mistakes. Including the self-named PL/SQL Evangelist.
Code review is a critical component of high quality development. All critical code (and what isn't?) should be looked at by another human being before it goes "live."

An interview with Steven Feuerstein

Steven Feuerstein — Thu, 23 Apr 2009 01:06:00 GMT

In case you simply can't get enough of Steven Feuerstein (no, I don't generally talk about my self in the third person or in the royal "we"), I invite you to check out this interview. I had an awful lot of fun answering the questions, and you might be entertained reading them.

A handy string parsing utility - extended

Steven Feuerstein — Mon, 13 Apr 2009 16:57:00 GMT

It's truly one of the oddities of the PL/SQL language that it does not offer a delimited string parsing program. The closest we can get is DBMS_UTILITY.COMMA_TO_TABLE, and that is sadly deficient (it only parses comma-delimited strings and each item between the commas must be a valid PL/SQL identifier).

So I built one myself (the parse package) and put it in the demo.zip file that accompanies my training materials. You will find it in the parse.pkg file. For the past few years it has consisted of a single function that accepts a delimited list and returns a collection of the items in the list.

On a flight from London Heathrow to Istanbul in April 2009, though, I decided to make this package a bit more useful. I added two overloadings of the string_to_list function so that you can now easily parse (1) a string that contains multiple lists and (2) a string that contains multiple lists, each of which has a name.

So I have updated the demo.zip download to include this new version, and I thought I would bring it to your attention. The demo.zip contains about 2000 files and there is no index. So it's just a tad bit difficult to find the useful programs within it.

I have also created a Quest Code Tester for Oracle test definition to verify the behavior of these functions. If you would like to check that out, install Quest Code Tester 1.8.3 or higher (available as a beta here until mid-May 2009), and then import the Q#PARSE.qut file (also in demo.zip).

Here are examples of using the three different overloadings of parse.string_to_list:

DECLARE
   l_list   parse.items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d',',');
END;
/

DECLARE
   l_list   parse.nested_items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/

DECLARE
   l_list   parse.named_nested_items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/

The third overloading, which returns a collection of type parse.named_nested_items_tt, also offers a demonstration of the elegance and usefulness of multi-level and string-indexed collections.

I hope you find the parse package useful.

And I hope that someday we can happily deprecate it, for the very good reason that PL/SQL finally offers one of its own.

Run an OS command from within PL/SQL

Steven Feuerstein — Fri, 27 Mar 2009 13:14:00 GMT

Developers are hard people to satisfy. Oracle gives us this incredibly robust, powerful and easy to use database programming language – and all we can do is complain about what it doesn't do for us.

Well, that's reality for you: PL/SQL is powerful and robust and easy to use, but also very narrowly focused. So if you want it to do something outside of its area of expertise, sometimes you have to jump through a few more hoops than you'd like.

A good example of this is figuring out how to run an OS command from a PL/SQL block. There is not, unfortunately, a native "execute host command" statement within PL/SQL . Instead, Oracle offers (at least) three ways to do this:

Use the DBMS_SCHEDULER package
Run a Java method to execute your command.
Run a C program to execute your command.

DBMS_SCHEDULER offers the simplest and most intuitive solution, especially from the standpoint of DBA and system administration tasks. I talk about this solution on the OTN Best Practice page:

http://apex.oracle.com/pls/otn/f?p=2853:4:1544539351000407::NO::P4_QA_ID:16282

I will, in this Toad World blog, offer brief guidance on the other two options. It is very unlikely that you will be able to rely solely on the content below to implement either approach. Instead, I suggest you use this as a starting point and then from there visit the detailed Oracle documentation and/or the full chapter coverage of both these techniques in my Oracle PL/SQL Programming.

Executing an OS command with Java

With the Java approach, you will take these steps:

Find the Java class that implements host command execution.
Build a class that invokes that host command method. Let's call it HostCommand.
Build a PL/SQL program that calls a method in HostCommand to run your command.
Acquire the privileges needed to execute host commands via Java in the database.

Let's go through each of these steps.

Find the Java class that implements host command execution.

Objects of the java.lang.Runtime class include an exec method that will execute a host command.
Build a class that invokes that host command method. Let's call it HostCommand.

Here's code to create a new Java class in the database to invoke this command for the Windows XP operating system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLcmd"
AS import java.lang.Runtime;

public class execHostCommand
{
public static void execute (String command)
    throws java.io.IOException
{
   String osName = System.getProperty("os.name");
   if(osName.equals("Windows XP"))
       command = "cmd /c " + command;
   Runtime rt = java.lang.Runtime.getRuntime();
   rt.exec(command);
}
}
/

You can easily modify the execute method to support other operating systems based on the value of osName.
Build a PL/SQL program that calls a method in HostCommand to run your command.

CREATE OR REPLACE PACKAGE host_command IS
PROCEDURE execute (cmd IN VARCHAR2) AS LANGUAGE JAVA NAME
'execHostCommand.execute(java.lang.String)';
END;
/

Notice that I "map" the VARCHAR2 datatype to the java.lang.String class in my call to the new Java method.
Acquire the privileges needed to execute host commands via Java in the database.

You need special privileges to execute host commands from within the database through Java. Otherwise when you try to execute your command you will see an error like this (the error message will vary depending on what you are trying to do):

One way to obtain these privileges to have the JAVASYSPRIV role granted to your schema. This role contains all the privileges you need (and more).

For a more nuanced approach to granting the required privileges, you can also use the Java security API available in the Oracle database. For example, if I want to delete a file using a host command (perhaps it is not accessible through UTL_FILE), I will need to grant the following privileges to the schema in which the command is executed, such as HR:

BEGIN
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.io.FilePermission'
                             , '<>'
                             , 'execute'
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'writeFileDescriptor'
                             , ''
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'readFileDescriptor'
                             , ''
                              );
END;
/

Executing an OS command with C

To use C, you must define an external procedure and then invoke it within your PL/SQL block. It is not possible within this article to cover completely all the steps and issues involved in setting up such an external procedure. I will, instead, cover the highlights. For the full details, check out Oracle documentation at:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_extern_proc.htm#ADFNS010

and also read Chapter 27 of Oracle PL/SQL Programming, 4^th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help your database administrator to supply the privileges needed to execute your host command in C.

Here are the steps to follow with C:

Find (or build) the C program that implements host command execution.
Save the C source to a file and generate a shared library for it.
Save the library file where Oracle can find it.
Define a library inside Oracle that is associated with the shared library on disk.
Create a PL/SQL wrapper for the C function.

Let's go through each of these steps.

Find (or build) the C program that implements host command execution.

The C system function executes an operating system command. So I build a simple C function, extprocsh(), that accepts a string and passes it to the system function for execution:

int extprocsh(char *cmd)
{
return system(cmd);
}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.
Save the C source to a file and generate a shared library for it.

I save the source code in a file named extprocsh.c. I then use the GNU C compiler to generate a shared library. On a 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, the following compiler command worked to create a shared library:

gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so

Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:

c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll
Save the library file where Oracle can find it.

These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. The default locations for Windows and Unix respectively are:

$ORACLE_HOME/bin
$ORACLE_HOME/lib

If you want to use a non-default location, you will need to edit the listener configuration file and supply path value(s) for the ENVS="EXTPROC_DLLS...".
Define a library inside Oracle that is associated with the shared library on disk.

After copying the file and/or making adjustments to the listener, you will then define a "library" inside Oracle to point to the DLL. For example:

CREATE OR REPLACE LIBRARY extprocshell_lib
   AS '/u01/app/oracle/local/lib/extprocsh.so';   -- Unix

CREATE OR REPLACE LIBRARY extprocshell_lib
   AS 'c:\oracle\local\lib\extprocsh.dll';      -- Microsoft

Note: performing this step requires Oracle’s CREATE LIBRARY privilege.
Create a PL/SQL wrapper for the C function.

Now I can create a PL/SQL call specification which uses the newly created library:

CREATE OR REPLACE FUNCTION exec_host_command (cmd IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   PARAMETERS (cmd STRING, RETURN INT);
Then, assuming that your DBA has set up the system environment to support external procedures, the exec_host_command function can now be called anywhere you can invoke a PL/SQL function.

Note that these operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process.

Automatic refactoring in PL/SQL tools – Part 3

Steven Feuerstein — Mon, 09 Mar 2009 15:18:00 GMT

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.

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.

Automatic refactoring in PL/SQL tools – Part 1

Steven Feuerstein — Tue, 09 Dec 2008 19:23:00 GMT

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:

This lets you toggle between different case formats. It seems to work well enough. Honestly, however, I believe that it is not appropriate to include something like this under the Refactoring banner. It is a simple key driven formatting feature.

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.

Congratulations to the newest Oracle Magazine PL/SQL Developer of the Year!

Steven Feuerstein — Mon, 03 Nov 2008 15:03:00 GMT

As I travel certain parts of the globe doing presentations on PL/SQL, I meet many extremely talented PL/SQL programmers – people who meet the most complex challenges you can imagine with a deft combination of intense creativity, hard work, and of course Oracle PL/SQL.

Oracle Magazine names only one as the PL/SQL Developer of the Year at each Oracle Open World, and this year the award was given to Alex De Vergori of Betfair. Oracle Magazine writes:

Developer works with 250,000 lines of PL/SQL code that deliver success.

Successful PL/SQL developers require more than just good technical knowledge, according to Alex De Vergori, Oracle Magazine’s PL/SQL Developer of the Year.

“They also need fine attention to detail, a passion about what they’re doing, and a high degree of pride in the software that they’re producing,” says De Vergori, a database architect who channels those skills at Betfair, an online gaming company that pioneered the concept of betting exchanges in 2000.

To manage customers’ bets, De Vergori and the Betfair team have created a core betting engine and business logic that encompass more than 250,000 lines of PL/SQL code. The Betfair solution runs on Oracle Database using distributed databases around the world and an Oracle data warehouse running Oracle Real Application Clusters. Betfair also uses Oracle Coherence for its in-memory distributed data caching requirements, Java for its middle tier, and Ajax for its front-end applications.

“We take in excess of five million transactions a day, and they all go straight through to the database, where those bets match with other transactions in real time,” says De Vergori. “At peak, we can easily see something like 1,000 bet transactions per second. With Oracle, our growth has been exponential, so that has enabled us to succeed in our market.”

Click here if you would like to read more details about how Betfair is using PL/SQL.

Alex, welcome to the "select circle" of PL/SQL Developer of the Year awardees. Now, finally, you understand why you were spending all those long days, nights and weekends writing PL/SQL code!