Monday, June 30, 2008

APEX 3.1.1 "broke my application" : This is the fix.

In APEX 3.1.1 all compound item types - items that contain HTML layout and multiple inputs - have been surrounded by a fieldset. The result is that some items that previously nicely aligned horizontally now wrap to the next line.

It mainly effects items with Post Element Texts or where a ColSpan > 1 is defined.

This unwanted effect is easily repaired by creating a new CSS file containing:


and referencing this CSS file in your template. The result then looks like it was before:

Thursday, June 26, 2008

Drag & Drop feature in APEX

In a previous post I mentioned a Drag & Drop feature we created in APEX. Because there is a lot of interest for this subject, I created a small movie where I show the drag and drop function. The purpose is to load an Open Action (or Order) into a Truck for a specified day.


If you like to see this feature live at OOW - and on a somewhat bigger size screen - , or other nice things we created during our project, please vote for my session on Oracle Mix : (Re)developing-a-logistic-application-in-apex-in-the-real-world. You can register using your OTN account.

Extending SQL Developer with Designer data

Using the Preferences of SQL Developer you can register a User Defined Extension (UDE). This UDE is just a small XML file containing a command or query. If your Oracle Designer Repository is queriable by the user you use for your connection, you can show the Table Definitions in Designer alongside the definitions in the Oracle Database. You just have to create an XML file containing this code:

Register the file as an 'EDITOR' type in SQL Developer using Tools / Preferences / Database / User Defined Extensions. Restart SQLDev and the result is something like:

Is it usefull? Not really with this query, but you can extend the query with a lot of other data, for example to list the modules a table is used in or the column definitions of that table!

Monday, June 23, 2008

Travelling back from New Orleans

From the Sheraton to the airport I shared a cab with Carl Backstrom and arrived around 2 hours before the scheduled departure. I only had a 37 minute transfer time in Houston so I had picked a chair in the front of the plane - just after the business class. The plane left the gate exactly on time, but it stopped on the runway. Due to a thunderstorm the airport closed for 30 minutes....
So when I got out of the plane in Houston my transfer time was reduced to -2 minutes. The plane to Amsterdam was somewhere at the same pier - but that pier is really huge. So I started the 1 kilometer walk without any (positive) expectations. But to my suprise that plane was scheduled 45 to 60 minutes later! I've never been so happy with a delay....
The flight was o.k. In the 10 hours flight I snoozed a little and arrived in Amsterdam at the original scheduled time. My wife and daughters waited there for me, so it was a good homecoming!

ODTUG Kaleidoscope 2008 : Day 4 - ADF vs APEX and Wrap Up

The last morning of this years ODTUG was completely filled with one three hour session: APEX versus ADF 'shootout'. Although the presenters, Lucas and Dimitri, quickly stated that there was no violence to be expected, the audience was hoping for some clear statements.

Lucas and Dimitri both built an application using their favorite tool and demo'ed it to the audience. Both failed on one part - as live demo's tend to do - so it was 1-1 at mid time. After the break the match continued with some nice features shown on both sides. Both presenters where dared to say something positive on the other tools. For Dimitri this was the most difficult part: "I can't come up with anything nice to say about JDeveloper".

After a complete comparison, there was one clear winner: The audience!

Thanks to both of you guys for this joint effort!

So to wrap up: ODTUG is a great event. Not great in the sense that you're overwhelmed with 1,000's of people (there were about 700 attendees), but exactly the opposite. Due to the fact that it is rather small, you'll find yourself having a beer (or two) with all kind of guru's, experts, co-bloggers etc. Especially that relaxed, cool atmosphere is what makes ODTUG such an excellent event.

One more note about 'atmosphere': New Orleans in June is so hot and humid, that you barely can stand the outside temperature for more than 10 minutes - on the opposite : inside (in particular inside the Sheraton) the air conditioning is turned to the max. So every time you get from the outside to the inside, or the other way round, you have to deal with a temperature difference that feels like a 20 C.

Also the Sheraton is a good place to stay and from the 43th floor I had a great view on the river. Could have been better if somebody had cleaned the window - but who cleans windows at that height?

I hope to see everybody again next year - in Monterey (?).

Friday, June 20, 2008

ODTUG Kaleidoscope 2008 : Day 3 - Experts Day

Building Advanced Tabular Forms was the first subject of the day. In APEX you can build tabular forms (a multi record update form) using a wizard or manually (with the APEX_ITEM API and processing with the APEX_APPLICATION API. Another option is using a collection which gives you full control on the processing - like distributing your data over multiple tables.

After that one Dimitri Gielis presented on Creating Advanced Charts with APEX. He showed the audience what you can do using the charting options of APEX. Impressive!

Next was the Oracle APEX Developer of the Year 2007, Patrick Wolf. His main message is that you can speed up your development process (even further!) when you use the right third party tools. The tools you can't do without as an APEX developer are - apart from Firefox itself:
  • Firebug - a great extension on Firefox
  • Web Developer - another Firefox extension
  • YSlow - a performance monitor, again an Firefox extension
  • Live HTTP Header - to track traffic between the browser and from server, again Firefox
  • APEX Developer Plugin - An extension on APEX that Patrick built, to make your live (even) more easy
  • APEX Essentials - An APEX application to check the quality of your APEX application, free, adaptable and extendible, created by Patrick
  • jquery - A great Javascript pack, this may be included in the next version of APEX
  • APEXLib Framework - A framework, by Patrick, that reduces the writing of code even more
I will report on the presentations of Anton Nielsen and Carl Backstrom later (because I'm at the airport right now and have to get into the plane soon).

Thursday, June 19, 2008

ODTUG Kaleidoscope 2008 : Day 2 - You can(’t) do THAT in a browser!

The first session of this day was APEX Development: Watch it live! by Bill Holtzman of the National Air Traffic Controllers Association. He built a Grievance Control system for the NATCA used by hundreds of users. Now he showed live how to build a football betting system in only 45 minutes (using some prefab SQL scripts). Another nice example how quick application development in APEX can be!

After that Scott Spendolini came on stage with his presentation You can't do THAT in a browser! Although APEX contains a lot of really nice features, like Flash based charting, Export to PDF/Word./Excel and Tree Reports, sometimes you have the need to go a little bit further than that. He demoed how you can achieve MS Outlook integration for your Address Book (vCard) and Calendar (iCal). Then he showed how some third party products seamlessly integrate with APEX:

  • DHTLMX Tree (DHTML eXtensions) : Consists of JS libraries to manage all aspects and uses an XML file as data source. You can create his XML file you can using PL/SQL procedures
  • FusionGadgets & FusionMaps: To create Gantt Charts, Gauges., Pyramids and Process Flow Diagrams. These plugins also use XML file as a datasource, which can be generated from PL/SQL.
  • PLPDF : To generate PDF documents, charts, barcodes using PDF-templates.

These extensions have only got one drawback: They cost ‘some' money ($ 50 - $ 600)...

In the afternoon there was as session on Writing a Customized Authentication Scheme for APEX by Raj Mattamal. Raj is very enthusiastic and energetic and fun to listen to (and look at). APEX has a couple of components concerning security. Two of these components are Authentication (who are you? can you log in?) and Authorization (once you are logged in, what are you allowed to do). APEX offers a couple of standard ways to authenticate a user: LDAP, SSO, APEX's own security scheme, open door and database authentication. You would preferably use one of those. If the requirements in your organization demand that you should use another way to authenticate a user - for instance using an Oracle database table containing usernames and passwords, you can easily custom a standard authentication scheme and call your own function for validating usernames and passwords.

Another thing that may arise is that you would like the users to switch easily between applications, without re-entering their usernames and passwords. For applications within the same workspace this can be done by entering the same cookie name in the authentication scheme on both sides.

Alas this doesn't work when your applications are in different workspaces. Then you have to create your own Page Sentry Function. This function is checked on every page view to make sure that the user and session is still valid, and will redirect you to a login page if you aren't authenticated yet or (re-)instantiate the APEX session using APEX API calls. There is a great example on OTN on NTLM Authentication to start with.

Another thing to deal with is Single Sign-Out. Just expiring the Suite Cookie isn't enough, you have to log out of every application, otherwise your session information will still be in the database. You need to built a procedure for that manually.

For the next session I was an ambassador Explore and Benefit the APEX Repository by Michiel Jonkers of AMIS. He pointed out the differences between the Designer and APEX repository views. The main difference is that Designer offers the possibility to update the data in the repository using the API, APEX doesn't have an API for that (yet).

The best part of the day is that I could demonstrate our APEX showcase, containing a very good looking drag & drop feature, to some of the APEX guru's (Dimitri Gielis and John Scott of APEX Evangelists and David Peake and Carl Backstrom of Oracle APEX Product Development). And they all were very impressed by what I showed them! We already knew we built something awesome, but now other experts know that too! (Nice work Rutger!)

So you really can do THAT in a browser!

Carl strongly emphasized that I should blog about this drag & drop feature - probably so he can use it in a next version of APEX ;-) - so I will do that somewhere in the next weeks. Keep an eye on this blog if you're interested.

Tuesday, June 17, 2008

ODTUG Kaleidoscope 2008 : Day 1

Today started off with a keynote of Vince Casarez about ‘Web 2.0 meets the enterprise'. He showed some really cool stuff included in Webcenter, like the carrousel (similar to the iTunes coverflow), dynamic charts that were refreshed using push technology. This is the stuff Oracle itself is using for their Fusion apps.

Next John Scott was on for his APEX Hints, Tricks and Best Practices. They were all very useful so I'll list them all 10.

  1. Don't use the default SYSAUX tablespace for the APEX environment, but use dedicated tablespaces instead to make the installation more manageable.
  2. Create specific accounts for all developers, making QA and auditing easier.
  3. Always lock a page when you make changes and document these changes using the comment property.
  4. Use aliases for applications and pages, so f?p=SALES:HOME instead of f?p:117:17.
  5. Use the possibilities of APEX to support re-use of code, like Shared Components, Page 0, UI Defaults and Publish / Subscribe. A little explanation on the last one: If you copy an object you can define it as a subscription to its original. Modifications to the original can then be published to all subscribers.
  6. Use bind variables whenever possible. So don't code WHERE NAME = ‘%P7_SEARCH%', but use :P7_SEARCH instead. So in general:
    • Use :APP_USER in SQL or PL/SQL within APEX
    • Use &APP_USER. within HTML text
    • Use v(‘APP_USER') outside the APEX context
  7. Use database packages to code your PL/SQL.
  8. Don't use HTML markup or CSS in SQL queries: keep presentation and logic separated. Use HTML expressions and templates instead.
  9. Use the Supporting Objects for deployment. There you can define scripts that should run when your application is installed, upgraded or removed. Also use the Build Options, they're great for excluding features that are still on hand.
  10. Be pro-active: use the Application Reports, the Apex Dictionary and Pro-active Monitoring so that you will be the first to know when something is not going the way you expect it to go.

  11. After John I went to (another) Thom Kyte session on 'The Top 11 New Features of Oracle Database 11g'. The most of these features were already familiar to me as I was part of the Logica team that participated in the 11g b├Ęta test. Half of the features were DBA-related, but the features that are most appealing to developers are:

    1. the possibility to cache more using client side caching, server result caching or PL/SQL function result caching;
    2. virtual columns using expressions, constants or deterministic functions (you even can index these columns or use them in a foreign key constraint);
    3. the SQL pivot / unpivot command, so there's no need for the usual max(decode....) ‘trick'.

    In the afternoon Karen en Jan from iAdvise did a presentation on their QA-tool on APEX using the dictionary views. This is a good concept for controlling the quality of your applications on coding style and naming conventions and can be compared with the Headstart quality checks for Designer. They encountered one major technical issue: the battery of the laptop died halfway the presentation, so somebody had to pick up another power adapter.

    David Peake ended the day with an APEX 3.1 overview. To me the interesting part were the last 5 minutes (of his half an hour overdue presentation) where he showed very little of the pre-alfa version of the Oracle Forms Application Migration. With this utility you can read the XML file of a Forms module into Forms Objects in APEX. Then you can change these objects and generate an application. David didn't show the generation part (probably because it didn't work yet) and told that there was ‘some' more work to do, but the APEX team would like to deliver this feature asap... You can expect a whitepaper on OTN about his very soon!

Monday, June 16, 2008

ODTUG Kaleidoscope 2008 : The pre-conference conference

The day started with a rather fierce thunderstorm, and I can tell you that if it rains in New Orleans it really rains! This day was a sort of warming-up for the real conference with three full day symposiums on Essbase, APEX and Fusion. I attended the ‘The Seasoning of Application Express - Must hear Succes Stories‘ symposium that started off with an impressive presentation of Scott Spendolini. His theme was ‘Less is more‘, what didn’t refer this own presentation as he managed to go to 431 slides in half an hour! After that Mike Hichwa from Oracle did an ‘Apex Update’. He told us that currently APEX is the most used tool within Oracle for internal applications. Oracle also releases products that are based on APEX, like Database Vault. And we got a sneak preview of the future of APEX: we will get updateable interactive reports, custom item types, easy way to attach files of any kind to a record and declarative AJAX support. And some other good news: SQL Developer will support logical and physical data modeling - so we don’t need Designer for that anymore. And from these datamodels APEX applications can be generated using templates.

The other five presentations were user experiences. They all come together at one point: Application development can be incredibly fast when you use APEX, and that’s exactly what the business needs: short term to market and agile development. The IT departments have some difficulties in this paradigm shift: the regular long term waterfall approach doesn’t work here. And the risk is that the business users will develop the applications themselves (again, just like they did using Excel and Access), just because it is - or seems to be - so easy.

Just before the Welcome Reception Thomas Kyte did a presentation on ‘How do you know what you know…‘. It was the same message as he delivered 1,5 year ago during Open World, but with different examples: Knowledge is good, but can also be bad. If you had an experience in the past, that doesn’t automatically mean it still counts, because things, like the Oracle database, change. BTW it’s always a great pleasure to attend one of Thom’s presentations.

ODTUG Kaleidoscope 2008 : The day before

After an 18 hour trip from my home I arrived at the Sheraton in New Orleans. Luckily this time I also got my bag (at OOW 2007 it arrived a day later). A small wonder, because my transfer time in Houston was reduced from 1 ½ hour to 20 minutes due to the border control officer, who seemed to be very interested in all my predecessors in the queue. So I had to run from gate E to C. On the way I saw at a screen that The Netherlands where ahead against France with 1-0. At the time I arrived in New Orleans I called home and found out it turned out be a 4-1 victory!

When I came outside of the airport the tropical atmosphere hit me. It was about 32 C, but very very humid. After dropping of my luggage and freshening up a little in my room at the 43 floor, overlooking the Mississippi, I went out into the French Quarter. This area is packed with bars and restaurant and every bar seemed to have it’s own live music. I managed to stay awake until 9pm - that makes about 22 hours awake - and slept until 6:30 the next morning.

When I stepped out for a nice and quiet stroll through the French Quarter and the riverside it was so hot and humid already - or still - that my sunglasses blurred immediately. Like stepping into a tropical swimming pool. After my walk I took the old steamboat for a trip over the Mississippi and strolled over the Cajun / Zydeco and Tomato festival. New Orleans is very very lively: there is music on every corner and food stalls in between them. And of course the obvious street artists painting, breakdancing and reading hands. In this part of town there is not much damage left from Katrina three years ago, but also it didn’t suffer that much from The Storm as the locals seem to call it.

I hope I am over my jetlag already and ready for the start of the conference tomorrow morning at 8:30 : a full day ‘APEX Must hear success stories’

For those who are more of the visual type, I uploaded some pictures here.