25 February 2010

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.

23 February 2010

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...