Mood Swings
An interesting column comment encountered:
MOOD_SWINGS_IND VARCHAR2(1) (Y/N) “indicates that the person was in a swinging mood at the time of the episode”
AUSOUG Conference 2006 over
Another enjoyable conference. I’ve attended it every year since 2000 and this year’s was packed with presentations. Over 300 attendees spread over up to six presentations meant less crowding, I think; although as always there were a few packed sessions.
Highlights for me were:
- Connor’s “live demo” of 11g in Being a successful developer with Oracle
- Learning that cancelling a cursor is a good thing, but finding it can’t be done in PL/SQL – Anjo Kolk, The life of a cursor and its impact on the shared pool
- Trace Analysis on Steroids – Dave Moore’s talk was an eye-opener, not about performance-enhancing drugs (caffeine’s usually enough for me), but about Trace Analyzer. I thought this would be just another “if you’re not buying our product you’re wasting your time” talk, but in fact this is a totally free, open-source (PL/SQL!) tool that not only collates all the data from a trace file (as tkprof does), but also digs into your database to correlate the trace info with the data dictionary and present it all beautifully in HTML. Downloading and trying out this tool is definitely on my to-do list.
- Connor again, Odds and ends – an odd but arguably the best session to end the conference with. A number of tricks I’ll want to experiment with once I get a copy of the powerpoints (e.g. I wasn’t aware that contexts can be made “globally accessible”).
Thanks to the AUSOUG committee for another well-organised event.
Generic Audit
Looking at the column comments on this table, I can sympathise with the poor soul who after painstakingly describing 100 columns just decided that it just wasn’t worth it…
table audit_history (column comments in quotes):
id number(18) “Unique identifier”
aud_table_name varchar2(30) “audit record table”
audit_action varchar2(50) “audit action e.g. update”
aud_timestamp date “timestamp of the audit”
aud_user_id varchar2(100) “user performing the action”
aud_col_1 varchar2(4000) “audit column 1”
aud_col_2 varchar2(4000) “audit column 2”
aud_col_3 varchar2(4000) “audit column 3”
… (etc.) …
aud_col_99 varchar2(4000) “audit column 99”
aud_col_100 varchar2(4000) “audit column 100”
aud_col_101 varchar2(4000)
aud_col_102 varchar2(4000)
… (etc.) …
aud_col_139 varchar2(4000)
aud_col_140 varchar2(4000)
Tip: Don’t let anyone even think about using this kind of design for their change history auditing requirements. It might look elegant to them, but it is not good. Just, don’t.
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 1.0.14.67 to 1.0.15.27, 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.11.0.0.37.25. I copied this across to a new folder that had been created (oracle.onlinedb.11.0.0.37.36) 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.
- Create an Application Item to store the current refresh interval (e.g.
F100_REFRESH_INTERVAL) - 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)
- Computation Item =
- 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.
AUSOUG National Conference 2006
Just read the latest draft timetable, looks like there’s going to be some very interesting presentations this year. It looks a lot bigger than before, mainly because of the merger with Apps. I’m particularly looking forward to:
- “The future of database technology from Oracle Development” (Mark Townsend, Oracle)
- “Being a successful developer with Oracle” (Connor McDonald)
- “Systematic Oracle performance tuning” (Guy Harrison, Quest)
- “The next-generation self-managing database: a sneak preview” (Mark Townsend, Oracle)
- “Next generation Oracle database availability: a sneak preview” (Mark Townsend, oracle)
- “Odds and ends” (Connor McDonald)
My curiosity has been piqued on these ones as well:
- “Adding wings to your SQL development” (Howard Ong, Aurora Consulting)
- “AP Imaging – remaining a step ahead” (Jacinta Savage, Uni of Melbourne)
- “Guaranteed project failure” (Julie Harris, DFEEST)
Buzzword alert! 😉
- “Accelerating the journey to Fusion” (Roland Slee, Oracle)
- “Web Services, SOA & BPEL – a primer” (Basheer Khan – Innowave Technologies)
- “Understanding the Fusion technology stack: preparing for the future” (Tim Blake, Oracle)
Townsend features a bit more this year, and I’m sure McDonald’s presentations will be entertaining as usual. Penny’s only doing one session this year, however – mind you, that one could be a fairly involved hands-on session with Application Express.
Beware the wily SHOW_ALERT
What is the return value of SHOW_ALERT if the user doesn’t click one of the available buttons? According to the documentation (Forms 6i):
SHOW_ALERT
Displays the given alert, and returns a numeric value when the operator selects one of three alert buttons.
Syntax:
SHOW_ALERT (alert_id Alert);
SHOW_ALERT (alert_name VARCHAR2);
Returns a numeric constant corresponding to the button the operator selected from the alert. Button mappings are specified in the alert design.
If the operator selects… Form Builder returns
Button 1 ALERT_BUTTON1
Button 2 ALERT_BUTTON2
Button 3 ALERT_BUTTON3
So for example, we can use this in code like this:
DECLARE
btn NUMBER(2);
BEGIN
btn := SHOW_ALERT('MY_ALERT');
IF btn = ALERT_BUTTON1 THEN
-- Yes was chosen
ELSIF btn = ALERT_BUTTON2 THEN
-- No was chosen
ELSIF btn = ALERT_BUTTON3 THEN
-- Cancel was chosen
END IF;
END;
The alert can have two or three buttons, depending on whether you set the Button 3 Label property. For example, you could have an alert named MY_ALERT with the following settings:
Message: Do you want to save the changes you have made?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel
If the user clicks Yes, No or Cancel, the function returns one of the three ALERT_BUTTON constants. Simple enough. But what if the user decides to be different and clicks the “X” (close window button)? Alternatively, what if the user presses the “Esc” key on their keyboard?
Testing with Forms 6 (6.0.8.26.0) reveals that the answer depends on whether you have just two buttons defined, or all three. These results are probably the same on other versions, but you should test this on your version to make sure.
If you have three buttons, both the Close Window button and the “Esc” key cause SHOW_ALERT to return ALERT_BUTTON3. If you only have two buttons, the “Esc” key still returns ALERT_BUTTON3, for some reason, whereas the Close Window button returns ALERT_BUTTON2! The moral of the story is, make sure your code handles all three of the ALERT_BUTTON alternatives correctly! Also, make sure that the last button (2nd or 3rd) really does mean “cancel”. A very bad (hypothetical) example would be:
Message: What would you like to do?
Button 1 Label: Create a new record
Button 2 Label: Modify this record
Button 3 Label: Delete all these records
EDIT:
This post seems quite popular, so I thought I’d add what I think are good examples for handling alerts in Forms:
1. Two-button alert
Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: [blank]
DECLARE
btn NUMBER(2);
BEGIN
btn := SHOW_ALERT('MY_ALERT');
IF btn = ALERT_BUTTON1 THEN
-- Yes was chosen
ELSIF btn = ALERT_BUTTON2 THEN
-- No was chosen, or Close button clicked
ELSIF btn = ALERT_BUTTON3 THEN
-- ESC key pressed
END IF;
END;
2. Three-button alert
Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel
DECLARE
btn NUMBER(2);
BEGIN
btn := SHOW_ALERT('MY_ALERT');
IF btn = ALERT_BUTTON1 THEN
-- Yes was chosen
ELSIF btn = ALERT_BUTTON2 THEN
-- No was chosen
ELSIF btn = ALERT_BUTTON3 THEN
-- Cancel was chosen, or Close button clicked, or ESC key pressed
END IF;
END;