Skip to Main Content
  • Questions
  • Line numbers in error messages do not match source lines

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Joris.

Asked: March 06, 2018 - 2:39 am UTC

Last updated: August 05, 2020 - 3:06 am UTC

Version: 12

Viewed 10K+ times! This question is

You Asked

When running a PL/SQL script through sqlplus, eventual error messages contain a line number that does not match the source lines. For example :

ERROR at line 1:
ORA-06533: Subscript beyond count 
ORA-06512: at line 144 
ORA-06512: at line 2958 


Here line 144 is not a source line that contains a subscript operator.

I have seen numerous reports on public form and Q&A sites mentioning the same issue.

test.sql

declare

type log_entry is record (stamp timestamp(3),
                          sevlvl varchar2(32),
                          message varchar2(1024));

type sql_log_buffer is varray(100) of log_entry;

log_buffer sql_log_buffer := sql_log_buffer();

procedure test is
begin
    log_buffer(2).message = 'not extended';
end;

begin
    test();
end;
/


and Connor said...

We'd need to see a concrete example ?

Anonymous block ? Stored Procedure ?

=====================

Compilation error line looks fine

SQL> declare
  2
  3  type log_entry is record (stamp timestamp(3),
  4                            sevlvl varchar2(32),
  5                            message varchar2(1024));
  6
  7  type sql_log_buffer is varray(100) of log_entry;
  8
  9  log_buffer sql_log_buffer := sql_log_buffer();
 10
 11  procedure test is
 12  begin
 13      log_buffer(2).message = 'not extended';
 14  end;
 15
 16  begin
 17      test();
 18  end;
 19  /
    log_buffer(2).message = 'not extended';
                          *
ERROR at line 13:
ORA-06550: line 13, column 27:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.


and when I fixed that, run time error looked fine as well

SQL> declare
  2
  3  type log_entry is record (stamp timestamp(3),
  4                            sevlvl varchar2(32),
  5                            message varchar2(1024));
  6
  7  type sql_log_buffer is varray(100) of log_entry;
  8
  9  log_buffer sql_log_buffer := sql_log_buffer();
 10
 11  procedure test is
 12  begin
 13      log_buffer(2).message := 'not extended';
 14  end;
 15
 16  begin
 17      test();
 18  end;
 19  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 13
ORA-06512: at line 17


ie, we failed at line 13, which came from a routine called at line 17

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Exception blocks mask the line number

Dan, March 09, 2018 - 5:26 pm UTC

Adding an exception block masks the line number.

declare

type log_entry is record (stamp timestamp(3),
                          sevlvl varchar2(32),
                          message varchar2(1024));

type sql_log_buffer is varray(100) of log_entry;

log_buffer sql_log_buffer := sql_log_buffer();

procedure test is
begin
    log_buffer(2).message := 'not extended';
exception
    when others then
        raise; 
end;

begin
    test();
end;


Results in
ORA-06533: Subscript beyond count
ORA-06512: at line 16
ORA-06512: at line 20


No reference to line 13 where the error really occurred.

For that reason, I usually somehow incorporate DBMS_UTILITY.FORMAT_ERROR_BACKTRACE into the output/log message.

exception
    when others then
        raise_application_error (-20001, 'Error occurred: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, true);
end;



Resulting in

ORA-20001: Error occurred: ORA-06512: at line 13
ORA-06512: at line 16
ORA-06533: Subscript beyond count
ORA-06512: at line 20


Now I have a chance.


Connor McDonald
March 10, 2018 - 5:06 am UTC

I *do* see a reference

SQL> declare
  2
  3  type log_entry is record (stamp timestamp(3),
  4                            sevlvl varchar2(32),
  5                            message varchar2(1024));
  6
  7  type sql_log_buffer is varray(100) of log_entry;
  8
  9  log_buffer sql_log_buffer := sql_log_buffer();
 10
 11  procedure test is
 12  begin
 13      log_buffer(2).message := 'not extended';
 14  exception
 15      when others then
 16          raise;
 17  end;
 18
 19  begin
 20      test();
 21  end;
 22  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 16
ORA-06512: at line 13
ORA-06512: at line 20


but I do take your point, which is - if you trap the error, then you'll get a reference to the point of raising it. But not much we can do about that (besides using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE as you mentioned) because the moment you trapped the error...it is *no longer* an error.

Error doesn't tell the correct line number - why?

Rob, August 01, 2020 - 2:21 am UTC

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!
Connor McDonald
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


Helpful Information

Rob, August 04, 2020 - 8:02 pm UTC

Thank you Connor! It was really helpful.
Connor McDonald
August 05, 2020 - 3:06 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library