Re: how to get message of line/col in EXCEPTION?

cheasy_at_gmail.com wrote:
> CREATE OR REPLACE PROCEDURE pro_testErr IS
> n_num NUMBER;
> BEGIN
> SELECT 1/0 INTO n_num FROM dual WHERE 1=1;
> END pro_testErr;
> ------------------------------------------------------------------------
> SQL> exec pro_testerr;
> BEGIN pro_testerr; END;
>
> *
> ERROR at line 1:
> ORA-01476: divisor is equal to zero
> ORA-06512: at "CE.PRO_TESTERR", line 4
> ORA-06512: at line 1
> ------------------------------------------------------------------------
>
> ================================================================
> CREATE OR REPLACE PROCEDURE pro_testErr IS
> n_num NUMBER;
> BEGIN
> SELECT 1/0 INTO n_num FROM dual WHERE 1=1;
>
> EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line(SQLERRM);
> END pro_testErr;
>
> SQL> set serveroutput on
> SQL> exec pro_testerr;
> ORA-01476: divisor is equal to zero
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
>
> why SQLERRM not include message of line/col? how to get that message in
> procedure? thx.

In 10g, use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in exception handler. In previous releases you can't retrieve error backtrace in PL/SQL. See 10g docs for details on the function and examples of use.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Fri Oct 13 2006 - 02:16:23 CDT