Error ORA-01722 de Oracle
Cuando estás haciendo pruebas de base de datos y Oracle te devuelve el error ORA-01722, empiezas a sudar.
|
1 2 3 4 |
ORA-01722: número no válido 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. |
Dicho error es bastante genérico y puede deberse a cosas bastante dispares. Oracle no da mucha más información en su web.
En mi caso se debía a que los datos de prueba tenían texto o caracteres no numéricos en un campo que estaba intentando convertir a numérico. Además asociado a un TRIM(). Descubrí que la función TO_NUMBER() funciona de la siguiente manera en lo que respecta al tipo de valor de entrada.
|
1 2 3 4 5 |
SELECT TRIM (TO_CHAR (NULL, '0000')) FROM DUAL; -- NULL SELECT TRIM (TO_CHAR ('', '0000')) FROM DUAL; -- NULL SELECT TRIM (TO_CHAR ('1', '0000')) FROM DUAL; -- 0001 SELECT TRIM (TO_CHAR ('null', '0000')) FROM DUAL; --- Devuelve el error ORA-01722 SELECT TRIM (TO_CHAR (' ', '0000')) FROM DUAL; --- Devuelve el error ORA-01722 |
Expresiones regulares para encontrar caracteres no numéricos en Oracle
En estos casos es donde las denostadas expresiones regulares vienen en nuestra ayuda. Mediante las siguientes podemos encontrar distintos tipos de caracteres alfabéticos, signos de puntuación, etc…
Encontrar registros que contienen letras en un determinado campo.
|
1 |
SELECT * FROM nombre_tabla WHERE REGEXP_LIKE(nombre_campo,'[a-zA-Z]+'); |
Esta es otra forma de encontrar letras, mirando si el campo es distinto en mayúsculas o minúsculas, lo que ocurre sólo en caso de encontrar letras.
|
1 |
SELECT * FROM nombre_tabla WHERE UPPER(nombre_columna) != LOWER(nombre_columna); |
La siguiente consulta busca caracteres no numéricos, incluyendo signos de puntuación. Ojo que también detecta los puntos y comas de los formatos numéricos decimales. La podemos liar si no llevamos cuidado.
|
1 |
SELECT * FROM nombre_tabla WHERE REGEXP_LIKE(nombre_columna,'\D+'); |
Y finalmente otra consulta que encuentra espacios.
|
1 |
SELECT * FROM nombre_tabla WHERE REGEXP_LIKE(nombre_columna,'\s+'); |
Sencillas y efectivas.