当迁移Oracle数据库到其他数据库系统时，你可能会遇到一个棘手的问题：在Oracle表中存在无效的数字数据，导致在目标数据库（比如PostgreSQL系列）导入时报错。这些错误可能表现为类似“invalid input syntax for type numeric: ‘xxx’”的提示信息。最近，在将Oracle国产化改造项目迁移到商业发行的MogDB时，我也遇到了这个问题。看来Oracle的容错率太高了，不会像其他数据库那样严格检查数据的有效性。因此，我们需要一种方法来找出并修正这些错误数据。在本文中，我将分享一种解决这个问题的方法。
MTK工具入库错误
code: 22p02 msg: invalid input syntax for type numeric:“0.00000023H4” where: copy TAB123, column COL123: “0.00000023H4”
data: 1 xxx xx xx 0.00000023H4 …
MTK( Database Migration Toolkit)工具是导入Mogdb的异构数据迁移工具，错误提示很明确TAB123表COL123字段的”0.00000023H4″值是无效数字，并提示了该行记录，接下来我们去源库确认。
Oracle源库确认数据
— 注 环境oracle 19c ，报错数据列导出到临时库演示。
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @init
SQL> @desc test1
Name Null? Type
------------------------------- -------- ----------------------------
1 ID NUMBER(38)
2 V NUMBER
SQL> select * from test1;
ID V
---------- ----------
1 1
2
3
SQL> col dv for a40
SQL> select id,v,dump(v,1016) dv from test1;
ID V DV
---------- ---------- ----------------------------------------
1 1 Typ=2 Len=2: c1,2
2 NULL
3 Typ=2 Len=3: bd,18,f5
Note:
ID=3对应的V列就是报错的数据，值不可见并且不是NULL。
ORA-01722错误
SQL> select length(v) from test1;
ERROR:
ORA-01722: invalid number
no rows selected
SQL> select to_number(v) from test1 where id=1;
TO_NUMBER(V)
------------
1
SQL> select to_number(v) from test1 where id=2;
TO_NUMBER(V)
------------
SQL> select to_number(v) from test1 where id=3;
select to_number(v) from test1 where id=3
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select to_char(v) from test1 where id=3;
select to_char(v) from test1 where id=3
*
ERROR at line 1:
ORA-01722: invalid number
Note:
常规函数提示ORA-01722: invalid number，显然是个无效数字。 这类通常是修正，或者制空修改为null就可以，但首选是可以快速从几千万或上亿的表中找到该数据。
如何查找无效的NUMVER数据？
对于字符类型的字段
对于数据库中是char或varchar字符型的值，转number报错的方法较多，如基于exception创建的自定义函数，或Oracle 12c(12.2)to_xxx 函数或cast 函数的增强， 对于开发人员实在喜欢。
# 自定义函数
CREATE OR REPLACE FUNCTION IS_NUMERIC(P_INPUT IN VARCHAR2) RETURN INTEGER IS
RESULT INTEGER;
NUM NUMBER ;
BEGIN
NUM:=TO_NUMBER(P_INPUT);
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END IS_NUMERIC;
/
-- or --
CREATE OR REPLACE FUNCTION IS_NUMERIC2(P_INPUT IN VARCHAR2)
RETURN number
IS
NUM NUMBER ;
BEGIN
NUM:=TO_NUMBER(P_INPUT);
RETURN 1;
EXCEPTION
WHEN value_error
THEN
RETURN 0;
END;
/
SQL> create table test2(id int,v varchar2(20));
Table created.
SQL> insert into test2 values(1,1000);
1 row created.
SQL> insert into test2 values(2,200.1);
1 row created.
SQL> insert into test2 values(3,'99%');
1 row created.
SQL>insert into test2 values(4,'$123');
1 row created.
SQL> select * from test2;
ID V
---------- ----------------------------------------
1 1000
2 200.1
3 99%
4 $123
SQL> select id,v,is_numeric(v) from test2;
ID V IS_NUMERIC(V)
---------- ---------------------------------------- -------------
1 1000 1
2 200.1 1
3 99% 0
4 $123 0
SQL> select id,v from test2 where IS_NUMERIC(V)=0;
ID V
---------- ----------------------------------------
3 99%
4 $123
SQL> select id,v from test2 where IS_NUMERIC2(V)=0;
ID V
---------- ----------------------------------------
3 99%
4 $123
# TO_* Conversion Functions
SQL> select id,v,to_number(v) from test2;
ERROR:
ORA-01722: invalid number
SQL> select id,v,to_number(v
default -99999999 on conversion error) from test2;
ID V TO_NUMBER(VDEFAULT-99999999ONCONVERSIONERROR)
---------- ---------------------------------------- ---------------------------------------------
1 1000 1000
2 200.1 200.1
3 99% -99999999
4 $123 -99999999
SQL> select id,v from test2 where to_number(v default -99999999 on conversion error)=-99999999;
ID V
---------- ----------------------------------------
3 99%
4 $123
# CAST Conversion Functions
SQL> select id, v,cast(v as number) from test2;
ERROR:
ORA-01722: invalid number
no rows selected
SQL> select id, v,cast(v as number) from test2 where id=1;
ID V CAST(VASNUMBER)
---------- ---------------------------------------- ---------------
1 1000 1000
SQL> select id, v,cast(v as number
2 default -99999999 on conversion error) from test2;
ID V CAST(VASNUMBERDEFAULT-99999999ONCONVERSIONERROR)
---------- ---------------------------------------- ------------------------------------------------
1 1000 1000
2 200.1 200.1
3 99% -99999999
4 $123 -99999999
Note:
从oracle 12c r2不用创建自定义函数也可以做类型转换报错的捕捉，CAST函数 （以及TO_NUMBER、TO_BINARY_FLOAT、TO_BINARY_DOUBLE、TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ、TO_DSINTERVAL和 T O_YMINTERVAL函数）现在可以返回用户指定的值，而不是错误,增加了DEFAULT XXX ON CONVERSION ERROR语法，当然还有另一个函数在此版本引入，我会在下面介绍，对于本案例已经是number数据类型的列类型可以使用吗？下面继续测试test1的的数据“对于number类型的字段”
函数官方文档了解更多
- CAST
- TO_BINARY_DOUBLE
- TO_BINARY_FLOAT
- TO_DATE
- TO_DSINTERVAL
- TO_NUMBER
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_YMINTERVAL
- VALIDATE_CONVERSION
对于number类型的字段
SQL> select id,v,IS_NUMERIC(v),IS_NUMERIC2(v) from test1;
ERROR:
ORA-01722: invalid number
SQL> select id,v,to_number(v default -99999999 on conversion error) from test1;
ERROR:
ORA-01722: invalid number
no rows selected
SQL> select id,v,cast(v as number default -99999999 on conversion error) from test1;
ERROR:
ORA-01722: invalid number
no rows selected
Note:
对于已经是number类型的列使用以上函数就无法匹配了，还是直接会报ORA-01722: invalid number, 感谢@FranckPachot的提示使用VALIDATE_CONVERSION function.
VALIDATE_CONVERSION函数
12c R2新的 VALIDATE_CONVERSION() 函数可用于帮助您识别无法转换为所需数据类型的列值.如果转换成功返回1，否则返回0。我们继续使用上面的test1和test2测试数据演示。
# 对于char类型的字段
SQL> SELECT ID,V,validate_conversion(V AS NUMBER),validate_conversion(V AS NUMBER,'$99999') from test2;
ID V VALIDATE_CONVERSION(VASNUMBER) VALIDATE_CONVERSION(VASNUMBER,'$99999')
---------- ---------------------------------------- ------------------------------ ---------------------------------------
1 1000 1 0
2 200.1 1 0
3 99% 0 0
4 $123 0 1
# 对于number类型的字段
SQL> select id,v,dump(v) dv, validate_conversion(V AS NUMBER) is_valid_number from test1;
ID V DV IS_VALID_NUMBER
---------- ---------- ---------------------------------------- ---------------
1 1 Typ=2 Len=2: 193,2 1
2 NULL 0
3 Typ=2 Len=3: 189,24,245 0
SQL> select id,v,dump(v) dv, validate_conversion(V AS NUMBER) is_valid_number from test1 where v is not null and validate_conversion(V AS NUMBER)=0;
ID V DV IS_VALID_NUMBER
---------- ---------- ---------------------------------------- ---------------
3 Typ=2 Len=3: 189,24,245 0
Note:
validate_conversion是oracle 12.2SQL方面的新功能，在数据类型转换判断上是个非常有用的工具，可以简单判断逻辑，可见他比cast和to_*函数验证的阶段更早，对于本案例中的已是number类型的列中的值，依旧可以判断值的有效性，支持的数据类型data type conversions有Number.Date.Timestamp.Timestamp with time zone.Binary_float.Binary_double.Interval day to second.Interval year to month.
— enjoy —
