Inhalte von OS-Directories über External Table anzeigen - 2

Nachdem ich bei einem Kunden gesehen hatte, wie leicht man die Liste der in einem OS-Verzeichnis vorliegenden Dateien in 11g über External Tables anzeigen kann, und hier einen Link zu Adrian Billingtons Erläuterung des Features unterbrachte, folgt nun ein praktisches Beispiel für 11.2 und Windows 7 (basierend auf den Ausführungen des Herrn Billington):

Zunächst benötigt man ein Directory-Objekt, das auf ein OS-Verzeichnis verweist:

create directory data_dir as 'c:\temp';

In diesem Verzeichnis legt man nun eine Batch-Datei an - hier directory_list.bat - , die den Befehl zur Anzeige der Verzeichnis-Inhalte enthält:

@echo off
dir /N c:\temp

In der Datenbank kann man nun eine External Table anlegen, die die Batch-Datei über einen PREPROCESSOR-Befehl ausführt:

CREATE TABLE directory_list
( file_date VARCHAR2(50)
, file_time VARCHAR2(50)
, file_size VARCHAR2(50)
, file_name VARCHAR2(255)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS
  (
     RECORDS DELIMITED BY NEWLINE
     LOAD WHEN file_size != '
'
     PREPROCESSOR data_dir: 'directory_list.bat'
     FIELDS TERMINATED BY WHITESPACE
  )
  LOCATION ('test.txt')
)
REJECT LIMIT UNLIMITED;

Da die External Table neben den Datei-Informationen auch noch Header- und Footer-Elemente enthält, ist es sinnvoll, diese Anteile mit Hilfe einer View zu filtern:

CREATE VIEW dir_list
AS
SELECT file_name
     , to_char(TO_DATE(file_date||','||file_time,'DD/MM/YYYY HH24:MI'), 'dd.mm.yyyy hh24:mi:ss') AS file_time
     , TO_NUMBER(file_size,
             'fm999,999,999,999') AS file_size
  FROM directory_list
 WHERE REGEXP_LIKE( file_date, '[0-9]{2}.[0-9]{2}.[0-9]{4}');

Die erzeugte View enthält dann die Informationen des DIR-Kommandos:

FILE_NAME                                FILE_TIME            FILE_SIZE
---------------------------------------- ------------------- ----------
directory_list.bat                       01.03.2011 20:28:00         25
DIRECTORY_LIST_3192_3532.bad             13.03.2011 15:10:00         62
DIRECTORY_LIST_3192_3532.dsc             13.03.2011 15:10:00        149
DIRECTORY_LIST_3192_3532.log             13.03.2011 15:10:00       3152
DIRECTORY_LIST_3932_3424.bad             01.03.2011 20:31:00         62
DIRECTORY_LIST_3932_3424.dsc             01.03.2011 20:31:00        149
DIRECTORY_LIST_3932_3424.log             01.03.2011 20:31:00      14380
EXT_UPTIME_3416_2432.bad                 16.02.2011 14:32:00         62
EXT_UPTIME_3416_2432.log                 16.02.2011 14:32:00       6433
EXT_UPTIME_412_1316.bad                  15.02.2011 20:55:00         65
EXT_UPTIME_412_1316.log                  15.02.2011 20:55:00        919
EXT_UPTIME_412_2632.bad                  15.02.2011 21:23:00         65
EXT_UPTIME_412_2632.log                  15.02.2011 21:23:00      22975
test.txt                                 01.03.2011 20:16:00          0
uptime.csv                               16.02.2011 14:11:00      30258
uptime_.csv                              15.02.2011 20:41:00      54541

Nützlich ist eine solche Möglichkeit z.B. dann, wenn man keinen direkten Zugriff auf den Serverrechner besitzt.