I don't see the correct line numbers in the erros logged using format_error_backtrace. I don't see why. Compiled code is exactly same as pasted here (user_source). This is just a sample code and actual code is way more complex and not knowing the exact line number is pain.
DROP TABLE test_055;
DROP TABLE test_055_a;
DROP TABLE test_055_b;
CREATE TABLE test_055
(
ky NUMBER PRIMARY KEY,
a_id NUMBER,
a_cd VARCHAR2 (3),
b_id NUMBER,
b_cd VARCHAR2 (3)
);
CREATE TABLE test_055_a
(
a_id NUMBER PRIMARY KEY,
a_cd VARCHAR2 (3)
);
INSERT INTO test_055_a (a_id, a_cd)
VALUES (11, 'A');
INSERT INTO test_055_a (a_id, a_cd)
VALUES (12, 'AA');
CREATE TABLE test_055_b
(
b_id NUMBER PRIMARY KEY,
b_cd VARCHAR2 (3)
);
INSERT INTO test_055_b (b_id, b_cd)
VALUES (111, 'B');
INSERT INTO test_055_b (b_id, b_cd)
VALUES (112, 'BB');
ALTER TABLE test_055
ADD CONSTRAINT fk_a_id FOREIGN KEY (a_id) REFERENCES test_055_a (a_id);
ALTER TABLE test_055
ADD CONSTRAINT fk_b_id FOREIGN KEY (b_id) REFERENCES test_055_b (b_id);
--------------------------------------------------------------------------------
--Trigger to sync a_id and a_cd, b_id and b_cd
CREATE OR REPLACE TRIGGER trg_test_055
BEFORE INSERT OR
UPDATE OF a_id,
a_cd,
b_id,
b_cd
ON test_055
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DECLARE
trg_name VARCHAR2 (30) := 'TRG_TEST_055';
v_a_id NUMBER;
v_a_cd VARCHAR2 (3);
v_b_id NUMBER;
v_b_cd VARCHAR2 (3);
BEGIN
IF ( NVL (:new.a_id, 999) <> NVL (:old.a_id, 999)
AND NVL (UPPER (TRIM (:new.a_cd)), 'XYZ') =
NVL (UPPER (TRIM (:old.a_cd)), 'XYZ'))
THEN
IF :new.a_id IS NULL
THEN
:new.a_cd := NULL;
ELSE
SELECT a_cd
INTO v_a_cd
FROM test_055_a
WHERE a_id = TRIM (:new.a_id);
:new.a_cd := v_a_cd;
END IF;
END IF;
IF ( NVL (UPPER (TRIM (:new.a_cd)), 'XYZ') <>
NVL (UPPER (TRIM (:old.a_cd)), 'XYZ')
AND NVL (:new.a_id, 0) = NVL (:old.a_id, 0))
THEN
IF :new.a_cd IS NULL
THEN
:new.a_id := NULL;
ELSE
SELECT a_id
INTO v_a_id
FROM test_055_a
WHERE UPPER (a_cd) = UPPER (TRIM (:new.a_cd));
:new.a_id := v_a_id;
END IF;
END IF;
IF ( NVL (:new.b_id, 0) <> NVL (:old.b_id, 0)
AND NVL (UPPER (TRIM (:new.b_cd)), 'XYZ') =
NVL (UPPER (TRIM (:old.b_cd)), 'XYZ'))
THEN
IF :new.b_id IS NULL
THEN
:new.b_cd := NULL;
ELSE
SELECT b_cd
INTO v_b_cd
FROM test_055_b
WHERE b_id = TRIM (:new.b_id);
:new.b_cd := v_b_cd;
END IF;
END IF;
IF ( NVL (UPPER (TRIM (:new.b_cd)), 'XYZ') <>
NVL (UPPER (TRIM (:old.b_cd)), 'XYZ')
AND NVL (:new.b_id, 0) = NVL (:old.b_id, 0))
THEN
IF :new.b_cd IS NULL
THEN
:new.b_id := NULL;
ELSE
SELECT b_id
INTO v_b_id
FROM test_055_b
WHERE UPPER (b_cd) = UPPER (TRIM (:new.b_cd));
:new.b_id := v_b_id;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(
trg_name || ':KY:'
|| :new.ky
|| ':Error:'
|| DBMS_UTILITY.format_error_stack
|| DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
/
SHOW ERR
--------------------------------------------------------------------------------
INSERT INTO test_055 (ky, a_id)
VALUES (1, 11);
INSERT INTO test_055 (ky, b_id)
VALUES (2, 111);
UPDATE test_055
SET a_id = 12
WHERE ky = 2;
SELECT * FROM test_055_a;
SELECT * FROM test_055_b;
SELECT * FROM test_055;
----------------------------------------------------------------
UPDATE test_055
SET a_id = 142
WHERE ky = 2;
TRG_TEST_055:KY:2:Error:ORA-01403: no data found
ORA-06512: at "SBOX.TRG_TEST_055", line 16 --expecting LINE 25
INSERT INTO test_055 (ky, b_id)
VALUES (3, 662);
TRG_TEST_055:KY:3:Error:ORA-01403: no data found
ORA-06512: at "SBOX.TRG_TEST_055", line 50 --expecting LINE 59
UPDATE test_055
SET a_cd = 'XY'
WHERE ky = 1;
TRG_TEST_055:KY:1:Error:ORA-01403: no data found
ORA-06512: at "SBOX.TRG_TEST_055", line 33 --expecting LINE 42
UPDATE test_055
SET b_cd = 'XY'
WHERE ky = 1;
TRG_TEST_055:KY:1:Error:ORA-01403: no data found
ORA-06512: at "SBOX.TRG_TEST_055", line 67 --expecting LINE 76
I see a lag of 9 lines - why? How can I get the correct line numbers?
Thank you!
August 04, 2020 - 7:08 am UTC
You *are* getting the right line number, but you need to interpret it correctly.
Remember a trigger is effectively:
Trigger Definition + PLSQL block
It is only the plsql block that pertain to line numbers, and that is where they start from. This will help explain:
SQL> create table t as select * from scott.emp;
Table created.
SQL>
SQL> create or replace
2 trigger trg
3 before delete on t
4 for each row
5 declare <<=== your plsql starts here
6 real_line int;
7 begin
8 real_line := 4;
9 real_line := 5;
10 real_line := 6;
11 real_line := 7;
12 real_line := 8;
13 select 1 into real_line from dual where 1=0;
14 real_line := 10;
15 real_line := 11;
16 real_line := 12;
17 real_line := 13;
18 exception
19 when others then
20 dbms_output.put_line(dbms_utility.format_error_backtrace);
21 end;
22 /
Trigger created.
SQL>
SQL> set serverout on
SQL> delete from t where rownum = 1;
ORA-06512: at "MCDONAC.TRG", line 9