Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

compare varchar with number

497828Mar 21 2007 — edited Mar 23 2007
Hi,
I had a problem as below:

Create table Test (col1 varchar2(20));
insert into Test (col1) values ('123');
insert into Test (col1) values ('ABC');
select * from Test Where col1 = 123;
I get following error:
ORA-01722: invalid number
select * from Test Where col1 = '123';
select successful.

But when I perform following step:
Delete from Test;
insert into Test (col1) values ('123');
insert into Test (col1) values ('234');
insert into Test (col1) values ('345');
select * from Test WHERE col1 = 123;
Select successful.

I just query why I can't compare 1 number to a varchar2 column when there is character record in that column. I'm using Oracle DB 10.2.0.2 and I believe oracle should be allow implicit convert between number char varchar2. Do I miss anything to make my first select statement return with the record I need?

Thank in advance.
Eng Khoon
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 20 2007
Added on Mar 21 2007
10 comments
2,152 views