Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

DIY Dynamic Views

The method described in this article is based on the DIY$: Do-It-Yourself Dynamic Views article found on the Oracle Magazine website. 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 Table() and 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...

Back to the Top.