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.

Apex Listener EA1

We pushed the first EA of the new listener out this week. Give it a try here.  I'll be watching the Apex forum here for question people may have.

Here's a few things I forgot to put into the doc.

1) In the zip is a sample configuration file, apex-config.xml . This contains a short comment on each parameter.

2) To change the default location of the config file OR the cache location. Edit the WEB-INF/web.xml and change the config.dir or cache.dir. These can be absolute paths /u01/klrice/ or environment variables such as ${MY_CONFIG_DIR}

3) The apex exporter and splitter are in the apex.jar file. Simply run "java -jar apex.jar" The splitter is joined into the export so you can do both in one command.  If you've not tried the splitter yet here the flags for it.

    -split                 : Split the export file up into smaller pieces and creates an install.sql to run them all

    -flat                   : Normal -split make a directory structure.  This option will keep it flat.

    -update              : If you export and split over the one that was already done, this will generate an update.sql which is only the files that changed. 

    -nochecksum     : Normally if a split is done over another one, the splitter does a checksum on the files to only touch the files that changed.  This is very handy if you want to check into a source control only the things that change.  So, if the only change from yesterday's export to today's is page 50.  Then only page50.sql will get updated.

Also there's a -zip that didn't make it into the usage which zips up the split files

Easier than ever local connections

I'm constantly trying to make things in the tool easier to get people started working with the database. The first step is always create a connection. You may wonder how can that get easier it's fairly simple as it is.

Well for a local database it is now easier than ever. I added a new context menu "Create Local Connections". When you have a local database it will automatically setup a connection for sys,system and any user which has an open account. So now in roughly 15 seconds you have a connection to each user in your local database. Then just simply open, enter the password and your up an running writing code. I plan on later extending this to something like "create connection based on connection X"

SQL Developer 2.1 EA1

There's tons of things in the new EA. The two biggest is the modeler is now integrated and the new unit testing. I'm going to do a few short demos of each and some of the things they offer. First the modeler not only allow you to open a model and see what was created in the full SQL Developer Data Modeler but it allows for drag and drop.

I'm going to try and do a bunch of short demos of some of the new things.

Here's the first, a very short demo of how to do it.

Apex performance week over week

I've looking into tuning the public apex.oracle.com. I could tell from the system standpoint that what we've done has been working. Now the real test is how is the end user's response times. I put a quick query together to attempt to measure that. Here's what I used which shows the trailing 24 hours pitted against the same time period 14 days ago.

select t.dt "Hour" ,t.cnt "Today Views" ,round(t.av,2) "Avg time",
      l.cnt "2 Week Ago Views",round(l.av,2) "2 Week Ago Avg" , 
      case when (t.cnt-l.cnt) > 0 then '<span style="color:green;">'||round((l.cnt/t.cnt)*100) ||'% ('||(t.cnt-l.cnt)||')</span>' else '<span style="color:red;">-'||round((l.cnt/t.cnt)*100) ||'% ('||(t.cnt-l.cnt)||')</span>' end "Count Diff" , 
      case when (t.av - l.av) > 0 then '<span style="color:red;">-'||round((l.av/t.av)*100)   ||'% ('||(round((t.av - l.av),2))||')</span>' else '<span style="color:green;">'||round((l.av/t.av)*100)|| '% ('||(round((t.av - l.av),2))||')</span>' end   "Avg Diff"
from ( 
select to_char(time_stamp,'HH24') dt,count(1) cnt,avg(elap) av
  from apex_030200.wwv_flow_activity_log
  where time_stamp > sysdate - 1
group by   to_char(time_stamp,'HH24')
 ) t,
( select to_char(time_stamp,'HH24') dt,count(1) cnt ,avg(elap) av
  from apex_030200.wwv_flow_activity_log
  where time_stamp between  sysdate -14 -1 and sysdate -14
group by   to_char(time_stamp,'HH24')
) l
where t.dt = l.dt
order by 1

Here's the results of the query when run for apex.oracle.com this morning. From these measure points, it appears what we're doing is working.

Apex Listener

After demoing at RMOUG using the listener, it seemed a good time to show where it's at. Here's a quick snap of the new way to configure it vs. mod_plsql's notepad/vi interface. You can probably tell from the screen which webserver I've been using to test it.

Forgot to show this at RMOUG today

I ran out of time and had a couple more things to show today. Here's the first that I ran out of time for.

The thing I meant to show was the ability to use the sql formatter to format your sql in the worksheet into a string for other programming languages. I'll show how to format for java here.

First here's the starting sql. You'll notice I even put in aliased column names quotes.



Now the preferences have to be changed for how to format. I'm going to try to add a simple right click -> Format to Java or something in the future but for now just change this preference.





Now a quick format.



And the sql query is properly escaped and ready to be copy-n-pasted into your favorite java code.