Home » Articles » 8i » Right here

DIY Dynamic Perspectives

The mode described this is according to a piece of writing known as “DIY$: Do-It-Yourself Dynamic Views” from Oracle Booklet, which is not to be had. The instance under permits the alert plank to be learn the use of SQL in a homogeneous way to the user_source view.

First an object sort is outlined to conserve each and every row of information.

CREATE TYPE Alert_Row_Type AS OBJECT
(
  order  NUMBER(10),
  textual content  VARCHAR2(2000)
);
/

Nearest a desk sort is outlined the use of the former object sort as a rowtype.

CREATE TYPE Alert_Type IS TABLE OF Alert_Row_Type;
/

Nearest a serve as is outlined to learn the exterior knowledge, park it within the desk sort and go back the desk sort. Understand that the UTL_FILE_DIR initialization parameter should be all set accurately to permit UTL_FILE bundle to get right of entry to 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.Lengthen;
      Alert_Tab(Alert_Tab.Latter) := 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;
/

In the end a view is created the use of the Desk() and Forged() purposes to forged the desk sort right into a relational desk layout.

CREATE OR REPLACE VIEW diy$alert AS
SELECT * 		
FROM Desk(Forged(Get_Alert() As Alert_Type));

At this level the contents of the flat record may also be queried and joined like a relational desk the use of the DIY view. Because the record is learn each day the view is accessed the knowledge will at all times be wave.

This means may also be prolonged for structured information, similar to CSV information, which may also be learn and divided into their particular person components the use of the Substr serve as. If the thing sort is altered to comprise these kind of components those flat information may also be queried like relational tables.

Take into accout, this mode isn’t vital in Oracle9i because it helps External Tables.

Hope this is helping. Regards Tim…

Back to the Top.