Re: Avoiding ORA-01722 Invalid Number

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 7 Jun 2005 19:05:24 -0700
Message-ID: <1118196324.299627.311020@g14g2000cwa.googlegroups.com>

scott_at_ORA92> CREATE TABLE t1 (charcol VARCHAR2(10))   2 /

Table created.

scott_at_ORA92> INSERT ALL

  2  INTO t1 VALUES ('10')
  3  INTO t1 VALUES ('20')
  4  INTO t1 VALUES ('30')
  5  INTO t1 VALUES ('231')
  6  INTO t1 VALUES ('PEN')

  7 SELECT * FROM DUAL
  8 /

5 rows created.

scott_at_ORA92> CREATE OR REPLACE FUNCTION my_to_number   2 (p_string IN VARCHAR2)
  3 RETURN NUMBER
  4 AS
  5 v_num NUMBER;
  6 BEGIN
  7 v_num := TO_NUMBER (p_string);
  8 RETURN v_num;
  9 EXCEPTION
 10 WHEN VALUE_ERROR THEN
 11 RETURN NULL;
 12 END my_to_number;
 13 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> SELECT *
  2 FROM t1
  3 WHERE my_to_number (charcol) BETWEEN 20 AND 30   4 /

CHARCOL

---

20
30

scott_at_ORA92> Received on Tue Jun 07 2005 - 21:05:24 CDT