Query doubt

Hi All,

I did the follwoing test.

Step 6 falied with ORA-01722. Can you please let me know why oracle failed to convert the number into character?

1) create table xyz (col1 varchar2(20))

2)
insert into xyz values (1)
insert into xyz values ('2')
commit

3)
Following query works:
------------------------
select * from xyz where col1 = 1
select * from xyz where col1 = '2'

4)
insert into xyz values ('a')
commit

5)
Following query works:
------------------------
select * from xyz where col1 = '1'
select * from xyz where col1 = '2'

6)
Following query does not works:
---------------------------------
select * from xyz where col1 = 1

Maybe because of this:

Code:

---

insert into xyz values ('a')

---

:rolleyes:

Quote:

---

Originally Posted by balajiyes

Hi All,

I did the follwoing test.

Step 6 falied with ORA-01722. Can you please let me know why oracle failed to convert the number into character?

6)
Following query does not works:
---------------------------------
select * from xyz where col1 = 1

---

I hope now you learned how to treat the data type in Oracle.

Tamil

Quote:

---

Originally Posted by balajiyes

Can you please let me know why oracle failed to convert the number into character?

---

If you looked up the error message, you would understand why:

ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal

Thanks Guys

It worked in Step 3 and Why not in Step 6?

Why this query did not fail in Step 3...?

If you put a numeric filter for char column, oracle converts char column values to numbers to compare. How would you convert 'a' to number?

because you need a full table scan and to_number is applied to all rows, when it meets your "a" it fails

in step 3 since all rows can be "to_numbered" (no "a") so there is no error

try this test

create a non-unique index on col1, try your step 6 query

then

create an unique index on col1 and try again your step 6 query