DIY Dynamic Views
The method described here is based on an article called "DIY$: Do-It-Yourself Dynamic Views" from Oracle Magazine, which is no longer available. The example below allows the alert log to be read using SQL in a similar manner to the user_source view.
First an object type is defined to hold each row of data.
CREATE TYPE Alert_Row_Type AS OBJECT ( line NUMBER(10), text VARCHAR2(2000) ); /
Next a table type is defined using the previous object type as a rowtype.
CREATE TYPE Alert_Type IS TABLE OF Alert_Row_Type; /
Next a function is defined to read the external data, place it in the table type and return the table type. Remember that the
UTL_FILE_DIR initialization parameter must be set correctly to allow
UTL_FILE package to access the filesystem.
CREATE OR REPLACE FUNCTION Get_Alert RETURN Alert_Type IS Alert_Tab Alert_Type := Alert_Type(); l_file UTL_FILE.file_type; l_line NUMBER(10) := 1; l_text VARCHAR2(2000); BEGIN l_file := UTL_FILE.fopen('C:\oracle\admin\TSH1\bdump', 'tsh1ALRT.LOG', 'r'); BEGIN LOOP utl_file.get_line(l_file, l_text); Alert_Tab.Extend; Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(l_line, l_text); l_line := l_line + 1; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; UTL_FILE.fclose(l_file); RETURN Alert_Tab; END; /
Finally a view is created using the
Cast() functions to cast the table type into a relational table format.
CREATE OR REPLACE VIEW diy$alert AS SELECT * FROM Table(Cast(Get_Alert() As Alert_Type));
At this point the contents of the flat file can be queried and joined like a relational table using the DIY view. Since the file is read every time the view is accessed the data will always be current.
This approach can be extended for structured files, such as CSV files, which can be read and divided into their individual elements using the
Substr function. If the object type is altered to contain all these elements these flat files can be queried like relational tables.
Remember, this method is not necessary in Oracle9i as it supports External Tables.
Hope this helps. Regards Tim...