Application Express and Anonymous Blocks

Almost every application I've written in Application Express seems to end up with anonymous PL/SQL blocks.  In the best practices document , this is called out as something to avoid doing.  The document mentions reuse and reuse and manageability.  Manageability includes things like running your logic within a debugger or in a Unit Test as part of a regression test.  There's also one more thing that is not mention which is performance.  The performance impact has been reduced with the latest versions of the database however a stored procedure is faster. 

So, now introduce SQL Developer 2.1.  We added the ability to re-factor those anonymous blocks into a PL/SQL package.

Now when you right click on an application you get a new "Refactor (in bulk)" option.

When you choose this, the tool will work for a little bit trying to figure out things like binds for in and out, data types for those params, and so on.  You'll see a worksheet open and the code will start appearing in it as it's working.

Once it's completed, you'll see a package spec with comments on where that procedure came from.  This includes the page ID, page name, and process name.  These are to make it easy to find that process and replace it with the plsql call.  The comment also says "SUGGESTED CALL TO PLACE INTO THE PROCESS"  this is suggested because there could be many reason you want to alter the suggestion.

Now if you scroll down, the body of the package is there also.

Hopefully, this will give you a jump start on moving those anonymous blocks into store procedures.