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;

------解决方案--------------------
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 Exception

CREATE 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();


------解决方案--------------------
运用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 ;


------解决方案--------------------
探讨

运用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 ……


------解决方案--------------------
这样可以吗，
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;