Dynamic Views

참고 : http://www.oracle-base.com/articles/8i/DIYDynamicViews.php

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...