OOW - Apex evening

Well, everything got finally arranged... I just received the registration confirmation of my first Oracle Open World. I really liked my first stay in the United States; ODTUG in Washington... Now I'm looking forward to visit San Francisco and meet my online Oracle friends in "real life"!

I didn't manage to get a direct flight from Brussels to SF, so I'll spent some minutes in Chicago ;-)
On October 20, somewhere in the evening, I'll cross the streets of SF for the first time...
Almost all hotels near the Moscone Center were fully booked, so I'm staying in the "Best Western Grosvenor Hotel SFO", 380 South Airport Boulevard, South San Francisco, CA 94080.
Somebody else staying there too?

I'm currently looking what to do in the evenings. This is what I already planned:
- Sun 22/10 at 7pm: "Welcome Reception" at Yerba Buena Gardens and Howard Street
- Mon 23/10 at 7pm: "OTN Night (or OTN Underground)" at the Marriott Hotel, Yerba Buena Level and sumptuous Westin St. Francis in Union Square
- Tue 24/10 at 7pm: "OOW2006 Blogger Meetup", organized by Mark Rittman in the "Thirsty Bear Brewing Company", just around the corner from the Moscone. Afterwards there's also the Appreciation Event with sir Elton John.
- There will also be a "Belgium evening" together with Oracle Belgium, but that's not yet planned. Update: Benelux Cocktailparty - Mon 23/10 6pm-9pm
- John Scott and I are also organizing an "OOW APEX meetup" evening! You can subscribe here. The post in the APEX forum is here, initially started by Joel Kallman.

APEX patch 2.2.1

A patch for Oracle Application Express 2.2 is released on the 14th of September.

This release fixes several issues in Application Express 2.2, you find the details below. The full installation of Application Express 2.2.1 can be downloaded from the OTN Site. The Application Express 2.2.1 patch set, to be applied to an Application Express 2.2 instance, can be downloaded from MetaLink (Patch Number 5500033) and is about 2.6MB.

To install the patch:
- sqlplus "sys/syspass as sysdba" @apxpatch.sql
- Copy the images/javascript/htmldb_html_elements.js file to the images/javascript directory used for Application Express. The version of htmldb_html_elements.js included in the patch set should overwrite the existing file.

It took me less than two minutes to apply the patch and it worked great :-)

If you're working with Application Express 2.2 and XE, you should read this.

The table below contains a list of the bugs fixed with the Oracle Application Express 2.2.1.00.04 release.
Bug Nr Description
5441709 Substitutions Not Respected In HTML Form Element Attributes
5442085 Item Buttons Do Not Show On Page Definition Page
5459267 Substitution Message Is Not Displayed While Importing Application Export File.
5473520 Calendar Does Not Render Correctly When Day Link Contains Substitutions
5478895 Creating A Form And Report On A Table Does Not Respect Report Region Name
5442111 Creating Breadcrumbs Does Not Consistently Use Breadcrumb Region
5453208 Missing Indexes On Apex_Mail Log Tables
5463785 Reordering Report Columns Jumbles Show Checkbox
5485723 Post Element Text Is Not Included In A Page XLIFF Translation File
5485866 Unable To Delete Messages From APEX_MAIL_QUEUE
5496804 Some Item Types Apply Label Attributes To The Form Item
5499606 Misspellings In Apex Views Columns And Comments
5499948 Build Option Utilization Report Raises Exception
5503306 Page Radio Items With Names > 30 Characters Cause Page Failures In Some Cases
5412152 PL/SQL: Numeric Or Value Error In Supporting Objects When Switching Editor
5439073 Prerequisite Check For CREATE TYPE System Privilege Always Fails
5439502 WWV_FLOW_CLICKTHRU_LOG Missing Corresponding APEX_DICTIONARY Views
5452033 Display As Text (Based On LOV) Items Cannot Handle Non-Numeric Extra Values
5453543 Unable To Edit Parameters In Process Of Type Web Service
5469015 Authentication Fails On Popup Item Help Pages
5479160 Generate DDL Functionality Fails In 9iR2 Databases
5493470 Application Builder Page Definition Page Does Not Show Build Options
5494575 Named List Of Values In APEX_APPLICATION_PAGE_RPT_COLS View Is Always Null
5443026 Reordering Report Columns Does Not Adjust Sort Check Boxes
5518829 Unable to Use Date Picker on TIMESTAMP in tabular form

Updateable view

Although this feature already exist a long time in Oracle, I'd like to share my experience using "updateable views" together with APEX.

My situation
I've a table "Teams", a table "Team Groups" and a table "Team Team Groups" (which teams are in which groups - many/many relationship).

While I was inputting the Champions League teams I had to
1. input into table Teams
2. input into table Team Team Groups

To speed up the inserts in an APEX application, you wouldn't create two insert pages (one per table), but only one page which handles the inserts for you. You can for ex. write some (APEX form) processing which will insert the data into the two tables or tell all items to which table they belong and adapt the standard processing.

I didn't use any of these, I did the insert through a view... I built an APEX tabular form on top of the view and had my GUI very quick and easy!

An updateable view exists of a regular view and an "instead of" trigger...

My view
SELECT t.team_id, t.team_name, t.image_id, t.team_comment, t.is_active, g.team_group_id
FROM tdg_team t, tdg_team_team_group g
WHERE t.team_id = g.team_id;

My trigger
CREATE OR REPLACE TRIGGER tdg_vw_teams_groups_ioi_trg
INSTEAD OF INSERT ON tdg_vw_teams_groups
DECLARE
BEGIN
INSERT INTO tdg_team
(team_id, team_name, image_id, team_comment, is_active)
VALUES (:NEW.team_id, :NEW.team_name, :NEW.image_id, :NEW.team_comment, :NEW.is_active);
INSERT INTO tdg_team_team_group
(team_id, team_group_id)
VALUES (:NEW.team_id, :NEW.team_group_id);
END tdg_vw_teams_groups_ioi_trg;

Almost the same for Update and Delete...

To see if the view is really updateable, run this statement
SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'TDG_VW_TEAMS_GROUPS';

My insert statement
INSERT INTO tdg_vw_teams_groups
(team_id, team_name, team_comment, image_id, is_active, team_group_id)
VALUES (tdg_team_seq.NEXTVAL, 'Shakhtar', NULL, NULL, 1, 1011);

I found it very useful to speed up my inserts like this and this solution also works from SQL Plus, SQL Developer, etc.

I'm back!

After two weeks of honeymoon in Mauritius I'm back in Belgium...

We booked the One & Only Le Touessrok hotel. We can recommended Mauritius and this hotel in particular to everyone! We had a wonderful honeymoon and were treated as royalties!
We stayed in the Junior Suite 283 on the Frangipani Island. One word: "Luxe".
I can write pages of our stay, but I suppose this isn't interesting enough for everybody ;-) You find some more information and pictures of the hotel here.

Mauritius, for me, has two faces... The local people are not that rich and they don't have so much luxury as we know in Europe. They work hard on the fields (sugar, textile) or they fish to survive. Next to that you've the tourism. They literally created a "dream world": island, white beaches, crystal blue water, very nice weather, palm tries and they treat a tourist as a royalty! (tourism is an other important why of getting an income)

So, now a question for you! Any news in the Oracle world in the past two weeks?