LNNVL is a SQL function that takes a condition as an argument. It returns TRUE if the condition is FALSE or NULL. It returns FALSE if the condition is TRUE. It is available but not documented in Oracle database versions prior to 10gR1.

In his comment to my previous post about this function, Laurent demonstrated how […]

In Oracle, NULL does not equal NULL:

EDDIE@XE> SELECT ‘hi there’ 2 FROM DUAL 3 WHERE NULL = NULL;

no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

[…]

Let’s say that you want to check whether a word or a sentence reads the same backward or forward, how do you do it in SQL? you use the REVERSE function. We’ll see an example of that below, but first, a warning. Unlike LNNVL, REVERSE is still an undocumented SQL function, which means that it […]

RATIO_TO_REPORT is a very handy and useful function. Before going through what it does, let’s first look at this example:

SELECT last_name, salary, ROUND (salary / SUM (salary) OVER () * 100, […]

Connect to your Oracle database and try the following:

SELECT ‘hi there’ AS mycol FROM DUAL WHERE lnnvl (1 = 2)

You should get :

MYCOL ———- hi there

Now, you ask, what is LNNVL? You search the Oracle documentation, but you cannot find any mention of it in releases prior to 10gR1, yet […]

In Oracle, you quote string literals in single quotes. If you have single quotes inside that string literal, you have to escape each single quote with another single quote. But, did you know that, in 10gR2, there is an alternative approach, called the “Q-quote mechanism”?. I only knew about it when Martin mentioned it on […]

At least I forgot all about LEAST, a very simple Oracle SQL function.

You want to select the smallest value from a list of expressions, you use LEAST:

LEAST(expr [, expr ]…)

Some examples:

A famous person once said: Oracle’s analytic SQL functions rock, Oracle’s analytic SQL functions roll. I totally agree. However, I would like to add to this that they rock and roll on a much faster beat if you know more about them. To explain what I mean, let’s take an example:

In Oracle, as in other RDBMSs, nulls should be given a special treatment. In Oracle, there are three (maybe more?) functions that deal with nulls, NVL, NVL2 and COALESCE. I would like to ask you a question, but first, let me quickly refresh my memory:

I have noticed that, in his latest book, Tom Kyte used the SQL function DUMP quite frequently to expose the internal representation and the size of a specific expression. To be honest, I do not use DUMP very often, but I do find it useful in certain situations.