Oracle Database 11g Release 2に関する10の重要なこと – askTom Live -
Point3：　Execute on a directory　【圧縮ファイルでの外部表】

（ 目次 ）

3. Execute on a directory　【圧縮ファイルでの外部表】

これまで、ディレクトリ・オブジェクトに対する操作はREAD（読込み）とWRITE（書込み）しかできませんでしたが、Oracle Database 11g R2ではEXECUTE（実行）が使えるようになりました。ディレクトリに置いた実行可能なプログラムを、データベース上で直接実行できるようになったのです。

さらに、Oracle Database 11g R2では外部表のOSファイルの前処理（プリプロセッサ）も可能になりました。たとえば、ZIP等のコマンドライン解凍ユーティリティをプリプロセッサ・プログラムに指定することで、ファイルシステムの圧縮ファイルを直接処理できるようになります。

Oracleデータベースからこのコマンドライン・プログラムを呼び出して安全に操作するためには、呼び出したいプログラムが置かれたディレクトリに対するEXECUTEを許可する必要があります。これは、Oracleデータベースにどのプログラムを実行させるかを正しくコントロールすることで、「トロイの木馬」プログラムを実行してしまうことを避けるためです。

たとえば、誰かが巨大なgzipの圧縮ファイルを送ってきたとします。
Oracle Database 11g R2以前であれば、まずファイルを解凍してからロードする必要がありました。Oracle Database 11g R2では、解凍とロードを1つのステップでおこなうことができます。
実際に手順を見てみましょう。

これが私のファイルが置かれたディレクトリと、これらのファイルを解凍するためのプログラムが置かれたディレクトリです。

ops$tkyte%ORA11GR2> CREATE or replace DIRECTORY load_dir
  2  AS '/mnt/hgfs/docs/Presentations/Seminar/11gr2'
  3  /

Directory created.

ops$tkyte%ORA11GR2> CREATE or replace DIRECTORY exec_dir
  2  AS '/mnt/hgfs/docs/Presentations/Seminar/11gr2'
  3  /

Directory created.

下記の12行目にある「ORGANIZATION external」が外部表です。
17行目の赤字を見てください。ここではデータを解凍するプログラムを指定しています。圧縮ファイルemp.dat.gzに対して解凍プログラムgunzipを実行し、解凍されたデータを標準出力に出力することにします。
外部表から対象プログラムを選択すると、自動的にスクリプトを実行し、データを解凍して標準出力に出力してロードします。
これにより、解凍したファイルが500GBだったとすれば、500GBのディスクスペースが節約できます。

ops$tkyte%ORA11GR2> CREATE TABLE EMP_ET
  2  (
  3    "EMPNO" NUMBER(4),
  4    "ENAME" VARCHAR2(10),
  5    "JOB" VARCHAR2(9),
  6    "MGR" NUMBER(4),
  7    "HIREDATE" DATE,
  8    "SAL" NUMBER(7,2),
  9    "COMM" NUMBER(7,2),
 10    "DEPTNO" NUMBER(2)
 11  )
 12  ORGANIZATION external
 13  ( TYPE oracle_loader
 14    DEFAULT DIRECTORY load_dir
 15    ACCESS PARAMETERS
 16    ( RECORDS DELIMITED BY NEWLINE
 17          preprocessor  exec_dir:'run_gunzip.sh'
 18      FIELDS TERMINATED BY "|" LDRTRIM
 19    )
 20    location ( 'emp.dat.gz')
 21  )
 22  /
Table created.


ops$tkyte%ORA11GR2> !file emp.dat.gz
emp.dat.gz: gzip compressed data, was "emp.dat",
 from Unix, last modified: Wed Oct  7 12:48:53 2009

ops$tkyte%ORA11GR2> !cat run_gunzip.sh
#!/bin/bash

/usr/bin/gunzip -c $*

ops$tkyte%ORA11GR2> select empno, ename from emp_et where rownum <= 5;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN

このような便利な機能が登場すると、私はいつも、できるだけ開発者が意図していないような利用方法を探します。その1つがディレクトリの一覧表示です。
私は長い間、ディレクトリの一覧表示を実現したいと考えていました。PL/SQLによって、ファイルの読込みや書込み、削除、ファイル名の書換えが可能になりました。しかし、ディレクトリ内にどのようなファイルがあるかを表示することはできませんでした。
この機能を使うことで、ディレクトリ内のファイルをリストアップできるようになったのです。

下のコードを見てください。
まず表を作成します。UNIXのlist filesコマンドにならい、名前を「ls」とします。列と行は1つです。これは外部表で、スクリプトを実行します。スクリプトはファイルとディレクトリの一覧を表示するためのコマンドlsを実行します。外部表lsの「*」を選択すると、シェルスクリプトを実行します。シェルスクリプトは、ファイルを置きたいディレクトリにcdして、実行結果を標準出力に出力します。これが、データベース表の行になります。

ops$tkyte%ORA11GR2> CREATE TABLE ls
  2  (
  3    line varchar2(255)
  4  )
  5  ORGANIZATION external
  6  ( TYPE oracle_loader
  7    DEFAULT DIRECTORY load_dir
  8    ACCESS PARAMETERS
  9    ( RECORDS DELIMITED BY NEWLINE
 10          preprocessor  exec_dir:'run_ls.sh'
 11      FIELDS TERMINATED BY "|" LDRTRIM
 12    )
 13    location ( 'run_ls.sh')
 14  )
 15  /

Table created.


ops$tkyte%ORA11GR2> select * from ls;

LINE
---------------------------------------------------------------
11 things about 11gr2.ppt
diyp.sql
ebr.old.sql
ebr.sql
emp.ctl
emp.dat.gz
EMP_ET_26122.log
emp_et.sql
LS_26122.log
run_gunzip.sh
run_ls.sh

11 rows selected.

これはlsのほか、ps（process status）やvmstat、iostatなどのUNIXコマンドでも実行できます。
Windowsにも、標準出力に出力するさまざまなコマンドがあります。CやVisual Basicを使うこともできます。

▲ ページTOPに戻る

Thomas(Tom) Kyte (トム・カイト）
2000年にAsk Tomブログ( http://asktom.oracle.com ) を開設して以来10年にわたり、全世界のオラクル技術者のありとあらゆる質問に答え、データベース技術の活用を世に広めてきた世界的に有名なエバンジェリスト。