NVL function requires data type match. If you try to evaluate NUMBER data with NVL, then replacement value should also be NUMBER. It can’t be CHAR or VARCHAR2 for instance.

select from NVL(order_total, 0) from orders; 

Statement above is perfectly fine because order_total column is NUMBER and replacement value 0 is also a NUMBER

But statement below won’t work because of data type mismatch.

select from NVL(order_total, 'none') from orders; 

Data types that can be used are date, character, and number.
Data types must match:
NVL(commission_pct, 0)
NVL(hire_date, ’01-JAN-97′)
NVL(job_name, ‘No Job Yet’)

At the same time, when NVL expression data types differ, Oracle converts expression2 to the data type of expression1 if possible, before the two expressions compared.

Let’s consider the case when expression1 is character data type (CHAR or VARCHAR2) and expression2  is numeric data type (NUMBER or INTEGER)

select NVL(order_mode, 0) from orders;

Such NVL function will work perfectly despite 0 is a numeric literal and order order_mode column is VARCHAR2 column data type.

So, the general rule here is: NVL  function is very flexible when expression1 is a character data type. In that case, NVL will convert expression2 (DATE or NUMBER) to VARCHAR2 before the comparison.

If expression1 is DATE or NUMBER data type, and expression2 is character data type, then NVL won’t convert expression2 and produce error

Be careful here. In other words, Oracle will implicitly convert data types within NVL function if it is possible. Same rule applied through entire Oracle logic ( for instance in WHERE clause, etc)