PL SQL : NVL first parameter type conversion issue

Question

I have a table Application which has a column

BORROWINGTERM   NUMBER(10,0) Nullable

why this script throw an error (ORA-01722 invalid number)

select nvl(borrowingterm, 'no term') from Application 

while this one works

select nvl(to_char(borrowingterm), 'no term') from Application 

and this one also works

select nvl(1234,5678) from dual; 

base on this article

the first parameter of NVL function should be string type

Answer 1

You're getting the error because you are mixing two different types in the nvl clause. One number, one string. The types must be the same.

Answer 2

Keeping it simple ,

• String null can be replaced with string Substitute value.
• Number null can be replaced by number value and so on..

Example:

select nvl(to_char(borrowingterm), 'no term') from Application; 

//conert number to string and then provide string substitute value to column

select nvl(borrowingterm, 0') from Application; 

// replacing null with 0