Can I pass a number for varchar2 in Oracle?

sql oracle ora-01722
Victor picture Victor · May 2, 2012 · Viewed 16.7k times · Source

I have an Oracle table and a column (col1) has type varchar2(12 byte). It has one row and value of col1 is 1234

When I say 

select * from table where col1 = 1234

Oracle says invalid number. Why is that? Why I cannot pass a number when it is varchar2?

EDIT: All the responses are great. Thank you. But I am not able to understand why it does not take 1234 when 1234 is a valid varchar2 datatype.

Answer

JAQFrost picture JAQFrost · May 3, 2012

The problem is that you expect that Oracle will implicitly cast 1234 to a character type. To the contrary, Oracle is implicitly casting the column to a number. There is a non-numeric value in the column, so Oracle throws an error. The Oracle documentation warns against implicit casts just before it explains how they will be resolved. The rule which explains the behaviour you're seeing is:

When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.

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