PL SQL: the problem of converting the first NVL parameter

Question

I have a Table application in which there is a column

BORROWINGTERM NUMBER(10,0) Nullable 

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

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

while this one works

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

and it also works

 select nvl(1234,5678) from dual; 

base of this article

the first parameter of the NVL function must be a string type

Answer 1

You get an error because you are mixing two different types in an nvl clause. One number, one line. Types must be the same.

Answer 2

In short, if the first argument is numeric, then Oracle is trying to convert the second argument to a number.

See NVL documentation

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105.htm#i91798

Answer 3

In the first example, since borrowingterm is a numeric type, Oracle tries to implicitly convert the string "no term" to numeric, hence an error.

See NVL documentation.

Answer 4

Keeping it simple,

• String null can be replaced with the string Substitute value.
• The null number can be replaced with a numerical value, etc.

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

Answer 5

"the first parameter of the NVL function must be a string type

I think you yourself answered. If you ask why, you should consider reusing the NVL output type in another function.

 select to_date(nvl(number_field, 'some_text')) from dual 

In the above situation, nvl output is unknown, so ORA-01722 is raised.