如何避免Oracle查询中的ORA-01722: invalid number错误？

这个问题我碰到过不少次，确实很头疼——毕竟只要有一行数据格式不对，整个游标就卡壳了。不过有几个靠谱的办法能解决，看你的Oracle版本和需求选就行：

1. 用Oracle 12c+的安全数字转换（最简便） #

如果你的Oracle数据库是12c或更高版本，直接用TO_NUMBER的DEFAULT ON CONVERSION ERROR特性，转换失败时返回指定默认值（比如NULL），这样就不会触发错误了。修改后的查询语句如下：

SELECT text 
FROM book 
WHERE lyrics IS NULL 
  AND MOD(TO_NUMBER(SUBSTR(text,18,16) DEFAULT NULL ON CONVERSION ERROR),5) = 1;

当SUBSTR(text,18,16)不是合法数字时，TO_NUMBER会返回NULL，MOD(NULL,5)结果也是NULL，不会等于1，这类行就会被自动过滤掉，不会触发错误。

2. 先过滤非数字行（兼容低版本Oracle） #

如果你的数据库版本低于12c，没有上面的特性，可以先用正则表达式判断子串是否为纯数字，再进行转换计算。修改查询：

SELECT text 
FROM book 
WHERE lyrics IS NULL 
  AND REGEXP_LIKE(SUBSTR(text,18,16), '^[0-9]+$') -- 只保留子串是纯数字的行
  AND MOD(TO_NUMBER(SUBSTR(text,18,16)),5) = 1;

这样查询只会处理那些子串确实是数字的行，从源头上避免转换错误。

3. 在存储过程的游标循环里捕获异常（适合必须处理所有行的场景） #

如果你的需求是即使行有错误也要处理其他正常行，而不是过滤掉错误行，那可以在游标循环的每一行处理逻辑里加异常捕获：

CREATE OR REPLACE PROCEDURE process_book_data IS
  CURSOR c_book IS
    SELECT text 
    FROM book 
    WHERE lyrics IS NULL;
  v_text book.text%TYPE;
BEGIN
  OPEN c_book;
  LOOP
    FETCH c_book INTO v_text;
    EXIT WHEN c_book%NOTFOUND;
    
    BEGIN
      -- 尝试执行转换和判断逻辑
      IF MOD(TO_NUMBER(SUBSTR(v_text,18,16)),5) = 1 THEN
        -- 这里写你的业务处理逻辑，比如插入、更新等
        DBMS_OUTPUT.PUT_LINE('处理行：' || v_text);
      END IF;
    EXCEPTION
      WHEN ORA-01722 THEN
        -- 捕获数字转换错误，跳过该行，记录日志或直接忽略
        DBMS_OUTPUT.PUT_LINE('跳过错误行：' || v_text || '，原因：数字转换失败');
      WHEN OTHERS THEN
        -- 处理其他异常
        DBMS_OUTPUT.PUT_LINE('处理行出错：' || v_text || '，错误：' || SQLERRM);
    END;
  END LOOP;
  CLOSE c_book;
END;
/

这个方法的好处是，哪怕某一行触发了ORA-01722错误，异常块会捕获它，然后继续处理下一行，不会让整个存储过程终止。

内容的提问来源于stack exchange，提问作者en Lopes