define bdump = ' '
column dump_dest new_value bdump
select value dump_dest from v$parameter where name='background_dump_dest';

drop directory BDUMP;
create or replace directory BDUMP
as
'&&bdump';

drop table alert_log;
create table alert_log(
id number constraint pk_alert primary key, 
logline varchar2(4000))
organization index;

drop sequence alert_seq;
create sequence alert_seq start with 1 increment by 1 cache 1000;

create trigger trg_alert_log_ins
before insert on alert_log
for each row
begin
	select alert_seq.nextval into :new.id from dual;
end;
/

create or replace procedure read_alertlog(p_filename IN VARCHAR2,p_location in VARCHAR2)
as
/*
purpose:		procedure to read alert log at a daily interval.

requirements:	Directory object created for the background_dump_dest.
				Read permissions on this directory.
				Table called alert_log, column logline varchar2(4000).
				Oracle 9.2 or higher. 

parameters:		name of alert log, name of bdump directory. 

Version:		NL 27/11/2003 - created.
				NL 02/12/2003 - generalized.
				NL 16/02/2004 - actually commit the changes - doh.
				NL 16/02/2004 - timestamp of old file changed to seconds.
				NL 17/02/2004 - tidied up horrible invalid_operation exception.    
*/
fHandle			UTL_FILE.FILE_TYPE;
v_logline		VARCHAR2(4000);
v_newname		varchar2(50);
file_error		EXCEPTION;
PRAGMA EXCEPTION_INIT(file_error,-20100);
BEGIN
	begin
		fHandle := UTL_FILE.FOPEN(p_location,p_filename,'r',4000);
	exception
		when UTL_FILE.INVALID_OPERATION THEN -- probably specified wrong location or file name
			RAISE_APPLICATION_ERROR(-20100,'Check Alert Log location and existence');
		when others then
			raise;
	end;	
	loop
	begin
		UTL_FILE.get_line(fHandle,v_logline);
		insert into alert_log(logline) values(v_logline);
	exception -- reached end of file
		when no_data_found then exit;
	end;
	end loop;
	commit;
	UTL_FILE.FCLOSE(fHandle);
	v_newname := p_filename||to_char(sysdate,'..DDMMYYHH24MISS');
	begin
		UTL_FILE.FRENAME(p_location,p_filename,p_location,v_newname);
	exception
		when UTL_FILE.RENAME_FAILED THEN -- couldn't rename
			RAISE_APPLICATION_ERROR(-20100,'Check directory rights and available space');
		when others then
			raise;
	end;	 
end;
/