PL/SQL如何在异常处理中捕获异常发生的位置

Question

以下是我的过程代码：
create or replace procedure P_TEST is
begin
  INSERT INTO TEST_2 SELECT * FROM TEST_1 ORDER BY ID;
  DBMS_OUTPUT.put_line('成功导入'||SQL%Rowcount||'条数据！');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('错误信息：'||SQLERRM||'错误位置：'||SQL%Rowcount);
    ROLLBACK;
end P_TEST;我希望在插入数据时，如果出现异常要获取异常的发生的位置；但是SQL%Rowcount始终都是0，请教各位大虾有什么方法可以获取到异常发生的位置，以便反馈信息查找错误的原因。

Answer 1

create or replace procedure P_TEST 
is
cnt number;  ----用一个全局变量来存放你插入成功数据条数
begin
  INSERT INTO TEST_2 SELECT * FROM TEST_1 ORDER BY ID;
  cnt:=cnt+1; --成功一笔就累加
  DBMS_OUTPUT.put_line('成功导入'||SQL%Rowcount||'条数据！');
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.put_line('错误信息：'||SQLERRM||'错误位置：'||cnt);
  ROLLBACK;
end P_TEST;

Answer 2

谢谢你的回帖，但是这样还是不行的，我使用的是INSERT ... SELECT ... 的方法一次性导入数据，没有使用循环。我是想捕获在执行INSERT ... SELECT ... 语句时出现异常的记录位置。

Answer 3

Oracle提供的解决方案是：自己创建一个位置变量，在exception部分，根据位置变量的值来确定异常的记录位置。--以下引自Oracle文档：Using Locator Variables to Identify Exception Locations
Using one exception handler for a sequence of statements, such as INSERT, DELETE, or UPDATE statements, can mask the statement that caused an error. If you need to know which statement failed, you can use a locator variable:Example 10-14 Using a Locator Variable to Identify the Location of an ExceptionCREATE OR REPLACE PROCEDURE loc_var AS
   stmt_no NUMBER;
   name    VARCHAR2(100);
BEGIN
   stmt_no := 1;  -- designates 1st SELECT statement
   SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'ABC%';
   stmt_no := 2;  -- designates 2nd SELECT statement
   SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'XYZ%';
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Table name not found in query ' || stmt_no);
END;
/
CALL loc_var();

Answer 4

由于你的是insert ...select 没有采用循环插入，没办法直接定位到你批量插入发生异常的位置，还是采用循环插入吧。。

Answer 5

运用dbms_utility包中format_error_stack函数,可以定位到错误sql语句的原因。
例如：
create table ta(name varchar2(2));
declare 
begin
 insert into ta select 1 from dual;
 insert into ta select 2 from dual;
 insert into ta select 12 from dual;
 insert into ta select 123 from dual;
 insert into ta select 5 from dual;
 commit;
exception when others
   then 
     dbms_output.put_line(substr(dbms_utility.format_error_stack,1,200));
   rollback;
end ;

Answer 6

这个dbms_utility.format_error_stack不错 楼主可以看看 

Answer 7

这样可以吗，
create or replace procedure P_TEST is
begin
  INSERT INTO TEST_2 SELECT * FROM TEST_1 ORDER BY ID;
  if sql%notfound then
  raise exc;
  end if;
  DBMS_OUTPUT.put_line('成功导入'||SQL%Rowcount||'条数据！');
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.put_line('错误信息：'||SQLERRM||'错误位置：'||SQL%Rowcount);
  ROLLBACK;
end P_TEST;

Answer 8

我主要是要捕获INSERT INTO TEST_2 SELECT * FROM TEST_1 ORDER BY ID;这个语句的异常，如果这句出现异常的话，下面的语句就执行不到了。

Answer 9

还是一样没有捕获到INSERT...SELECT...语句出现异常的位置。