Re: data dictionary view with all oracle errors in it?

From: <Jared.Still_at_radisys.com>
Date: Wed, 28 Jul 2004 10:07:39 -0700
Message-ID: <OF82D1E0BC.53C4C0FC-ON88256EDF.005DF610-88256EDF.005E16D3@radisys.com>

oracle-l-bounce_at_freelists.org wrote on 07/27/2004 08:43:42 AM:
> Someone once posted here a cool script to retrieve the Oracle errors
(also
> event descriptions) in PL/SQL:
>
> SET SERVEROUTPUT ON
> DECLARE
> err_msg VARCHAR2(1000);
> BEGIN
> dbms_output.enable (1000000);
> FOR err_num IN 10000..11005
> -- FOR err_num IN 38001..39000
> LOOP
> err_msg := SQLERRM (-err_num);
> IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
> dbms_output.put_line (err_msg);
> END IF;
> END LOOP;
> END;
> /
>
> Tanel.
>

I find this version to be a little more useful. The dbms_output buffer is not large enough for all messages, and some must be broken in to segments to display via put_line (255 char limit)

Jared

drop table oerrors;
create table oerrors ( errnum number, msg varchar2(4000));

DECLARE

        err_msg VARCHAR2(1000);
        oline varchar2(1000);
BEGIN
        FOR err_num IN 1..65535
        LOOP
                err_msg := SQLERRM (-err_num);
                IF err_msg NOT LIKE '%Message '||err_num||' not found%' 
THEN
                        insert into oerrors values(err_num, err_msg);
                END IF;
        END LOOP;
        commit;

END;
/

col msg format a100
set linesize 120
set pagesize 0

spool oerrors.txt

select errnum, msg
from oerrors
order by errnum
/

spool off

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 28 2004 - 12:04:50 CDT