如何在SQL*Plus中查找批量插入时失败的语句及缺失行

嘿，这个批量插入丢数据的问题我碰到过好多次，大概率是SQL*Plus的交互特性或者你Excel里的脚本藏着小问题，咱们一步步来定位：

1. 先查SQL*Plus的执行日志（最直接的方式） #

如果执行脚本时你没开日志，那下次一定要记得开启，这是排查错误的关键：

• 执行插入前先运行：spool insert_errors.log
• 然后粘贴你的所有INSERT语句执行
• 执行完后运行：spool off

打开生成的insert_errors.log，里面会清晰显示哪些INSERT语句报错了——比如违反主键约束、字段类型不匹配、单引号没转义这些问题，对应的行就是没插入成功的。

要是你之前没开spool，也可以回忆下执行时SQL*Plus有没有弹出红色的错误提示，那些提示对应的行就是插入失败的。

2. 对比源数据和目标表，精准定位缺失行 #

把Excel里的10K条源数据导入到一个临时表（用SQL Developer的「导入数据」功能就行，直接选Excel文件导入），然后用SQL对比找出缺失的行：

方法一：用MINUS语句（适合有唯一标识的表） #

假设你的源临时表叫temp_source，目标表叫target_table，id是唯一键：

SELECT id, col1, col2, col3 
FROM temp_source
MINUS
SELECT id, col1, col2, col3 
FROM target_table;

这个查询会返回所有在源数据里存在，但目标表没有的行。

方法二：用LEFT JOIN（无唯一键也能用） #

如果没有唯一键，就用所有字段做关联：

SELECT s.* 
FROM temp_source s
LEFT JOIN target_table t 
  ON s.col1 = t.col1 
  AND s.col2 = t.col2 
  -- 把所有字段都加上
WHERE t.col1 IS NULL;

这样也能找出完全没插入的行。

3. 排查Excel脚本的格式问题 #

很多时候丢数据是因为Excel里的INSERT语句有格式坑：

• 单引号没转义：比如字段值里有O'Malley，Excel里没写成O''Malley（两个单引号转义），导致SQL语法错误，这行直接插失败。
• 换行/截断问题：Excel单元格里的INSERT语句如果换行，复制到SQLPlus后可能被拆成多行，导致语法错误；或者SQLPlus的LINESIZE设置太小，把长语句截断了。
• 缺少分号：每条INSERT结尾必须有分号;，如果某行没加，SQL*Plus会把它和下一行合并成错误的语句，导致两行都插失败。

4. 优化SQL*Plus的环境设置（避免下次再丢） #

为了以后能快速定位问题，执行批量插入前先设置这两个参数：

• SET ERRORLOGGING ON：这个会把所有错误记录到系统表SPERRORLOG里，执行完后查SELECT * FROM SPERRORLOG;就能看到所有错误详情。
• SET FEEDBACK ON：每成功插入一行，SQL*Plus会显示「1 row created.」，如果某行没显示这个，就说明插入失败了。

另外，其实批量插入10K行不建议用单条INSERT复制粘贴的方式，效率低还容易出问题，下次可以试试用SQL*Loader或者把数据拼成批量INSERT（比如INSERT INTO table VALUES (...), (...), (...)），会稳定很多。

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