-
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
Cheers!
OraKid.
-
Maybe because of this:
Code:
insert into xyz values ('a')
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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
-
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
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Thanks Guys
It worked in Step 3 and Why not in Step 6?
Why this query did not fail in Step 3...?
Cheers!
OraKid.
-
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?
Ales The whole difference between a little boy and an adult man is the price of toys
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|