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.

Showing Peers in a Connect by

I built a system internally a while ago and one of the most popular pages was one that showed a tree of data. We even got accused of caching the page because it was too dynamic and fast.  Here's what I did and what the output ended up like.

In SQL Developer:

 
In Application Express:  The leading   gets highlighted as part of the link but I'm sure there CSS wizards that could fix that.

And try it here

with q as (  select level lvl,empno
      from emp e
      connect by prior empno  = mgr
      start with empno = 7839   ) 
select SYS_CONNECT_BY_PATH(sub.ename,'/'),LPAD(' ',((18*(level-1))+1),' ') ||sub.ename,sub.empno,sal,comm,d.dname
from dept d,(
    select e.* 
    from emp e
    connect by prior mgr=empno
    start with empno = 7566

union 
    select e.* 
    from emp e
    where level = (select lvl from q where empno = 7566 ) 
    connect by prior empno = mgr
    start with empno = 7839
 union 
    select e.* 
    from emp e
    where mgr = 7566
  ) sub    
where d.deptno = sub.deptno  
connect by prior empno  = mgr
start with empno = 7839  

Once it breaks down it's quite simple.  The inner unions are
1) The parents of the interesting record
2) The peers of the interesting record
3) The children of the interesting record

Then put the outer connect by and you have what I got.

Externally Editing Blobs

Another new thing in this RC1 is the ability to easily edit blobs in the database.  We've had the save and load from file as well as the ability to show the text of the blob but now we're interrogating the blob to figure out what it is.  The code is starting with what should be fairly common file types and we'll expand as we go.  There's a new button on the BLOB view dialog named External Editor.  When you press this, you may get prompted to go define an editor for this type.  Here's what it looks like for XML stored in a BLOB.

Defining the editor is located in the preferences.  I'm going to setup VIM as the editor of choice for all *.xml files. 

Now when I press the External Editor, gVIM launches. What is happening is that there is a temp file created and called via command line from SQL Developer.  The launching of the external tool is monitored so that when that tool is closed the temp file is re-loaded into the database into the BLOB content.

Which is why you'll see a * next to the row indicating that there was a change made and it's awaiting a commit or rollback.

 

Now editing images,documents,xml stored in the database is greatly simplified.

Apex Listener EA2

A new build of the Application Express Listener was just loaded.  This new version adds lots of new features. Check out the Configuration in the install guide.  Here's a screenshot of some of the new options available.

Another new thing is in the log file.  Every 1000th request I print some statistics to the log file.  The statistics include uptime, processing time , min/max/avg/median times for the requests.  Here's an example of those statistics where the listener has been up for 9 hours for a total of 16 minutes of processing time to deliver 79.1M of data.


Current Time:    Wed Dec 02 10:06:02 PST 2009
Server uptime:   0 Days 09:01:32.126
Total accesses:  1.95K
Total Traffic:   79.1M
Total Processing Time:    0 Days 00:16:52.965
Min/Average/Median/Max Processing Time:    6    506    308    12057ms
Min/Average/Median/Max DB Time:    0    288    205    9077ms
Active Requests:    0

1/3 active/available in cache apex

Realtime SQL Monitoring

There was some a new feature added to the realtime SQL monitoring in the latest SQL Developer 2.1 staged on OTN today.  This little feature adds the ability to save the reports from the real time sql monitoring.

First to invoke the screens, goto the Tools->Monitor SQL...

Next you'll get a list of the SQL that has been monitored.  In case your new to this feature of 11.1, this is any SQL that runs over 5 seconds and it's history is limited to 20 statements per CPU the database is running on.

To get to this screen right click on the grid of running SQL and choose "Show SQL Details"  This will show the state of the SQL statement you choose.  The green arrows in this screen shot indicate that this current step of the execution plan the statement is on.  This is VERY handy to determine if the SQL is nearing the end or just beginning.  This has all been in the tool for a while now.  The new part is highlighted in ORANGE.  It's a simple save button to save this screen to an HTML file.

Once you save the file and open in a browser, you'll get a saved version of that same screen.  You can then mail or post this resulting file where the person responsible for the SQL can get it and address an issues.