SET NULL: Specify a String to Return Whenever a Null Value Occurs in SQLcl / SQL*Plus

SQLcl and SQL*Plus are command line interfaces for working with Oracle Database.

By default, they return an empty string whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

The Default

The following SELECT statement returns null:

SELECT ASIN(null)
FROM DUAL;

Result:

   ASIN(NULL) 
_____________ 
              

By default, SQLcl and SQL*Plus return an empty string whenever the result of a query is null.

Set a String for Null Values

Now let’s set a string to use whenever null is returned by a SELECT statement:

SET NULL 'null';

Now let’s run the null-producing original query again:

SELECT ASIN(null)
FROM DUAL;

Result:

   ASIN(NULL) 
_____________ 
         null

This time, the string null is returned.

Just to be sure, let’s change the string to something else and run the query again:

SET NULL 'Null value was returned.';

SELECT ASIN(null)
FROM DUAL;

Result:

                 ASIN(NULL) 
___________________________ 
   Null value was returned.