Avoiding ORA-01722 Invalid Number

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Tue, 7 Jun 2005 07:15:59 +0100
Message-ID: <MPG.1d0f4bfd4a5ab270989ea8@news.individual.net>

Platform: Oracle 9iR2

For the following, please don't tell me to 'change my design' - I just want to know whether there is a way of coding the select such that it won't return an error.....

---

09:10:37 SQL> CREATE TABLE t1(charcol VARCHAR2(10)); Table created.

09:10:49 SQL> INSERT INTO t1 VALUES ('10'); 1 row created.

09:11:05 SQL> INSERT INTO t1 VALUES ('20'); 1 row created.

09:11:05 SQL> INSERT INTO t1 VALUES ('30'); 1 row created.

09:11:05 SQL> INSERT INTO t1 VALUES ('40'); 1 row created.

09:11:05 SQL> INSERT INTO t1 VALUES ('50'); 1 row created.

09:11:05 SQL> SELECT * FROM t1 WHERE charcol BETWEEN 20 AND 30; CHARCOL

---

20
30

09:11:12 SQL> INSERT INTO t1 VALUES ('PEN'); 1 row created.

09:11:18 SQL> SELECT * FROM t1 WHERE charcol BETWEEN 20 AND 30; ERROR:
ORA-01722: invalid number

no rows selected

---

Now I can see that Oracle is objecting because we are asking it to compare values in the selected columns to numbers (we can change the BETWEEN clause thus: "BETWEEN '20' and '30'" and this would run without error.

However a value of '021' would not fit within this range because it is a 3 character string rather than a true number.

What I would like is for Oracle to return a result set where the value in the column is NUMERIC and between 20 and 30 and not throw an error when it finds a value that is not numeric.

?

cheers

-- 

jeremy

Received on Tue Jun 07 2005 - 01:15:59 CDT