How does line numbering work in an Oracle trigger?

Question

I have a trigger that throws an error, and I'm not sure how to find out which line of PL / SQL code throws this error. My mistake

[Oracle] ORA-01403: no data found ORA-06512: with "MYSCHEMA.FOO_BI", line 9

My trigger looks something like this:

create or replace TRIGGER "MYSCHEMA"."FOO_BI" BEFORE INSERT ON FOO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE NUM1 NUMBER; NUM2 NUMBER; BEGIN -- some comment if :new.batch_num is null then SELECT COUNT(*) INTO :NEW.BATCH_NUM FROM FOO WHERE CORP_ID = :NEW.CORP_ID; end if; if :new.batch_id is null or :new.batch_id = '' then :NEW.BATCH_ID := :NEW.CORP_ID || '-' || :NEW.BATCH_NUM; end if; /* etc... */ 

I found what looks like a similar question , but line numbering starts with create or replace... and presents my error line as a comment, which, I think, should be fictitious, How is line numbering indicated when an error occurs when the trigger starts?

Answer 1

Line numbering (as indicated in the stack trace) begins with the fact that the DECLARE line is 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 that the error was logged as occurring on line 9, which is actually line 13 in the source.

Answer 2

Line numbers refer to the stored source in the Oracle data dictionary. You can determine the actual line numbering by looking at the views of the data dictionary.

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