Change the XML Prolog using XMLROOT

About a month ago I wrote about using XMLType and RefCursor to rename the ROW and ROWSET tags. You can find that article by clicking here.
For the same project described in that article the XML prolog needed to include the encoding as well. Just in case you are unfamiliar with the XML Prolog, this is it:

<?xml version="1.0"?>

In Oracle 10g there is an XMLROOT function which creates the XML Prolog for you, but it doesn't allow you to add encoding to it. At least not by specifying parameters, but there is a way to do this, read on..

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.