## True or False – NVL

11 05 2010

May 11, 2010

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of the NVL function, inspired by a recent thread that I found in a discussion forum.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1.  The most common use of the NVL function is to prevent division by zero errors in mathematical equations.

2.  The SQL statement:

`SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1;`

replaces NULL values found in the CURRENT_STATUS column of table T1 with the phrase: Not disclosed

3.  The NVL function replaces a NULL column value with any other value.

4.  The NVL function is the most efficient method for converting NULL values to a non-NULL value.

```CREATE TABLE T1(
C1 NUMBER,
C2 NUMBER,
C3 NUMBER,
C4 DATE,
C5 DATE,
C6 DATE,
C7 VARCHAR2(20),
C8 VARCHAR2(20),
C9 VARCHAR2(20),
PRIMARY KEY(C1));

INSERT /*+ APPEND */ INTO
T1
SELECT
ROWNUM,
DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
CHR(MOD(ROWNUM,26)+65)||
CHR(MOD(ROWNUM+1,26)+65)||
CHR(MOD(ROWNUM+2,26)+65),20,'A'),
DECODE(MOD(ROWNUM,5),0,NULL,
CHR(MOD(ROWNUM,26)+65)||
CHR(MOD(ROWNUM+1,26)+65)||
CHR(MOD(ROWNUM+2,26)+65),20,'A')),
DECODE(MOD(ROWNUM,5),0,NULL,
CHR(MOD(ROWNUM,26)+65)||
CHR(MOD(ROWNUM+1,26)+65)||
CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
DUAL
CONNECT BY
LEVEL<=1000000;

COMMIT;

The above creates a table with 1,000,000 rows, where one of every 5 rows contains NULL values in columns C2, C3, C5, C6, C8, and C9.

For some of the quiz questions, the following script might be helpful:

```SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS
SET TIMING ON

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'NVL_TEST';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT /*+ GATHER_PLAN_STATISTICS */
COALESCE(C3,C2,C1) NUM,
COALESCE(C6,C5,C4) DAT,
COALESCE(C9,C8,C7) VCAR
FROM
T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
NVL(C3,NVL(C2,C1)) NUM,
NVL(C6,NVL(C5,C4)) DAT,
NVL(C9,NVL(C8,C7)) VCAR
FROM
T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
NVL(C3,C1) NUM,
NVL(C6,C4) DAT,
NVL(C9,C7) VCAR
FROM
T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
DECODE(C3,NULL,DECODE(C2,NULL,C1,C2),C3) NUM,
DECODE(C6,NULL,DECODE(C5,NULL,C4,C5),C6) DAT,
DECODE(C9,NULL,DECODE(C8,NULL,C7,C8),C9) VCAR
FROM
T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
NVL(C3,NVL(C2,C4)) COL1,
NVL(C6,NVL(C5,C7)) COL2
FROM
T1;

SELECT
SYSDATE
FROM
DUAL;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';```

It is probably best to execute the above script several times to limit the effects of physical block reads.  You may see different performance results whether or not the 10046 trace is enabled, whether or not physical block reads are performed on every execution, and whether or not the test is executed directly on the database server.  The GATHER_PLAN_STATISTICS hint is used to permit DBMS_XPLAN.DISPLAY_CURSOR to retrieve the execution plan with the ALLSTATS LAST format parameter.

### 9 responses

11 05 2010

I will answer the easy question.
#2 False. Why?

```CREATE TABLE T1 (CURRENT_STATUS NUMBER);

INSERT INTO T1 VALUES(1);
```

The above SQL created a table with a single row. Now the test case:

```SQL> SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1;
SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1
*
ERROR at line 1:
ORA-00904: "Not disclosed": invalid identifier
```

```SELECT NVL(CURRENT_STATUS, 'Not disclosed') FROM T1;

SQL> SELECT NVL(CURRENT_STATUS, 'Not disclosed') FROM T1;
SELECT NVL(CURRENT_STATUS, 'Not disclosed') FROM T1
*
ERROR at line 1:
ORA-01722: invalid number
```

That did not work either. How could such a simple concept go wrong? Is it too much to ask that people test that something works before posting it on the Internet, or at least to ask someone to double-check that it works as described?

17 05 2010

This kind of error seems to be a recurring theme on *that* website ….

17 05 2010

At its core, for that website (and most commercial websites), it is a business decision that determines what is posted, what is error checked, what is periodically reviewed, what is corrected, and how frequently the pages are cross-linked from domains owned by the same company.

The cross-linking drives the pages further up the Google search results, which means that someone trying to learn how something works will *probably* view the web pages that were cross-linked, and will possibly leave the person to quickly learn that Oracle Database is probably an impossibly difficult to learn platform. Oracle Database is not an impossibly difficult to learn platform.

At its core, in case the message is not clear, this blog website has a simple message: “Stop, think, understand.” Not everything examined needs to be true for that to happen, but it is important to determine methods to detect when something cannot possibly be true, and verify that what is true will also not only be true in the specific release of Oracle that is in use, but also in past and future releases.

*Enough said on the topic, I think*

12 05 2010

I’ll answer the (imho) even easier question #1: FALSE.
Why? NVL doesn’t replace zeroes, it replaces NULLs. And a division by NULL doesn’t throw an exception, it simply resolves to NULL.

Ah, by the way: #3 can be falsified with your answer to #2.

12 05 2010

I think that I was typing up my answer for question #3 while you were posting the answer for question #1.

If I remember correctly, I think that Crystal Reports will *optionally* replace NULL numeric values with a value of 0, and if you bring the query result into a spreadsheet package, such as Excel, the NULL value will be treated as a 0. But, those special cases do not make the question true. As you stated, it definitely is not true for SQL.

12 05 2010

Time to answer another one of the easy questions.
#3 “The NVL function replaces a NULL column value with any other value.” False. Why? In my previous comment, after I replaced the double quotes ( ” ) with single quotes ( ‘ ), the query still failed. In that example the CURRENT_STATUS column was defined as a NUMBER and the SQL statement attempted to replace the NULL value in that column with a value that was a VARCHAR (string) value. The replacement value specified in the NVL function must be of the same datatype.

14 05 2010

Charles,
#4 false; coalesce (and case and decode) can use a short circuit evalution (and NVL and NVL2 are not setup for this) – http://triangle-circle-square.blogspot.com/2010/02/short-circuit-evaluations-moving-away.html

Regards
MP

14 05 2010

Martin,

That is a great example that you linked to in your response. I never considered using DBMS_LOCK.SLEEP to demonstrate the potential difference that the short-circuit evaluation could make in evaluation time. At first glance it might seem silly to use DBMS_LOCK.SLEEP in the PL/SQL function, but it could be the case in a production application that each of the PL/SQL functions in the NVL() and COALESCE() functions performs a large number of calculations and send several SQL statements to the database.

oraculix and Martin, thanks for contributing to this blog article.

28 02 2012

[...] “True or False – NVL”, ein paar schöne Beispiele zu Mißverständnissen bzgl. NVL() Bewerten: Diesen Artikel:DruckenFacebookTwitterMehrE-MailDiggRedditStumbleUponLinkedIn [...]