with x as (select rownum r1 from all_objects where rownum < 10001)
,y as (select rownum r2 from all_objects where rownum < 51)
select 'ora- ' || lpad(rownum,5, '0 ') ora_num, sqlerrm(rownum) msg_text
from x, y
/
Of course I am writing without testing (from home) ...
Raj
--- Jared.Still@(protected) wrote:
> oracle-l-bounce@(protected) 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@(protected)
> 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
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>
