SQL & PL/SQL

I was helping a fellow developer try to find the point at which their TRIGGER was failing (the always vague "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" was popping up). Without putting the TRIGGER into the TOAD dubugger we were having a hell-of-a-time locating the offending code in a TRIGGER about 2000 lines long.

I can't really tell what's going on, though. Here's some sample code to help convey my confusion:

SQL> create table test_debug_output (f1 number, f2 number);

Table created.

SQL> create or replace trigger test_debug_output_bt
  2  before insert or update or delete
  3  on test_debug_output
  4  for each row
  5  declare
  6    procedure proc1;
  7    procedure proc2;
  8  
  9    var1 number;
 10    
 11    procedure proc1 is
 12    begin
 13      raise_application_error (-20500, 'ErRoR1');
 14      proc2;
 15    end proc1;
 16    
 17    procedure proc2 is
 18    begin
 19      raise_application_error (-20500, 'ErRoR2');
 20    end proc2;
 21    
 22  begin
 23    proc1;
 24  end test_debug_output_bt;
 25  /

Trigger created.

SQL> begin
  2    insert into test_debug_output values (1,2);
  3  exception when others then
  4    dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  5  end;
  6  /
ORA-06512: at "FORBESC.TEST_DEBUG_OUTPUT_BT", line 9
ORA-06512: at "FORBESC.TEST_DEBUG_OUTPUT_BT", line 19
ORA-06512: at line 2

PL/SQL procedure successfully completed.

Line 9 and 19? What do they have to do with anything ... In any case, let me add an EXCEPTION handler into the TRIGGER, so it looks more like the TRIGGER we have in production.

SQL> create or replace trigger test_debug_output_bt
  2  before insert or update or delete
  3  on test_debug_output
  4  for each row
  5  declare
  6    procedure proc1;
  7    procedure proc2;
  8  
  9    var1 number;
 10    
 11    procedure proc1 is
 12    begin
 13      raise_application_error (-20500, 'ErRoR1');
 14      proc2;
 15    end proc1;
 16    
 17    procedure proc2 is
 18    begin
 19      raise_application_error (-20500, 'ErRoR2');
 20    end proc2;
 21    
 22  begin
 23    proc1;
 *24  exception when others then*
 *25    raise;*
 26  end test_debug_output_bt;
 27  /

Trigger created.

SQL> begin
  2    insert into test_debug_output values (1,2);
  3  exception when others then
  4    dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  5  end;
  6  /
ORA-06512: at "FORBESC.TEST_DEBUG_OUTPUT_BT", line 21
ORA-06512: at line 2

PL/SQL procedure successfully completed.

Now just line 21? First, line 9, 19 or 21 don't fit with the line that's actually causing the error (line 13). And why did it shift from 9 & 19 to just 21 when I put an EXCEPTION handler into the TRIGGER? Neither of these are helping me locate the actual error.

Could someone please help me understand this behavior? It all seems to work flawlessly with PACKAGEs...

Thanks,
--=Chuck