6

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
nandin
  • 2,549
  • 5
  • 23
  • 27

5 Answers5

9

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.

Lost in Alabama
  • 1,653
  • 10
  • 16
6

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

Pavan Ebbadi
  • 852
  • 1
  • 13
  • 26
5

In short, if the first argument is numeric then Oracle attempts 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

pablo
  • 408
  • 2
  • 4
5

In your first example, because borrowingterm is a numeric type, Oracle is trying to implicitly convert the string 'no term' to a numeric, hence the error.

See the NVL documentation.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
2

"the first parameter of NVL function should be string type"

I think you gave the answer yourself. If you are asking why, you should consider the possibility of reusing the output type of NVL in another function.

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

In the situation above, the output of nvl is unknown, thus an ORA-01722 is raised.

Erkan Haspulat
  • 12,032
  • 6
  • 39
  • 45