Re: Avoiding ORA-01722 Invalid Number

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 7 Jun 2005 03:50:43 -0700
Message-ID: <1118141443.267792.138770@g49g2000cwa.googlegroups.com>

>>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.

The OP seemed to look just for that (Avoiding ORA-01722 Invalid Number). The approach was only a demonstration that it can be done, without digging in the table design, different values and so on...

>>This will full-scan the table each time, does not account for special
characters, end perform this expensive computation for each and every row in the table.

Again, it was a mere example, not a solution in deep.

>>(TM) not a good idea - this will break exept for the smallest databases.

PIII 1133 MHz Win2K server 512 Mb Ram 10Gb HD:

SQL*Plus: Release 9.2.0.6.0 - Production on Mar Jun 7 12:40:01 2005

Conectado a:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> select count('x') from t1;

COUNT('X')

3840384

SQL> SELECT *
2 FROM ( SELECT REPLACE(charcol,'x',null) charcol 3 FROM ( SELECT TRANSLATE( charcol, 4
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 5

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') charcol
  6                      FROM t1 )
  7            WHERE charcol NOT LIKE '%x%' )

8 WHERE charcol BETWEEN 10 AND 20;

1280128 filas seleccionadas.

Transcurrido: 00:01:46.00

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3) 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=3)

Statistics

          0  recursive calls
          0  db block gets
      15860  consistent gets
       3634  physical reads
          0  redo size
    7619861  bytes sent via SQL*Net to client
     110503  bytes received via SQL*Net from client
      10002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

1280128 rows processed

I know it's not fantastic, but no errors and the data pulled out from the tiny PC...

>> i hate to see these 'fancy' solutions from so-called object-designers that cannot tell the difference between numeric datatypes and alphanumeric.

I guess you always work with (your) excellent designs and don't need to do such tasks. My experience in the real world is other.

Cheers.

Carlos. Received on Tue Jun 07 2005 - 05:50:43 CDT