create or replace procedure write_file
( DIR IN VARCHAR2,filename IN VARCHAR2,text IN varchar2)
is
file utl_file.file_type;
begin
file:=utl_file.fopen(DIR, filename,'a');
utl_file.put_line(file,text);
utl_file.new_line(file);
utl_file.fclose(file);
end;
/
create or replace
procedure read_file ( DIR IN VARCHAR2,filename IN VARCHAR2, report IN VARCHAR2)
is
file utl_file.file_type; --file handler
buffer char(1000);
--EOF boolean:=false;
begin
if not utl_file.is_open(file) then
file := utl_file.fopen(DIR,filename,'R');
end if;
loop
begin
utl_file.get_Line(file,buffer,32767);
--dbms_output.put_line(buffer);
if instr(buffer,'ORA-')=1 then
---dbms_output.put_line(buffer);
write_file(DIR, report,buffer);
end if;
exception
when no_data_found then
exit;
end;
end loop;
-- To run everynight at midnight starting tonight
exec dbms_job.submit
(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');
