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


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

Read More…

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.  

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…

Oracle External Tables – Part 1

If Only I Could Write A SQL Query On Data in a File…

Imagine you have a text file with data in, such as a CSV (character separated values) or ASCII fixed width fields, wouldn’t it be great if you could treat it like a table and write SQL queries against in?  Well you can and that’s exactly what Oracle’s External Tables functionality allow you to do!
Read more…