如何得到plsql中执行时报错的SQL的位置行号

在进行PL/SQL开发的时候经常会写以下代码，

declare
  n1 varchar(10);
  n2 varchar(10);
  n3 varchar(10);
begin
select name into n1 from test_tab1 where id = 2;
 select name into n2 from test_tab2 where id = 2;
 select name into n3 from test_tab3 where id = 2;
EXCEPTION
    WHEN OTHERS THEN
      rollback;
   insert error_info_table values(‘Proc Name’,substr(sqlerrm,1,200));
   commit;
end;
/

当select into 或其他类型语句出现错误的时候，利用exception 截获住，把错误记录到 error table里，以便查找错误信息，但是这样做在查找问题的时候仍然有些麻烦，我们必须一个一个select语句去执行，看看错误到底发生在那条语句，有些时候我们可能会用以下方法来解决这个问题

 1. 每条语句都加一组 begin … exception … end .

 2. 在每条语句后都加一个标记变量，最后把改标记变量也存入error table.

很明显，这2中方法都会增加代码编写的工作量和复杂度。

那么是否有方法能得到，发生错误的代码行号呢？

在10gR1及之前，只能不去截获exception, 让错误发生，从错误信息中得到错误发生的位置，当然这肯定是不能接受的。

从10gR2后，我们可以利用 package DBMS_UTILITY.format_error_backtrace 来的到发生错误语句的行号。

请看以下例子：

SQL> declare
  2    n1 varchar(10);
  3    n2 varchar(10);
  4    n3 varchar(10);
  5  begin
  6    select name into n1 from test_tab1 where id = 2;
  7    select name into n2 from test_tab2 where id = 2;
  8    select name into n3 from test_tab3 where id = 2;
  9  EXCEPTION
 10      WHEN OTHERS THEN
 11        DBMS_OUTPUT.PUT_LINE(substr(sqlerrm,1,200));
 12       DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
 13  end;
 14  /
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7

PL/SQL procedure successfully completed.

根据这个“ORA-06512: at line 7”，我们可以很容易的找到发生错误的语句的位置。