Re: Where is line 21? ++ USER_TRIGGERS vs USER

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 16 Nov 2004 10:36:14 -0800
Message-ID: <92eeeff0.0411161036.45590fc1@posting.google.com>

> And not to rub it in ... Oracle does not report errors where
> they first originate but often later on in the code.

I beg to differ Daniel. Here is an exerpt from a 10g database. First bit of code does not compile OK. See the LINE/COL correctly identify the offending lines in the code which can be extracted from user_source with the same line no given in the error stack.

2nd bit of code actually compiles but throws an exception. Once again the line is correctly identified by the compiler and the user_source. Oracle will attempt to list the actual error and exception in the first line of error stack. 2nd line is mostly ORA-06512 which shows the <schema>.<object_name> and any line no. Any subsequent lines after the ORA-06512 are additional information/errors that are encountered by the compiler. Now if you want a point from where to start debugging... line no in ORA-6512 is where I mostly start... going backwards or forward.

SQL> create or replace procedure foo
2 as
3 begin

  4     dbms_output.put_line('This is line 4 OK');
  5     dbms_output.put_line('This is line 5 OK');
  6     dbms_output.put_line('This is line 6 NOT OK';
  7     dbms_output.put_line('This is line 7 OK');
  8     dbms_output.put_line('This is line 8 NOT OK);

9 end foo;
10 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE FOO:

LINE/COL ERROR

-------- -----------------------------------------------------------------
6/48     PLS-00103: Encountered the symbol ";" when expecting one of
the
         following:
         . ( ) , * @ % & | = - + < / > at in is mod remainder not
         range rem => .. <an exponent (**)> <> or != or ~= >= <= <>
         and or like as between from using || multiset member
         SUBMULTISET_
         The symbol ")" was substituted for ";" to continue.

8/25     PLS-00103: Encountered the symbol "This is line 8 NOT OK);
         end foo;" when expecting one of the following:
         ( ) - + case mod new not null others <an identifier>

LINE/COL ERROR

-------- -----------------------------------------------------------------
         <a double-quoted delimited-identifier> <a bind variable>
         table avg count current exists max min prior sql stddev sum
         variance execute multiset the both leading trailing forall
         merge year month DAY_ hour minute second timezone_hour
         timezone_minute timezone_region timezone_abbr time timestamp
         interval date
         <a string literal wit

SQL> select text from user_source where name = 'FOO' and line = 6;

TEXT

dbms_output.put_line('This is line 6 NOT OK';

SQL> create or replace procedure foo
2 as
3 begin

  4     dbms_output.put_line('This is line 4 OK');
  5     dbms_output.put_line('This is line 5 OK');
  6     raise_application_error(-20001, 'Error at line 6');
  7     dbms_output.put_line('This is line 7 OK');

8 end foo;
9 /

Procedure created.

SQL> exec foo;
BEGIN foo; END;

*
ERROR at line 1:

ORA-20001: Error at line 6
ORA-06512: at "TEST.FOO", line 6
ORA-06512: at line 1

SQL> select text from user_source where name = 'FOO' and line = 6;

TEXT

raise_application_error(-20001, 'Error at line 6');

SQL> Regards
/Rauf Received on Tue Nov 16 2004 - 12:36:14 CST

Re: Where is line 21? ++ USER_TRIGGERS vs USER_SOURCE