Happy New Year & the 100th post!

A happy New Year to you all! I hope 2007 will bring all the best for you... and many thanks for visiting my blog the last year.

This post is also very special, as it's the 100th post on my blog! In one year one hundred posts.

My new toy - HTC P3300

Yesterday I got my new smart mobility, the HTC P3300. My first experiences with this phone/pda are great. I love it. The sound of the phone is very good and although the processor is slow, windows mobile 5 is working very fast. This pda is unique due to the "ipod-kind" of navigation with the rotating weel (HTC RollR). Some people hate it, but so far, for me it's working nicely. Windows Mobile 5 is far from being perfect as Scott Spendolini posted so nicely, but you can get the job done although it won't be in the most user-friendly way.

I still have my IPAQ HP 4700hx, which I used happily for two years. The processor of the HP is far more powerful, but it doesn't have an integrated phone or gps. I'm not sure I will still use it in the future.

I doubted between taking a phone with (for ex. HTC TyTN) or without keyboard. I decided to take one without as I'll primarily use the pda as phone, gps and to search through my agenda and contacts. That the HTC P3300 wasn't as heavy and big as a phone with keyboard was an extra. Of course today I saw a post about the HTC phones for 2007 in this road map. Maybe the follow-up of the of the new HTC TyTN can be interesting too... as usual in IT, the moment you buy something, the next day a new and better one sees the light ;-)

Nevertheless I wanted to blog about this nice pda as I've spent a whole day playing with it and I really liked it.

Follow-up comments with Cocomment

Did you already put comments on blogs? And did you find it also difficult to keep track of that reply or follow-up comments of others?
I saw some people came to my blog through cocomment and had a look what it was. It's a very nice utility to do exactly what I missed. It keeps track of the comments on the blogs you specify. You can find more information on the website of cocomment.

There's even a way to include it directly in your blog or in other words integrate cocomment as "one-click" for your visitors. Anybody did this with blogger? There're some example codes on the cocomment website, but I didn't try it yet.

DG Tournament updated with PW-Error Handling

Patrick Wolf and I exchanged some mails in the last weeks. I promised him to test his "ApexLib Framework" in my application.

As you can see on the picture, the package is integrated and works nicely. Patrick explains well how to integrate the package on his blog. I didn't follow the steps exactly like he wrote, for ex. I didn't make a region on page 0, but integrated a part in my template and a part in the page where I wanted to use the advanced error handling package. I also saved the javascript file as a static file in APEX.

You can't see this package working in DG Tournament for real, as I implemented this error-handling in the editor/admin part of the application. I didn't choose to implement it in the "Your Bets" page as I use (client side) javascript in that page to avoid "human"-errors. For ex. you can only type numbers in the score fields, so I don't need to check if a number was entered (and I avoid a round-trip to the server). But the picture shows you a nice error.

The ApexLib package is good, it shows very nicely what you can do and how you can improve APEX, but there's still room for improvement:

- check also if there's a number as value (now if you type 'A' the error isn't "right")

- more client-side scripting instead of server-side round-trips (but that also counts for APEX in general)

The page validation I used:

DECLARE
 vMin NUMBER(3) := 0;
 vMax NUMBER(3) := 99;
BEGIN
 FOR i IN 1 .. ApexLib_TabForm.getRowCount
 LOOP
  IF ApexLib_TabForm.hasRowChanged(i)
  THEN
   IF ApexLib_TabForm.NV('HOME_SCORE', i) NOT BETWEEN vMin AND vMax
   THEN
    ApexLib_Error.addError
   ( pError => 'The score of the home team has to be a number between %0 and %1'
   , pColumnName => 'HOME_SCORE'
   , pRow => i
   , p0 => vMin
   , p1 => vMax
   );
   END IF;
   IF ApexLib_TabForm.NV('VISIT_SCORE', i) NOT BETWEEN vMin AND vMax
   THEN
    ApexLib_Error.addError
   ( pError => 'The score of the visit team has to be a number between %0 and %1'
   , pColumnName => 'VISIT_SCORE'
   , pRow => i
   , p0 => vMin
   , p1 => vMax
   );
   END IF;
  END IF;
 END LOOP;
 RETURN ApexLib_Error.getErrorStack;
END;

* Update 30-Dec:

If you specify a format mask for your text field, the error is as expected. In my case I had to specify for HOME_SCORE and VISIT_SCORE format mask 90.

Flashforward 2007

I'm not sure what 2007 will bring for me...

On professional life the goal is to promote Oracle even more as before.
I also love Application Express, so that will keep a place in my heart too. I also hope to work more on myself and explore new, unknown sides of myself.

My wishes in my private life; most important: to stay happy... but if I'm allowed to wish for more, we hope to find a house and maybe start a family.

Flashback 2006

When I look over my shoulder I can be an happy man. For me, 2006 was a fantastic year, both professional and private.

My highlights:

• January, 18: Creation of this Blog and first post
• February, 25: First public adoration for APEX on this blog
• May, 30: Release of the World Cup 2006 APEX application to the world
• June, 6: Tom Kyte's post about my application
• June, 9: Start of the World Cup and the betting
• June, 17-23: First time in the United States and first ODTUG conference
• August, 11: Married with Kristel Geukens & honeymoon in Mauritius
• October, 11: Launch of follow-up WC 2006 app, named DG Tournament
• October, 21-28: First Oracle Open World in San Francisco (VS)
• November, 10: Took decision to go for a new challenge in 2007
• November, 15: Back to my old school to teach Oracle to new students
• December, 6: Became Vice President of the IOUG APEX SIG

Of course not everything ran as smoothly as expected and I had also some doubts, but nevertheless I'm very happy with a year like this.

Follow-up testing pre-tag with Blogger

I always had difficulties showing SQL statements on my Blog. At the blogger meet-up at OOW I talked with Eddy (Awad) about this and he told me to test the pre- and code-tag. So, finally, today I tested it (again) and apparently it works. Also thanks to Patrick Wolf to give me a css for the pre-tag.

I also noticed that Beta Blogger became Blogger (2). Great!

A statement without the pre-tag:

SELECT t.team_name, t.team_id FROM tdg_team t, (SELECT r.team_id, rank() over (PARTITION BY r.team_group_id ORDER BY r.points DESC, r.matches_won DESC, r.goals_difference DESC, r.goals_scored DESC) team_rank FROM tdg_match_rank r WHERE r.tournament_id = :TDG_AI_TOURNAMENT_ID) rt WHERE t.team_id = rt.team_id AND rt.team_rank IN (1,2);

A statement with the pre-tag:

SELECT t.team_name, t.team_id
  FROM tdg_team t,
      (SELECT r.team_id, rank() over (PARTITION BY r.team_group_id
                                      ORDER BY r.points DESC, r.matches_won DESC,
                                      r.goals_difference DESC, r.goals_scored DESC) team_rank
         FROM tdg_match_rank r
        WHERE r.tournament_id = :TDG_AI_TOURNAMENT_ID) rt
 WHERE t.team_id = rt.team_id
   AND rt.team_rank IN (1,2);

A statement with the pre- and code-tag:

SELECT t.team_name, t.team_id
  FROM tdg_team t,
      (SELECT r.team_id, rank() over (PARTITION BY r.team_group_id
                                      ORDER BY r.points DESC, r.matches_won DESC,
                                      r.goals_difference DESC, r.goals_scored DESC) team_rank
         FROM tdg_match_rank r
        WHERE r.tournament_id = :TDG_AI_TOURNAMENT_ID) rt
 WHERE t.team_id = rt.team_id
   AND rt.team_rank IN (1,2);

Testing pre-tag with Beta Blogger

A statement without the pre-tag:

SELECT t.team_name, t.team_id
FROM tdg_team t,
(SELECT r.team_id, rank() over (PARTITION BY r.team_group_id ORDER BY r.points DESC, r.matches_won DESC, r.goals_difference DESC, r.goals_scored DESC) team_rank
FROM tdg_match_rank r
WHERE r.tournament_id = :TDG_AI_TOURNAMENT_ID) rt
WHERE t.team_id = rt.team_id AND rt.team_rank IN (1,2)

A statement with the pre-tag:

SELECT t.team_name, t.team_id
FROM tdg_team t,
    (SELECT r.team_id, rank() over (PARTITION BY r.team_group_id
                                    ORDER BY r.points DESC, r.matches_won DESC,
                                    r.goals_difference DESC, r.goals_scored DESC) team_rank
       FROM tdg_match_rank r
      WHERE r.tournament_id = :TDG_AI_TOURNAMENT_ID) rt
WHERE t.team_id = rt.team_id
 AND rt.team_rank IN (1,2)

DG Tournament updated

I updated DG Tournament with the results of the drawing for the second round of the UEFA Champions League.

You can now start betting on the games of the 1/8 Finals! (see screenshot)

Some technical background about this update.
(you can't see the adaptation as it's in the "editor/admin" section of the application)
To add the matches of the next round I created a tabular form with dropdown lists of the teams.
I used an analytical functions to get the first two teams of each group:

SELECT t.team_name, t.team_id
FROM tdg_team t,
(SELECT r.team_id, rank() over (PARTITION BY r.team_group_id ORDER BY r.points DESC, r.matches_won DESC, r.goals_difference DESC, r.goals_scored DESC) team_rank
FROM tdg_match_rank r
WHERE r.tournament_id = :TDG_AI_TOURNAMENT_ID) rt
WHERE t.team_id = rt.team_id AND rt.team_rank IN (1,2)

I use analytical functions more and more. I find them really powerful!

Review of two J. Gennick books

In April I blogged about good books for my Oracle Team. One of the books was "Oracle Regular Expressions Pocket Reference" by Jonathan Gennick. Coincidence or not, but I met Jonathan at Oracle Open World this year! We went for a drink and had a really nice chat.
I find Jonathan a man with a lot of knowledge and he's a great story teller/writer ;-)
I also promised to write a review of two his books, so here we go.

SQL Pocket Guide (Second Edition)

Quoted from the Introduction of the book:

This book is an attempt to cram the most useful information about SQL into a pocket-size guide. It covers data manipulation and transaction control statements, datatypes and table creation and modification for the following platforms:

• Oracle DB 10gR2 & XE
• IBM DB2 8.2
• MS SQL Server 2005
• MySQL 5.0
• PostgreSQL 8.1

I find it an excellent description what this book is about.
I would recommend this book to every DBA or Developer working on multiple database flavors!
You can quickly find how you can do something. For ex.: What Datetime Functions are available in Oracle or SQL Server or ...?
Not all implementations of SQL are the same in the different databases and not every SQL is available in the same way. This book not only tells you about SQL but it also proofs it on the hand of a sample schema. The sample schema can be downloaded from the O'Reilly site.
Next to the little "tips & warnings", I find this the biggest PRO for this book! THE PROOF! You can test and see the result yourself, which is important as things change... (although maybe for SQL a bit less)

Oracle Regular Expressions (Pocket Reference)

Quoted from the Introduction of the book:

This little booklet describes Oracle's regular expression support in detail. It's goal is to enable you to take full advantage of the newly introduced regular expression features when querying and manipulating textual data.

As above, I find it an excellent description what this book is about. The book explains the use of the regular expressions very well with clearly written examples.
For this book too, you can download a sample table with data to test the regular expressions yourself. I find regular expressions really powerful and one of the most underestimated/(used) features of the database. I used for example regular expressions to check for a valid email address in the registration page of the World Cup 2006 application.

Apex Oracle User Group (or APEX SIG)

Is there an Oracle User Group specialized in Application Express?
Yes! It's called the APEX SIG: Application Express Special Interest Group

You can all make part of this User Group by registering on the IOUG (Independent Oracle User Group) website.
After the voting of the last weeks, yesterday, the APEX SIG leaders got announced. We're with a group of seven extraordinary people!

The seven people are:

• President: Steve Howard
• Vice President: Dimitri Gielis
• Web Content Coordinator: Doug Gault
• Web Master: Tony Jedlinski
• SIG Officer: John Scott
• SIG Officer: Scott Spendolini
• SIG Officer: Vikas Agnihotri

Together with the others, I'm also looking forward to get this APEX SIG rock! Of course we can't do everything by ourselves, so you're input is really important! The purpose of the APEX SIG is to provide a centralised, cohesive resource for anyone and everyone involved with Application Express.

John Scott (JES) posted a nice thread on the APEX forum which explains the purpose of the SIG really well. His last paragraph:

On a final note, I want to stress again how important it is for us to have
your involvement in this, if you've ever sat and thought "I wish APEX did
this..." or "I hate the way it does that...", then please get involved with the
SIG, let's all make APEX an even better development environment because we'll
all benefit from that.

So go to the website and get involved into this APEX SIG!

asktom beta in APEX

I don't know if you already saw the "beta version of the new asktom interface"? I find it cool! You can see the new askTom here.

I've the impression that this askTom is more integrated into APEX and uses a lot more features that come "out-of-the-box" with APEX. For ex. the use of theme 18, tabs, breadcrumbs etc.

I really look forward to dowload this application from the "Packaged Application" page on the APEX OTN page. The application is called "Ask the Expert" and has following description:

Ask the Expert is an application based on Ask Tom (http://asktom.oracle.com/). Using this application, users can post questions to an expert and the expert can answer. The application is powered by interMedia for text search. The expert has the full power to control the application. They can limit the number of questions allowed and block new questions completely. If the question is found to be useful, it can be published so others can view it.

APEX - Application Migration Workshop

On OTN an evaluation release of a new feature of APEX 3.0 is released.

Donal Daly, one of the developers of the product, blogged about it here. He would appreciate it if you give some feedback about your first impressions using the tool.

What does the tool do?

APEX App Mig Workshop assists customers in migrating Microsoft Access Forms and Reports to Oracle APEX.

How does it work?

1. Export MS Access Metadata
2. Migrate MS Access database to Oracle
3. Create an Oracle APEX Workspace
4. Create a Migration Project
5. Analyze MS Access Application
6. Generate Oracle APEX Application
7. Customize your Oracle APEX Application