Create Structured Nested XML from Flat Data

Imagine you’ve been given the task to create an XML document which is structured according to nested level of detail, but the data source is unstructured flat data, it’s not normalised, not extracted to the relevant third normal form. It might be coming from a view, query, an external table, a spreadsheet, some legacy table etc. Anyway, you get the idea.

In other words, convert this…

        ID FIRST_NAME           LAST_NAME                   AGE CITY                 REGION                    COUNTRY
---------- -------------------- -------------------- ---------- -------------------- ------------------------- --------------------
         1 Zsazsa               Piniur                       46 Hamburg              Hamburg                   Germany
         2 Chane                Frise                        41 Düsseldorf           Nordrhein-Westfalen       Germany
         3 Brandon              Fishbourn                    41 Manchester           England                   United Kingdom
         4 Hulda                Sepey                        31 Dortmund  

-- SNIP....

Into this….

  <COUNTRY NAME="....">
      <REGION NAME="....">
          <CITY NAME="....">

Continue reading


NO_DATA_FOUND when using aggregate functions like count, min, max, avg…

A common misconception when writing PL/SQL select …. into … statements is that if it is an aggregation query (a query that is using an aggregation like count, max, min, avg, etc.) then they always return a result – possibly null – so a NO_DATA_FOUND exception will never occur.

During a post on the Oracle Community Forums, a regular and respected contributor made a statement to this effect, before the classic error was pointed out to him.

Let’s take a look at when, how and why NO_DATA_FOUND exceptions occur and show that they can indeed occur when using aggregated functions.
Read more…