Using DBMS_XMLGen for Dynamic SQL

The DBMS_XMLGen package has some interesting functionality that allows the caller to perform SQL based on a SQL string, where previously they have had to use Dynamic SQL (execute immediate / DBMS_SQL) or compile and call PL/SQL routines.

Here’s an example, where I’m counting the number of records in a couple of tables in the HR schema.

select table_name, DBMS_XMLGen.GetXMLType('select count(*) cnt from '||table_name) XML_Data
from user_tables
where table_name in ('EMPLOYEES', 'DEPARTMENTS')
/

TABLE_NAME                       XML_DATA
------------------------------------------------------- 
'DEPARTMENTS'                    <ROWSET>
                                   <ROW>
                                    <CNT>27</CNT>
                                   </ROW>
                                 </ROWSET>

'EMPLOYEES'                      <ROWSET>
                                   <ROW>
                                    <CNT>107</CNT>
                                   </ROW>
                                 </ROWSET>

Advertisements

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

<COUNTRIES>
  <COUNTRY NAME="....">
    <REGIONS>
      <REGION NAME="....">
        <CITIES>
          <CITY NAME="....">
            <RESIDENTS>
              <RESIDENT>....

Null Member in Collection?

If you’re working with collections you sometimes need to know if some value is a member of it. Fortunately, Oracle provided the very useful member of function, it’s highly optimised and will out-perform other methods

Nvl versus Coalesce, and the winner is…

Most Oracle developers I know use NVL, rather than COALESCE for returning the first not null value of two arguments.  Most will cite it’s for habitual, historic reasons (COALESCE was introduced later in Oracle’s history) or because it’s shorter to type.

“Well NVL and COALESCE do the same thing anyway, so why would I change?“

Well, there’s one very good reason why you should the SQL standards function COALESCE over Oracle’s proprietary NVL….

CLOB Concatenation

Adding string data to a CLOB in Oracle can be a tad slow, when compared to other string concatenation situations.  I first became aware of this concatenation performance issue from my own coding experience, but also from threads on Oracle Technical Network and a blog post by Jonathan Lewis.

Recursive Subquery Factoring Date Bug in 11gR2

Since Oracle 11gR2, Oracle has supported Recursive Subquery Factoring – this is a more flexible way of doing recursive queries than Oracle’s Connect By. Although 11g is quite old now, it’s still being used in earnest.  Whilst writing some code recently, I noticed there appears to be a problem with them regarding dates. I found this in version 11.2.0.2.0. Let’s take a look at what I found…

with t1(a_date) as (
  select date '2018-12-26'
  from   dual
  union all
  select a_date + 1 -- Problem here
  from   t1
  where a_date + 1 < date '2018-12-30'
)
select * from t1

Error at line 5
ORA-01790: expression must have same datatype as corresponding expression

Faster Decimal to Binary Function

There was recently a post on the Oracle forums by a person who needed to convert decimal values to a binary string. They had found the standard library PL/SQL function that you often see on Oracle sites, but its slow performance was giving him headaches. It was an interesting thread as many people offered solutions, and it showed the ingenuity of people, in coming up with alternatives. In this article I’ll show some of my efforts, and my final function which, to my pleasure, was the fastest in the thread.

Inheriting Values in SQL

Imagine a situation where you have hierarchical data with various values and you wanted to inherit values from ancestors if a given level in the hierarchy had null values. So if a level had a null value, it could inherit that missing value from the parent and if the parent didn’t have a value, the grandparent etc. etc. updwards until we reach the root. How would you do it?  Well in this article I’ll explore a few approaches.

Parameterised Views

Background

Occasionally I see posts on Oracle sites from users asking how to create a view based on parameter(s).  Now, many would ask why would you’d need this, especially when you can simply select from the view and apply a where clause?  Well, there are a few situations which fall outside of that approach, the commonest it probably that the view contains a function that needs parameters or the view requires some “state” in its operation (state means there is memory of the past).  Anyway, assuming you have such a need let’s look at some of the approaches at our disposal.

How to Convert Accented Characters into Non Accented Ones

A while ago a user on an Oracle forum I post to asked how he could convert accented characters into non accented characters (note, he didn’t care about case sensitivity, just wanted rid of the accents).  This was because he was creating a database role from this via an Web API and Oracle was raising an ORA-00911 invalid character whenever an API call was made with an accented character.

I knew that when Oracle did binary sorts on strings, it needed to forget about accents, meaning it had such functionality built in somewhere.  So the solution I gave him ended up leveraging this.

Anyway, here’s an example of the solution using a full list of European accented characters.

with t as (
  select 'ÂÃÄÀÁÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ' str 
  from dual
)
select str, utl_raw.cast_to_varchar2(nlssort(str, 'nls_sort=binary_ai')) str2 
from t

STR                                                            STR2 
-------------------------------------------------------------- ---------------------------------------------------------------
ÂÃÄÀÁÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ aaaaaaæceeeeiiiiðnoooooouuuuyþssaaaaaaæceeeeiiiiðnoooooouuuuyþy 
1 row selected.

I use Oracle’s inbuilt nlssort functionality which converts a string into a raw (string of bytes) used for sorting, and I simply specify that raw is to be accent and case insensitive. I then simply cast that raw back into a varchar2 – which results in replacing accented chars with their non accented, non case sensitive equivalent!  If he wanted this uppercase, he could simply UPPER(..) it.

Note : Some special characters in Icelandic / Turkish have no unaccented equivalent, but then there’s nothing we can do about those.