Month: October 2006

SQL Developer Wish List

I’m starting to come around to Oracle SQL Developer. At home I only use free software so that’s obviously a big plus, but at work I’m still using PL/SQL Developer (Allround Automations) and SQL*Plus.

These are the features I like best about these products:

SQL Developer:

  • Connection management
    • Connection browser
    • Multiple simultaneous connections
    • Password persistance
    • Quick connection switching within SQL worksheet
  • Object viewer
    • Easy Query management
    • Nicer plan viewer

PL/SQL Developer:

  • Faster startup, smaller memory footprint
  • Fully configurable object browser
    • Re-order categories
    • Create/modify/delete categories
    • Colour coding
  • Data browser
    • Edit data directly in grid
    • View data, including LOBs, e.g. RTF, XML, HTML, hex, etc.
  • Window management
    • Summary tab lists all windows, indicating which are unsaved or are currently running SQL
  • Query management
    • Runs DDL and DML in a second session, easy to cancel queries
  • Smart SQL and PL/SQL Editing
    • Context-sensitive menus
    • Smarter, context-sensitive code suggestions
    • Configurable SQL beautifier
  • Session browser, configurable

I’d like to see Oracle work on some of these features before I switch over at work. I’d also like to see SQL Developer able to export LOBs when exporting tables to XML.

Lost SQL Developer Connections

I upgraded Oracle SQL Developer from to, just for the heck of it. Unfortunately, all my saved connections had disappeared! After a text search I found the connections are stored in a file called IDEConnections.xml under the folder sqldeveloper\jdev\system\oracle.onlinedb. I copied this across to a new folder that had been created (oracle.onlinedb. and bingo they’re back again.

Bonus – now I know what file to back up if I want to restore my connections later on.

APEX Tip: Page Auto Refresh

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with Apex version 2.2 but should work on earlier versions of Apex or HTMLDB.

  1. Create an Application Item to store the current refresh interval (e.g. F100_REFRESH_INTERVAL)
  2. Create an Application Computation to initialise it (e.g. F100_REFRESH_INTERVAL_COMP):
    • Computation Item = F100_REFRESH_INTERVAL
    • Computation Point = On New Instance (e.g. On Login)
    • Computation Type = Static Assignment
    • Computation = (default number of seconds, e.g. 60)

  3. Create a static List of Values (e.g. REFRESH_INTERVAL):

Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)

Now, for each page you wish to have auto-refreshed, edit the Page Properties:

  • HTML Header = <meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">

Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):

  • Display As = Select List with Submit
  • Region = (any region, I prefer the Breadcrumb region if one exists)
  • Label = Refresh
  • Source Used = Always, replacing any existing value in session state
  • Source Type = Item (application or page item name)
  • Maintain session state = Per session
  • Source value or expression = F100_REFRESH_INTERVAL
  • List of Values definition = REFRESH_INTERVAL

I also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:

  • Source Used = Always, replacing any existing value in session state
  • Source Type = PL/SQL Expression or Function
  • Maintain session state = Per session
  • Source value or expression = TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')

Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.

You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.

If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.

AUSOUG 2006 Perth Conference update

Noticed that the final conference programme is now available. Unfortunately I don’t have a laptop so I can’t attend some of the hands-on sessions.

I’m planning to at least attend the following:

  • Future of Database Technology (Barry Matthews)
  • How Can I Tune It When I Can’t Change the Code (Penny Cookson)
  • Being a Successful Developer (Connor McDonald)
  • Next-Gen Self-Managing Database (Barry Matthews)
  • Next-Gen Oracle Database Availability (Barry Matthews)
  • Odds and Ends (Connor McDonald)

Unfortunately, the schedule precludes me from Systematic Oracle Performance Tuning (Guy Harrison).

From the Delegate Program:

“…a smattering of such features and/or techniques Connor has encountered over the years, that he has either created himself, read about, or simply stolen from someone smarter.” (Odds and Ends, McDonald)

– plagiarism is the sincerest form of flattery… :)

“Oracle recently announced the future obsolescence of SQL*Plus GUI version… [SQL Developer] allows easier access for less technical users… The session will include basic SQL syntax training so that End Users can utilise the product to access their data.” (SQL Developer Hands-on Workshop, Kate Marshall)

– I personally have never come across any end-users who currently use SQL*Plus; I have, however, worked with some business analysts who can customise simple SQL using a helper like Discoverer; perhaps they’d be the kind of person who would benefit from this session. Looks like Chris Muir is giving a similar talk in Melbourne, although for some reason it’s in the “Applications” stream.

“Since Oracle introduced the shared pool in the Oracle kernel, it has been causing problems… these problems are because of Oracle bugs, …This presentation will have a closer look at what application developers do wrong.” (The Life of a Cursor and Its Impact on the Shared Pool, Anjo Kolk)

– hey, why should we expect presenters to sugar coat their topics?

Most interesting presentation title: Guaranteed Project Failure
Runner-up: Make Money Fast and Improve Your Love Life with Oracle…
Most boring presentation title: Off shoring Oracle Support: Alcoa’s journey …but the presentation itself will be interesting I’m sure :)

Learning APEX

I’ve been enjoying learning Oracle APEX the last few weeks, and have started to appreciate the depth of the product – there’s more than meets the eye. At first I was dependent on the wizards (of which there are many) for just about everything except SQL and PL/SQL. When I came across problems I’d just delete and re-create the entire page – but now I’m more often able to find the source of a problem and fix it. I’ve found that having a good knowledge of SQL, PL/SQL, HTML and Cascading Style Sheets is very helpful when working with Apex.