1) When a plsql block fails, you are always told which line the failure occurred on, eg
SQL> create or replace procedure bad_proc is
2 x int;
3 begin
4 select 1 into x from dual;
5
6 select 2 into x from dual;
7
8 select 3 into x from dual;
9
10 select 4/0 into x from dual;
11
12 select 5 into x from dual;
13
14 select 6 into x from dual;
15 end;
16 /
Procedure created.
SQL>
SQL>
SQL> exec bad_proc
BEGIN bad_proc; END;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MCDONAC.BAD_PROC", line 10
ORA-06512: at line 1
If you had an exception handler, then UTL_CALL_STACK and DBMS_UTILITY can be used to get this info as well, eg
SQL> create or replace procedure bad_proc is
2 x int;
3 begin
4 select 1 into x from dual;
5
6 select 2 into x from dual;
7
8 select 3 into x from dual;
9
10 select 4/0 into x from dual;
11
12 select 5 into x from dual;
13
14 select 6 into x from dual;
15 exception
16 when zero_divide then
17 dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
18 end;
19 /
Procedure created.
SQL>
SQL> set serverout on
SQL> exec bad_proc
HELP - I DIED HERE=>ORA-06512: at "MCDONAC.BAD_PROC", line 10
PL/SQL procedure successfully completed.
And here is some more information about handling errors in PLSQL
http://stevenfeuersteinonplsql.blogspot.com/2016/03/nine-good-to-knows-about-plsql-error.html 2) If you are inserting millions of rows, then ideally you are doing this with a single INSERT-SELECT, because that will be the most efficient. In that instance, you can typically monitor v$session_longops to see the progress.
Some examples of that here
https://asktom.oracle.com/pls/asktom/asktom.search?tag=vsession-longops-200011 If you have *code* that is doing the inserts, then you can instrument the code with DBMS_APPLICATION_INFO which can be monitored from other sessions. Search this site for DBMS_APPLICATION_INFO for many many examples of doing this.