create table msta_alertfile(
 line number,
 prio number,
 datum date,
 text varchar2(4000));




create or replace procedure Get_alert_file is
	dir     	varchar2(1000);
	dummy		number;
	start_pos	number :=1;
	stop_pos	number :=1;
	v_text 		varchar2(2000);
	line 		number:=0;
	amt		number :=2;
	v_clob		CLOB;
	v_file		bfile := null;
begin

	select value into dir
	from v$parameter
	where name ='BACKGROUND_DUMP_DEST';
	
	select count(*) into dummy
	from dba_directories
	where directory_name ='ALERTLOG_DIR';
	
	if dummy  > 0 then
		execute immediate 'DROP DIRECTORY ALERTLOG_DIR';
	end if;
	
	execute immediate 'CREATE DIRECTORY ALERTLOG_DIR as '''|| dir || '''';
	
	v_file := BFILENAME ('ALERTLOG_DIR','orclALRT.LOG');
	DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
	DBMS_LOB.FILEOPEN(v_file, DBMS_LOB.FILE_READONLY);
	DBMS_LOB.LOADFROMFILE(v_clob,v_file,DBMS_LOB.getlength(v_file));
	commit;
	dbms_lob.fileclose (v_file);

	loop
		start_pos := start_pos +1;
		stop_pos := dbms_lob.instr(v_clob, chr(10), start_pos);
		amt :=  stop_pos - start_pos -1;

		if amt = 0 then
			NULL; --kein Inhalt der Zeile
		elsif amt is null or amt < 0 then
			EXIT; --Ende des LOB

		ELSE
			dbms_lob.read(v_clob, amt, start_pos, v_text);
			line := line +1;
			insert into msta_alertfile values
			(line, 1, null, v_text);
			
			if mod(line, 500) =0 then
				commit;
			end if;
		end if;
	end loop;
	commit;
end;
/