Archive for May, 2006
Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)
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 More >
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. (more…)
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: More >
Why are there so few resuable PL/SQL components – Discussion triggered by presentation on PL/SQL Design Patterns
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!
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 @$$