Oracle Database 11g Release 2に関する10の重要なこと – askTom Live -
Point6：　You’ve got Mail　【ユーガットメール】

（ 目次 ）

6.You’ve got Mail　【ユーガットメール】

タイトルには「メール」とありますが、Eメールの話ではありません。
Oracle Database 11g R2では、ファイルシステムに新しいファイルが作成されたことを検知できるようになりました。ファイルが作成されたらイベントを発生させ、ストアド・プロシージャを実行したり、アプリケーションがメッセージを受け取ってなんらかの作業をおこなうためのキューに入れたりすることができます。
つまり、File Watcher が新規ファイルの作成を監視して、自動的に適切な処理をおこなってくれるのです。

たとえば、レポートファイルを格納するためのディレクトリにファイルが作成されたら、レポートをデータベースにロードしなければならないとします。
これまでは、おそらくクローンジョブなどを使って5分ごとにディレクトリを調べ、ファイルがあれば別の場所に移動していたのではないでしょうか。
そのようなコードはもう必要ありませんし、コードが少なければアプリケーション・バグも減ります。
すべてデータベースに任せればよいのです。
さっそく、どのように動作するか見てみましょう。

以下のコードをご覧ください。
まずは、データベース内に認証情報(credential)を作成します。これにより、私のユーザー名とパスワードが暗号化されて、データベース内に安全に保存されます。
ファイルの操作には、データベースのユーザー情報ではなく、この認証情報を使用します。セキュリティを考慮すると、ほとんど制約のない特権ユーザーであるオラクルのアカウントよりも、個別のIDを使うほうが安全です。

ops$tkyte%ORA11GR2> begin
  2    dbms_scheduler.create_credential(
  3       credential_name => 'watch_credential',
  4       username        => 'tkyte',
  5       password        => ‘foobar');
  6  end;
  7  /

PL/SQL procedure successfully completed.

次に、ファイルが置かれるディレクトリを作成します。
ファイル名、ファイルのタイムスタンプ、ファイルの内容をロードしたいので、これらを読み込める表も作成します。

ops$tkyte%ORA11GR2> create or replace directory MY_FILES as '/home/tkyte/files'
  2  /

Directory created.

ops$tkyte%ORA11GR2> create table files
  2  (
  3     file_name varchar2(100),
  4     loaded timestamp,
  5     contents  clob
  6  );

Table created.

次に、ファイルが作成された時にデータベースにロードをするストアド・プロシージャを設定します。このストアド・プロシージャでは、新しいデータタイプSCHEDULER_FILEWATCHER_RESULTを使用しています。これは、ファイル生成時のイベントに関するメッセージで、ファイルのタイムスタンプ、ディレクトリ、ファイル名などの属性をもちます。
そして、表にレコードを作成し、dbms_lob.loadfromfileでファイルシステムからファイルをロードしてデータベースに格納します。

ops$tkyte%ORA11GR2> create or replace procedure process_files
  2  (p_payload in sys.scheduler_filewatcher_result)
  3  is
  4      l_clob clob;
  5      l_bfile bfile;
  6  begin
  7      insert into files
  8      (loaded, file_name, contents )
  9      values (p_payload.file_timestamp,
 10       p_payload.directory_path || '/' || p_payload.actual_file_name,
 11       empty_clob()
 12      ) returning contents into l_clob;
 13  
 14      l_bfile := bfilename( 'MY_FILES', p_payload.actual_file_name );
 15      dbms_lob.fileopen( l_bfile );
 16      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
 17      dbms_lob.fileclose( l_bfile );
 18  end;
 19  /

Procedure created.

あとは、スケジューラを使ってデータベースにこのストアド・プロシージャを実行させるだけです。
スケジューラは、データベースに定期的な処理をおこなわせるためのもので、再帰的に動作します。ストアド・プロシージャとイベントメッセージを指定します。

ops$tkyte%ORA11GR2> begin
  2    dbms_scheduler.create_program(
  3      program_name        => 'file_watcher',
  4      program_type        => 'stored_procedure',
  5      program_action      => 'Process_Files',
  6      number_of_arguments => 1,
  7      enabled             => false);
  8    dbms_scheduler.define_metadata_argument(
  9      program_name        => 'file_watcher',
 10      metadata_attribute  => 'event_message',
 11      argument_position   => 1);
 12    dbms_scheduler.enable('file_watcher');
 13  end;
 14  /

PL/SQL procedure successfully completed.

そして、「このディレクトリを監視せよ」と指示します。 これらの資格情報を使って、このマスクに一致するファイルを探すよう指示するのです。

ops$tkyte%ORA11GR2> begin
  2    dbms_scheduler.create_file_watcher(
  3      file_watcher_name => 'my_file_watcher',
  4      directory_path    => '/home/tkyte/files',
  5      file_name         => '*',
  6      credential_name   => 'watch_credential',
  7      destination       => null,
  8      enabled           => false);
  9  end;
 10  /

PL/SQL procedure successfully completed.

次に、これを起動します。今回は、ファイルが10バイト以上であればイベントを発生させ、ストアド・プロシージャを実行するように設定します。

ops$tkyte%ORA11GR2> begin
  2    dbms_scheduler.create_job(
  3      job_name        => 'my_file_job',
  4      program_name    => 'file_watcher',
  5      event_condition => 'tab.user_data.file_size > 10',
  6      queue_spec      => 'my_file_watcher',
  7      auto_drop       => false,
  8      enabled         => false);
 10  end;
 11  /

PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_scheduler.enable('my_file_watcher,my_file_job');

PL/SQL procedure successfully completed.

ファイルがディレクトリに書き込まれたら、それが自動的に表にロードされます。

ops$tkyte%ORA11GR2> select * from files;

FILE_NAME                      LOADED                         CONTENTS
------------------------------ ------------------------------ ---------------
/home/tkyte/files/file4.txt    07-OCT-09 07.37.22.000000 PM   hello world, ho
                                                              w are you
                                                              hello world, ho
                                                              w are you
                                                              hello world, ho
                                                              w are you
                                                              hello world, ho
                                                              w are you

これで、かつては手作業でしていたことが自動化されました。以前は同じことをおこなうために、たくさんのコードを書かなくてはなりませんでした。
今では、ごく短いストアド・プロシージャを書くだけで、データベースが実行してくれるようになったのです。

▲ ページTOPに戻る

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