How does line numbering work in an Oracle trigger?

The line numbering (as reported in stack traces) starts with the DECLARE being line 1. So, if you do the following:

CREATE OR REPLACE TRIGGER foo
BEFORE INSERT ON test1 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
DECLARE
n1 NUMBER := 1;
n2 NUMBER := 2;

BEGIN

  -- a comment
  IF :new.n1 IS NULL THEN
    n1 := n2/0;
  END IF;
END;
/

SQL> insert into test1 values (3,'XX','YY',NULL);

insert into test1 values (3,'XX','YY',NULL)

ORA-01476: divisor is equal to zero
ORA-06512: at "XXX.FOO", line 9
ORA-04088: error during execution of trigger 'XXX.FOO'

SQL> select line, text from all_source where name = 'FOO';

      LINE TEXT
---------- --------------------------------------------------------------------------------
         1 TRIGGER foo
         2 BEFORE INSERT ON test1
         3 REFERENCING OLD AS OLD NEW AS NEW
         4 FOR EACH ROW
         5 DECLARE
         6 n1 NUMBER := 1;
         7 n2 NUMBER := 2;
         8 
         9 BEGIN
        10 
        11   -- a comment
        12   IF :new.n1 IS NULL THEN
        13     n1 := n2/0;
        14   END IF;
        15 END;

15 rows selected

You can see the error was reported as happening at line 9, which is actually line 13 in the source.


The line numbers refer to the stored source in the Oracle data dictionary. You can determine the actual line numbering by checking the data dictionary views.

SELECT text
  FROM all_source
  WHERE owner = 'MYSCHEMA'
    AND name = 'FOO_BI'
    AND type = 'TRIGGER'
    AND line = 9;