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;