This tutorial is based on examples to be easier to follow. For example you would need to replace empty (null) value then you can use the NVL Oracle function that will replace empty (null) value with the second argument. But the Oracle NVL function is limited to only two variables and in case you would need to work with more than 2 values the Online Tech Support recommends using Oracle COALESCE. The COALESCE Oracle function returns first non-empty (not null) value. Also you can declare as many variables or table columns you need. The function is inherited from ANSI standard and it works in the same way as in Oracle in other Ansi SQL systems. Oracle started to support Oracle COALESCE from Oracle 9i version and syntax is:
COALESCE (<variable1>,<variable2>,<variable3>,...)
The first Online Tech Support Select with Oracle COALESCE has 3 columns and 1 variable “Y“. The third column is not null and the first and second are null (empty). As you can see the output returns the value “3” from the third column. If the 3rd column would be set null too then the last value has set to “Y” and the Oracle COALESCE would return the “Y”-value.
SELECT COALESCE(my_value1, my_value2, my_value3,'Y')
FROM (SELECT NULL AS my_value1, NULL AS my_value2, 3 AS my_value3
FROM dual );
On the this example we have set the 3rd column to NULL as well and since Oracle COALESCE returns first not null value then there is only left the last variable “Y“. Take a look at the output as it returned the “Y“-value.
SELECT COALESCE(my_value1, my_value2, my_value3,'Y')
FROM (SELECT NULL AS my_value1, NULL AS my_value2, NULL AS my_value3
FROM dual );
Now we are going to show you what happens if you’ll declare only 1 variable in Oracle COALESCE as on the following example.
SELECT COALESCE (dummy) FROM dual;
As you see from the output function COALESCE raised an error. As the error text says: “ORA-00938: not enough arguments for function” – you didn’t declare enough arguments. You need to give at least 2 parameters to Oracle COALESCE as this is the minimum amount.
The next question would be – can you declare the same column names or variables more than one time in Oracle COALESCE? Does COALESCE validate his parameters or arguments?
SELECT COALESCE (dummy,dummy) FROM dual;
We did write another Oracle select to show you that Oracle COALESCE does not validate its arguments. As you see on the output above the function returned the Dual table value “X” without any errors. Please keep in mind to check your parameters you are giving to Oracle COALESCE.
A small recommendation about Oracle functions COALESCE, NVL and NVL2. When you need to compare 2 values use Oracle NVL because it is faster. To do comparison with more than 2 values use the Oracle COALESCE and when you need to check only 1 variable use Oracle NVL2.
See Also:
Home Oracle NVL Oracle NVL2