长INSERT查询报ORA-01722错误，如何获取实际错误值？

我太懂这种 frustration 了——ORA-01722 明明提示是无效数字，但默认的异常处理根本不告诉你到底是哪个值搞砸了，尤其是面对超长的INSERT语句时，排查起来像大海捞针。下面给你几个实用的方案，帮你快速定位问题：

方案1：逐个验证数字列的值（适合单条INSERT） #

如果你的INSERT是单条VALUES语句，把需要转换为数字的列单独拿出来做转换验证，一旦失败就捕获具体值：

DECLARE
  v_temp_num NUMBER;
  -- 先把INSERT里的所有值定义成变量
  v_id NUMBER := 123;
  v_num_col_val VARCHAR2(50) := '456a'; -- 假设这个是有问题的值
  v_name VARCHAR2(100) := '张三';
BEGIN
  -- 对每个需要转数字的列单独做转换测试
  v_temp_num := TO_NUMBER(v_num_col_val);
  
  -- 验证通过再执行插入
  INSERT INTO your_table (id, num_col, name)
  VALUES (v_id, v_num_col_val, v_name);
  
  COMMIT;
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('触发错误的列是num_col，对应的值是: ' || v_num_col_val);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('其他错误信息: ' || SQLERRM || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

运行这个块，就能直接看到哪个值导致了转换失败。

方案2：用LOG ERRORS记录错误行（适合批量插入） #

如果是批量INSERT（比如INSERT ... SELECT），Oracle的LOG ERRORS子句能帮你把错误行单独存到一张错误表，方便事后排查：

步骤1：创建错误日志表 #

BEGIN
  -- 替换成你的目标表名
  DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'your_table');
END;
/

这会自动生成一张名为err$_your_table的错误表。

步骤2：带错误日志执行INSERT #

INSERT INTO your_table (id, num_col, name)
SELECT source_id, source_num_str, source_name
FROM your_source_table
-- 开启错误日志，标记错误类型，不限制拒绝行数
LOG ERRORS INTO err$_your_table ('BATCH_INSERT_ERROR') REJECT LIMIT UNLIMITED;

步骤3：查询错误详情 #

SELECT 
  ora_err_mesg$ AS 错误信息,
  ora_err_tag$ AS 错误标记,
  id, num_col, name -- 原表的列，看具体哪行数据有问题
FROM err$_your_table;

这里的num_col列会显示导致转换失败的具体值，一目了然。

方案3：自定义安全转换函数（适合SELECT插入场景） #

写一个自定义函数，在转换数字失败时抛出包含具体值的错误：

CREATE OR REPLACE FUNCTION safe_to_number(p_input VARCHAR2) RETURN NUMBER IS
BEGIN
  RETURN TO_NUMBER(p_input);
EXCEPTION
  WHEN VALUE_ERROR THEN
    -- 抛出带具体值的自定义错误
    RAISE_APPLICATION_ERROR(-20001, '无效数字值: ' || p_input);
END;
/

然后在INSERT里用这个函数替换原来的列：

INSERT INTO your_table (num_col)
SELECT safe_to_number(source_num_str)
FROM your_source_table;

执行时会直接报错告诉你哪个值是无效数字，不用再猜。

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