如何获取发生异常的SQL语句？

Question

在一个pl/sql块中：begin
 insert into ....
 insert into ....
 insert into ....exception
    when others then
         ----这里怎么取到发生错误的SQL?
end;请各位帮忙一下，谢谢

Answer 1

begin
 insert into ....
 if(sql%notfound) then
 raise_application_error(-20001,'insert1 have problem');
 end if;
 insert into ....
 if(sql%notfound) then
 raise_application_error(-20001,'insert2 have problem');
 end if;
 insert into ....
 if(sql%notfound) then
 raise_application_error(-20001,'insert3 have problem');
 end if;exception
  when others then
    DBMS_OUTPUT.PUT_LINE(sqlerrm(sqlcode));
end;

Answer 2

begin
 insert into ....
 insert into ....
 insert into ....exception
  when others then
  dbms_output.put_line(sqlcode||' '||sqlerrm);
end;

Answer 3

if(sql%notfound) then
 raise_application_error(-20001,'insert1 have problem');这个不行啊，改变了程序结构，太麻烦啦。dbms_output.put_line(sqlcode||' '||sqlerrm);
sqlcode是错误代码，sqlerrm是错误信息，不是sql语句本身，我要的是发生错误的sql语句

Answer 4

那你必须把每个insert放到一个异常块里，然后做处理，标明错误，然后raise出来。。不然就没办法了。。

Answer 5

begin
 insert into ....
 insert into ....
 insert into ....exception
  when others then
--也可以用这个
  dbms_output.put_line(dbms_utility.format_error_backtrace);
 
  dbms_output.put_line(sqlcode||' '||sqlerrm);
end;

Answer 6

可以弄个varchar2的变量v_sql。使用execute immediate动态执行。执行错误的时候只要把v_sql打印到日志里就可以了

Answer 7

把每条INSERT语句单独放在一个块中就可以

Answer 8

--使用系统错误触发器记录引发错误的语句
--建立一个日志表
create table ERROR_LOG
(
  TIMESTAMP     DATE,
  USERNAME      VARCHAR2(30),
  INSTANCE      NUMBER,
  DATABASE_NAME VARCHAR2(50),
  ERROR_STACK   VARCHAR2(2000),
  SQL_TEXT      VARCHAR2(4000)
);
--建立一个错误触发器，注意sql_text字段值
CREATE OR REPLACE TRIGGER LogErrors
  AFTER ServerError ON TEST.SCHEMA
--这里针对TEST用户，可修改，也可针对所有数据库使用on database
DECLARE
  sql_txt ora_name_list_t;
  v_stmt  VARCHAR2(4000);
  n       BINARY_INTEGER := 0;
BEGIN
  n := ora_sql_txt(sql_txt);
  IF nvl(n, 200) <> 200 THEN
    FOR i IN 1 .. n LOOP
      v_stmt := v_stmt || sql_txt(i);
    END LOOP;
    dbms_output.put_line(v_stmt);
  END IF;
  INSERT INTO error_log
  VALUES
    (SYSDATE,
     sys.Login_user,
     sys.Instance_num,
     sys.DataBase_Name,
     Dbms_Utility.Format_Error_Stack,
     v_stmt);
END LogErrors;