Search code examples
sqloracle-databaseora-01722

Can I pass a number for varchar2 in Oracle?

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.

Solution

  • 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.