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