如何定位oracle批量插入数据引起错误的数据

假如将A表中的数据，选择几个字段插入B表
用批量的方法:
insert into b
select cc,dd
from a;

B的字段全部是非空
A的字段都可能为空

如果插入的过程中程序报错了，是由于非空限制造成的。
请问：
我该如何定位是A中的哪个数据引起错误的？

16 个解决方案

#1

insert into b
select cc,dd
from a
where a.** is not null;

你把非空的过滤调不仅好了

#2

引用 1 楼 crazylaa 的回复:

insert into b
select cc,dd
from a
where a.** is not null;

你把非空的过滤调不仅好了

这样的方法挺好的，另外也可以在执行插入语句之前先修复A表中的数据。确保A表中要插入到B表的字段没有空值，在设计表的时候给一个默认值就行了。

也可以用nvl(字段,替代值)函数来确保没有空值。

nvl函数参考

#3

多谢楼上两位
其实我的本意是抛砖引玉
不一定是仅仅非空那个意思
总之就是批量插入数据，可能会由于数据原因出错。
我想查出到底是哪条数据引起错误的。
就是这个意思。

#4

引用楼主 cnham 的回复:

假如将A表中的数据，选择几个字段插入B表
用批量的方法:
insert into b
select cc,dd
from a;

B的字段全部是非空
A的字段都可能为空

如果插入的过程中程序报错了，是由于非空限制造成的。
请问：
我该如何定位是A中的哪个数据引起错误的？

insert into b
select nvl(cc,0),nvl(dd,0)
from a;

#5

给你个代码，看看怎么处理。其实就是在出发EXCEPTION的部分。
要是业务复杂，你可以自定义EXCEPTION。

DECLARE
  v_err_detp_id dept%TYPE;
  CURSOR the_cursor  IS SELECT dept_id FROM dept;
  v_dept_arr the_cursor%rowtype;
  v_insert_sql VARCHAR2(100);
BEGIN
  OPEN the_cursor ;
  LOOP
    FETCH the_cursor INTO v_dept_arr;
    EXIT WHEN the_cursor%NOTFOUND;
    v_err_detp_id:=v_dept_arr.dept_id;
    execute IMMEDIATE v_insert_sql using v_dept_arr.dept_id;
  END LOOP;
  CLOSE the_cursor;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM:' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('ERR_DEPT_ID:' || v_err_detp_id);
end;
END;
/

#6

我这个没有用到游标阿
不过还是感谢楼上的

#7

引用楼主 cnham 的回复:

假如将A表中的数据，选择几个字段插入B表
用批量的方法:
insert into b
select cc,dd
from a;

B的字段全部是非空
A的字段都可能为空

如果插入的过程中程序报错了，是由于非空限制造成的。
请问：
我该如何定位是A中的哪个数据引起错误的？

用Logmnr分析下DML日志呢？
或者看看alter<sid>.log日志

#8

这里有篇Logminer来分析具体的DML操作日志的文章，不知道对你有没用？
http://space.itpub.net/12778571/viewspace-503191

#9

引用 6 楼 cnham 的回复:

我这个没有用到游标阿
不过还是感谢楼上的

我的重点也不是告诉你怎么用游标，用不用游标无所谓，而是你要在

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM:' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('ERR_DEPT_ID:' || v_err_detp_id);
end

这里捕获他的异常就可已了，就跟普通程序里的

Try

            Catch ex As System.Exception

            End Try

这样做一样。

#10

to 楼上：
捕获异常这个道理俺知道
批量插入的时候抛出异常容易
可是定位是哪条数据出错，这个俺还没有找到方法

#11

那你先得修复你的数据啊？
那要不然你就得过滤出空数据了

#12

操作之前你先把可能错误的数据都分析一遍不就好了

#13

引用 10 楼 cnham 的回复:

to 楼上：
捕获异常这个道理俺知道
批量插入的时候抛出异常容易
可是定位是哪条数据出错，这个俺还没有找到方法

Oracle不会自动给你找到错误的数据，然后保存下来，让楼主方便得到的。
你在程序上这时候怎么做照样也可以在ＰＬ／ＳＱＬ上做啊。

#14

用rowid，再查询的时候把rowid查出来，然后根据再写数据到B上的时候结合异常信息，输出rowid，这样子也许可以定位你的错误记录，不知道楼主是否要这个。

#15

如要要捕获出错的行,只能写cursor一条条插入,可以如楼上所说,记录rowid来定位是哪行数据有问题.

#16

楼主问题解决了吗？我也遇到同样的问题了，想定位到出错的记录