Weblog for the AMIS Technology corner
Archive for May, 2006
Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)
May 31st
Yesterday I published an article on this blog describing an approach to sorting the elements in PL/SQL Collections:Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one) . This approach makes use of the property of PL/SQL Associative Arrays that the keys are always kept in sorted order. We do not have to do anything, just stuff the things we want to have sorted into an Associative Array and when done, retrieve them again. They will reappear properly sorted! With some additional logic, we can use this mechanism to quickly sort large collections of complex records.
However, there are a few downsides to this approach: it will only sort on VARCHAR2 and NUMBER fields – and the alphabetical sort may not be the one we require. More complex sorting rules are not supported. Besides, the code may not be as clear and elegant as we would like. So far I have not done any performance tests – that would be the next step.
But first I want to demonstrate another way of sorting PL/SQL Collections. We will make use of the TABLE, MULTISET and CAST operators to out-source the heavy lifting to the SQL Engine! This means we have the full power of SQL at our disposal for manipulating the PL/SQL collection. Note: not just for sorting, also for filtering and aggregating! There is one important requirement: the PL/SQL Collection must be based on a type that is defined in the database!
Pitfalls in Internet Explorer – How to cover them up or walk around them
May 31st
I recently had to do a lot of Javascript and CSS scripting
for a project. I have some experience with Javascript and CSS and know how to
work around little quirks in Internet Explorer and FireFox. But now I had to do
advanced things (manipulating the DOM-tree,
layout was ruined). This was the moment I got really tired of Internet
Explorer. I use FireFox since version 0.3 and really like it for the speed and
tabbed browsing. Internet Explorer was all right, but I preferred FireFox But
now I realized FireFox also did a good job under water. Internet Explorer does
some really strange and unpredictable things.
I don’t hate Microsoft or wrote this article to do some
bashing, they just could have done a better job creating Internet Explorer and
it’s just a warning that testing in FireFox is not enough. With every quirk of
Internet Explorer I will give a workaround or let you know how the avoid the
bug. IE is still used on most of the pc’s and we just can’t ignore the people
using it.
Read the rest of this entry »
Free “1000 Java Tips” book
May 31st
A free book, titled 1000 Java Tips can be found at http://javafaq.nu/java/advert/index.shtml. It is claimed that after reading you will improve your skills and raise your salary.
Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one)
May 31st
Bubble Sort, Quick Sort, Insertion Sort, Shuttle Sort… When time is short, these are not the easiest ways of implementing sort operations on your PL/SQL Collections. In this brief article – or at least I will try to be brief – we will discuss two smarter ways of sorting collections than using these tried and tested algoritms. The first makes use of the fact that the PL/SQL engine will keep the keys of any Associative Array sorted in natural order, automatically. The second is a little more advanced and uses the MULTISET and CAST operators to have the SQL Engine do the sort for us – in even less code.
Note: Alex and I are doing a Quiz on SQL and PL/SQL at ODTUG 2006 in Washington next month. Yesterday we had a try-out of this quiz – 21 questions and some 20 odd contestants. The quiz and all demonstrations went okay, but we learned we had to shed at least 8 questions. The one we had one sorting PL/SQL Collections was one of the ones to – hence I can share it with you on the weblog. Of course the remaining 13 or so questions are not on the weblog until after we run the quiz in DC. However, you might care to take a look at the accompanying paper that you can find here: http://www.amis.nl/tech_artikelen.php?id=340 .
Why are there so few resuable PL/SQL components – Discussion triggered by presentation on PL/SQL Design Patterns
May 30th
While the Java – and even the .NET, Ruby, Perl, Python, PHP – arena is teeming with open source projects and reusable components, the PL/SQL scene is quiet. Why is that?
This question was one of the discussion items provoked by the dress rehearsal of the ODTUG 2006 Presentation: PL/SQL Design Patterns – pre-inventing the wheel. This presentation focused on the general concept of Design Patterns, some specific examples from adjacent technology domains and their possible applicability to the PL/SQL realm of programming. Here too we found that for virtually any popular technology stack and programming language, there is an abundance of Design Patterns, implementations of patterns and books about them, while everything seems quiet on the PL/SQL front.Again: why?
This article will not give you an answer. However, we wil discuss some of the possible causes – and you are invited to join in and add your two-cents… Perhaps this discussion can lead to a more explicit, vibrant collection of PL/SQL design patterns as well as reusable components. We can do that too you know!
AMIS gains First and Third place in Volleyball Tournament
May 30th
Tonight, AMIS participated in a Volleyball Tournament for companies in Nieuwegein, the place where the AMIS headquarters are located. Aptly named, the AMIS 1 team became First and AMIS 2 became Third in this tournament. Both teams became First in their group compatition, but AMIS 2 got beaten by a team from Rijkswaterstaat Nieuwegein in the semi-finals. This team were beaten by AMIS 1 in the finals, while AMIS 2 won the game for Third and Fourth place. Both teams won a bowl of Bittergarnituur while AMIS 1 also won the Tournament Cup.
Well done AMIS 1. Next year, AMIS 2 will meet you in the finals and beat your sorry @$$ 
The (AJA)X Files – On the built-in AJAX facilities of ADF Faces for zero-code Rich User Experience
May 25th
Any article discussing AJAX is currently an instant hit. Articles talking about JSF (Java Server Faces) are also pretty hot. So the combination must drive the number of reads through the roof… While many developers are trying to figure out how the AJAX and JSF paradigms can be sensibly merged together, one of the richest JSF implementations on the market – ADF Faces, created by Oracle and donated to the Open Source Apache MyFaces project – is equipped with a lot of on-board AJAX capabilities. In this article I will show you which AJAX tricks ADF Faces has up its sleeves and how we can use them – virtually without any programming.
We will see a Box Office application where we can order Theater Tickets. It has a particularly responsive interface that has the following features
- instant re-calculation of the over-all price of the tickets
- refresh of the list of available shows when I change my type-of-show preferences
- instant validation of the number of tickets I order (as this number is limited for popular shows)
- dynamic enable/disable of the seating item – some shows do not have a seating arrangement
- adopt the thumbnail image shown to the show selected
I believe that this list contains the most valuable uses of the concept of AJAX – which I would define as ‘having the web client communicate with the server without the user being aware and possibly update specific sections of the page based on the response received from the server, all to make for a quicker response to a user’s actions’. ADF Faces does not use the XmlHttpRequest Object, usually associated with AJAX. Does that make this any less AJAX? Not in my book.
Instant calculation of derived values, validation of newly entered values, updates of selection lists and refresh of screen widgets (enable/disable or hide/show) is I believe the bulk of what AJAX can do for data entry driven web applications.
IN or EXIST or doesn’t it matter
May 24th
In my previous post about package constants I mentioned the application I am working on right now. They gave me the task to speed up the application since performance was getting worse and worse. I analyzed the statspack results together with a DBA and we found two queries that together took 40% of the logical IO’s of the system. That’s a lot for only two queries, especially when you look how big our application is; we have many queries.
The two queries were both in one procedure. That must have been an off day for the original programmer 
. The queries were small and had an IN statement in the where clause. I simply rewrote the queries to use an EXIST and they became blazingly fast. That was strange. I attended the Tom Kyte seminar in Utrecht in 2005 and he claimed that it didn’t matter anymore. IN or EXIST, the database would see this and the optimizer would have the same execution plan for both, but not in my case. How could that be? I was running the queries in a 9R2 database.
The long awaited Oracle Warehouse Builder 10g R2 is now available
May 24th
After a long delay OWB 10g R2 (previously known as Paris) has finally been released for production and can be downloaded from OTN. OWB 10g R2 will not be packaged with the Oracle Development Suite as was the case with release 1 but will now be found packaged with the Oracle Database products (from release 10g R2 database). So if you’re looking to download OWB 10g R2 make sure you look in the Database Suite of products and NOT in the Development Suite.
Read the rest of this entry »
Dynamic SQL Pivoting – Stealing Anton’s Thunder
May 24th
I have a colleague who has a stroke of genius. He can create things in SQL and PL/SQL that I find very hard to fathom, let alone could have come up with myself. His name is Anton and his recent focus has been Pivoting in SQL. The process of turning a Query Result upside down to present it differently. Turning the set of values in one of the columns into the column headings for the entire query.
Like taking the following query result:
and turning it into this structure:
Anton recently wrote a brief, somewhat cryptic article on Pivoting SQL Queries on this weblog; see:http://technology.amis.nl/blog/?p=1197 . In this article, Anton introduced an approach to pivoting that does not require us to hard-code all the values that we want to see appear in our column-headings (such as the values for JOB in the example above). However, the OBJECT TYPE that he uses in this article contains the hardcode query itself. It was already quite good, but he went where man did not go before and created an implementation that is completely dynamic. We simply pass in the query we want to have pivoted, and the function Anton created obliges.
In this article I will show a few examples of what we can do with this amazing gem as well as make the code itself available for download. However, and this is the last time I will blow his trumpet, it is not my code. I am good at writing long-winded articles, while Anton forges some source.
