5

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.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Victor
  • 16,609
  • 71
  • 229
  • 409

3 Answers3

5

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.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
JAQFrost
  • 1,431
  • 8
  • 8
  • Thanks. so the character is 1234, which oracle is converting implicitly to a number. SO why the error since 1234 is a valid number? – Victor May 03 '12 at 02:01
  • Something in the table isn't a number. Oracle leaves 1234 as a number. It is trying to convert the value in the column to match. When it finds a value that isn't a number, it throws the error. – JAQFrost May 03 '12 at 14:21
4

Oh, it is much better to convert to char rather than to numbers:

select *
from table
where col1 = to_char(1234)

When the col1 does not look like a number, to_number returns an error, stopping the query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

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

Oracle does an implicit conversion from character type of col1 to number, since you're comparing it as a number.

Also, you assume that 1234 is the only row that's being fetched. In reality, Oracle has to fetch all rows from the table, and then filter out as per the where clause. Now there's a character value in col1 that's being fetched before it encounters your 1234 row & that's causing the error, since the character cannot be converted to a number.

This fiddle shows the behaviour. Since abc canot be converted to a number, you get that error message

Now if the only record in the table is that of col1 containing a numeric character, you'll see that the statement will work fine

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134