Identifying the "last" record using the LEAD function

Of course there is no such thing as the "last" record in a relational database. Unless you have an ORDER BY in your query.
For the current project we needed to determine the last record in a set, because this record needs special treatment. One of my colleagues came up with a CASE expression combined with a LEAD function to determine this record.

XMLType, RefCursor and renaming ROW and ROWSET tags

The project that I'm currently involved in uses XML to send data to a reporting engine which parses the XML and creates a pretty PDF report.
In order to do this some "generic" packages were created to create the XML based on the content of a RefCursor. Works like a charm. Just call the function with a RefCursor and get an XML back.
The problem they initially had was that the XML returned uses the ROWSET and ROW tags, while they wanted different tags. No problem, some REPLACE function would do the job, and that worked fine. Until last week that was...

The Case for the Case Statement

Oracle 9i introduced the CASE Statement and CASE Expressions.
Some say that the CASE statement is a drop-in replacement of the IF statement, but there is a subtle difference between these two.
In this post I will explain the difference between the Case statement and the IF statement. Sometimes it's "better" to change the IF statement to a CASE statement.

Best Wishes

Let SQL do the talking:

select decode
      (sign (floor (maxwidth/2) - rownum)
      ,1, lpad (' ', floor (maxwidth/2) - (rownum -1))
         ||rpad ('*', 2 * (rownum -1), ' *')
      ,lpad ('* * *', floor (maxwidth/2) + 3)) "Merry Christmas"
from all_objects
,    (select 40 as maxwidth from dual)
where rownum < floor (maxwidth/2) + 5

... and in case your wondering what the result would be, I added a screenshot.

ODTUG Kaleidoscope 2010: Accepted Abstract

My presentation on Analytic Functions is accepted for the ODTUG Kaleidoscope 2010 conference. The other abstract that I sent in is an alternate, standby if you will. As you can see on the right side, I have attended (and presented) at the ODTUG yearly conference since 2005.

Revenge of the Multibyte Characters

In The Netherlands, where I'm from, we do have Multibyte characters. Names with diacritic characters occur frequently.
In a prior post I've written about using Oracle text to perform diacritic searches.

This post is about the table definition. Every now and then this problem rears its ugly head, that's why I decided to write this little note on it.

(Non Oracle): Shocking Truth

Do you know what it looks like when a couple of guys are trying out how much electricity runs through an electric fence?



The guys in this clip are my nephews Michiel, Randy, and Jesper. During a family reunion gettaway they couldn't resist trying out the electirc fence across the street... My brother just happened to be filming...