The COALESCE function returns any datatype such as a string, numeric, date, etc. (BUT all expressions must be the same datatype in the COALESCE function.)
If all expressions are not the same datatype, an ORA-00932 error will be returned.

Just remember it, I actually have failed several times on this because the only thing that I did remember about COALESCE was – it returns the first non-null element. Who would have thought that all these elements must be the same data type?

Let’s see an example – how Oracle rejects incompatible data types being used in COALESCE as arguments

I have this simple test failed several times because my only rule for COALESCE that I have remembered was – first non-null element is returned. Should this be enough, COALESCE would retune 1 in above example, but it returned an error because text element ‘Ammetha’ cannot be implicitly converted to numeric data type, which all other elements belong to.

Unfortunately, implicit data type conversion is not happening in this case either. (as you can see below)

I would expect CHAR value ‘4’ be implicitly converted to NUMBER 4, but it is not happening.

COALESCE is a very strict function – all arguments should be the same data type.