Reading alert.log in PL\SQL

In this short article I'll show simple block, to read errors in alert.log without having access to server itself. Be carefully, if log file big enough - script going to work forever. In my case - 300mb file parsed in approx 40 sec + 10 sec to output result. However, you may abort it anytime.
Let's find alert.log and create work directory. Also you neeed privilege to work with UTL_FILE:


SQL> select physical_filename,physical_path from v$diag_dir_ext where physical_filename like 'alert%.log'

SQL> create or replace directory TRACE as 'path_where_alert_log_is';



SQL> DECLARE
v_dir varchar2(50) := 'TRACE';
v_name varchar2(30) := 'alert_SID.log';
v_inputfile UTL_FILE.FILE_TYPE;
v_newline varchar2(500);
v_newline_1 varchar2(500);
v_offset number;
BEGIN
dbms_output.enable(10000000);
v_inputfile := UTL_FILE.FOPEN(v_dir,v_name,'r');
LOOP
BEGIN
utl_file.get_line(v_inputfile, v_newline);
IF substr(v_newline,1,4) = 'ORA-' 
 and substr(v_newline,1,9) not in ('ORA-00942','ORA-06550','ORA-12012') /* you may specify some expected errors here */
  then
dbms_output.put_line(v_newline);
utl_file.get_line(v_inputfile,v_newline_1);
dbms_output.put_line(v_newline_1);
end if;
END;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('EXIT!');
UTL_FILE.FCLOSE(v_inputfile);
END;
/

The output would be something like: 
Sun Aug 05 06:00:43 2012
ORA-16957: SQL Analyze time limit interrupt
Sun Aug 05 06:04:44 2012
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.