rem -----------------------------------------------------------------------
rem Filename:   fileview.sql
rem Purpose:    Create a database view on top of a file
rem Date:       27-Nov-2002
rem -----------------------------------------------------------------------

-- Utl_file_dir must be set to the directory you want to read from
show parameter utl_file_dir

-- Define the table's columns
CREATE OR REPLACE TYPE Alert_Row_Type AS OBJECT ( 
	line NUMBER(8), 
	text VARCHAR2(2000) 
);
/

-- Create a table of many row objects
CREATE OR REPLACE TYPE Alert_Type IS TABLE OF Alert_Row_Type; 
/ 

-- Create a function to read the data into the table 
CREATE OR REPLACE FUNCTION Get_Alert 
RETURN Alert_Type 
IS 
  Alert_Tab Alert_Type := Alert_Type(Alert_Row_Type(NULL, NULL)); 
  v_file  Utl_File.File_Type; 
  v_line  NUMBER(10) := 1; 
  v_text  VARCHAR2(2000); 
  b_read  BOOLEAN    := TRUE; 
  b_first BOOLEAN    := TRUE; 
BEGIN 
  dbms_output.put_line('About to open file...');
  v_file := Utl_File.FOpen('/app/oracle/admin/orcl/bdump', 'alert_orcl.log', 'r'); 
  WHILE b_read LOOP 
   BEGIN 
     Utl_File.Get_Line(v_file, v_text); 
     IF b_first THEN 
       b_first := FALSE; 
     ELSE 
       Alert_Tab.Extend; 
     END IF; 

     Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(v_line, v_text); 
     v_line := v_line + 1; 
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
       b_read := FALSE; 
   END; 
 END LOOP; 
 Utl_File.FClose(v_file);
 RETURN Alert_Tab; 
EXCEPTION 
 WHEN utl_file.invalid_path       THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_path');
 WHEN utl_file.invalid_mode       THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_mode');
 WHEN utl_file.invalid_filehandle THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_filehandle');
 WHEN utl_file.invalid_operation  THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_operation');
 WHEN utl_file.read_error         THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.read_error');
 WHEN utl_file.write_error        THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.write_error');
 WHEN utl_file.internal_error     THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.internal_error');
 WHEN OTHERS                      THEN
   RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.other_error');   
END; 
/ 
show errors

-- Create a view to get the info from the function 
CREATE OR REPLACE FORCE VIEW alert_log_file AS 
	SELECT LINE, TEXT 
	FROM Table(Cast(Get_Alert() As Alert_Type))
/

-- Test it!!!
set pages 50000
select * from alert_log_file
where  text like '%ORA-%'
/