Re: Line number in a stored procedure or function

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Wed, 15 Dec 1999 16:24:23 -0800
Message-ID: <839bqj$1e8$1@plo.sierra.com>

I use my own section names and report them in a run log (table)

create or replace procedure test is
  c_err_msg varchar2(100);
  section_name varchar2(50);
begin
  section_name := 'First part';
insert into ....
  seection_name := 'Second part';
insert into ...
  etc.

exception
  when others then
    c_err_msg:=substr(sqlerrm,1,100);
    log_error('Section: ' || section_name || ' Error: ' || c_err_msg);
/* this also marks the date time */

end;
/

Marc Weinsock <MWeinstock_at_lds.com> wrote in message news:3857FCDC.82B55D0B_at_lds.com...
When using raise_application_error() function in an exception clause, does anyone know how to show the exact line number where the error happened. In the following I want to see line number 4 not line number 8.

SQL> create table temp

2 col1 varchar2(1));
Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE procedure test is
  2      c_err_msg       varchar2(100);

  3
  3 BEGIN
  4 insert into temp(col1) values ('12345');   5
  5 EXCEPTION

  6      when OTHERS then
  7      c_err_msg := sqlerrm;
  8      raise_application_error(-20000,c_err_msg);

9 END;
10 /
Procedure created.
SQL>
SQL> execute test
begin test; end;
*
ERROR at line 1:

ORA-20000: ORA-01401: inserted value too large for column
ORA-06512: at "WS_DEV.TEST", line 8
ORA-06512: at line 1

SQL>
SQL>
--

Marc Weinstock
Logical design Solutions
55 Broadway - 21st Floor
New York, New York 10006
Phone: 800.221.5327 x2136
Fax: 212-825-2245
E-Mail: MWeinstock_at_lds.com
Web: http://www.lds.com Received on Wed Dec 15 1999 - 18:24:23 CST