首页 » Cloud, ORACLE 9i-23ai » Oracle国产化改造迁移时的问题: Number data type中的 invalid number

Oracle国产化改造迁移时的问题: Number data type中的 invalid number

  • 2024/05/13
  • Cloud, ORACLE 9i-23ai
  • 95 views
  • Oracle国产化改造迁移时的问题: Number data type中的 invalid number已关闭评论

当迁移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类型的字段”

函数官方文档了解更多

对于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 —

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：