PL SQL : NVL first parameter type conversion issue

sql oracle ora-01722
nandin picture nandin · Mar 1, 2011 · Viewed 14.1k times · Source

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

Lost in Alabama picture Lost in Alabama · Mar 1, 2011

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.

Related questions

Joining tables with LIKE (SQL)

First of all I am using Oracle: Table One Name = tableone Table Two Name = tabletwo tableone has a column named pizzaone, tabletwo has a column named pizzatwo. I want to join tableone to tabletwo where pizzaone is somewhere in the …

Read More
.nextval JDBC insert problem

I try to insert into table with sequence .nextval as primary key, the sql in Java is sql = "INSERT INTO USER (USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL ) VALUES (?,?,?,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "User.nextval"); ps.setString(2, accountNumber); ps.…

Read More
oracle jdbc driver version madness

Why the heck does Oracle offer a different(!) version of the JDBC driver, e.g. ojdbc14.jar, for every(!) database version? The files all have different sizes and thus probably different content. background: We get a random and seemingly irreproducible …

Read More