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.