ITコーディネータのシュウです。

夏休みに、妻の実家に行って来ました。長女だけは仕事の都合がつかず、一緒に行けなかったのが残念でしたが、あとの子供たちを引き連れて行ってきました。また、実家からそれほど遠くはないところに、世界遺産があるということで、そこに行ってきたのが、上の写真です。

ここは世界遺産合掌造り集落といって、岐阜県の白川郷・荻町集落、富山県の五箇山・菅沼集落、五箇山・相倉集落があり、今回行ってみたのは五箇山・菅沼集落です。東海北陸自動車道の五箇山インターチェンジからわりとすぐのところにあり、合掌造りの家屋が9棟くらいの3つの集落では一番小さな集落です。
そこに行くのに、駐車場のあるところからはエレベータで降りて行くのですが、別世界というか、昔にタイムスリップしたような不思議な感覚になりました。屋根がとても高く独特で、両手を合わせたような形になっていることから、合掌造りと呼ばれるようになったと思われるとのことです。
１軒１軒がとても大きかったですね。妻や子供たちも感動しながら、いっぱい写真を撮ってました。
それなりに多くの観光客（外国の方も結構いました）の人も来ていて、結構人気があるんですね。できれば、白川郷や相倉集落など他の集落も見て見たかったのですが、時間も遅くなってしまいそうだったので、菅沼集落だけを見て帰りました。
今度時間があれば、また妻と一緒に行ってみようっと！

 ＜本日の題材＞
Oracleの外部表

ORACLEの場合に、テーブルにCSVファイル用のようなデータを読込む処理にSQL*Loaderというツールがあります（前回の投稿の中で、ちょこっと使っています）が、実際のテーブルには読込まずに、あたかもテーブルが存在するかのようにそういった外部ファイルにアクセスすることができるツールとして、外部表というものがあります。今回はそれを取り上げてみたいと思います。

まず、準備として、外部表によって読取りまたは書込みが行われるファイルのデフォルトの位置としてのディレクトリを作成します。以前、UTL_FILEパッケージについて取り上げたときに、ディレクトリの作成を行いましたが、今回も同様に設定します。

構文：
CREATE [OR REPLACE] DIRECTORY <ディレクトリ名>
 AS ‘<ディレクトリ・パス>

例) SYSDBA権限でログインし、ディレクトリを作成します。

CREATE OR REPLACE DIRECTORY temp_dir
AS 'C:\temp';

※このディレクトリは、サーバ上のディレクトリになります。

実行ユーザ「BLOG」にディレクトリに対する読込権限と書き込み権限を付与します。

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

 外部表の作成は、CREATE TABLE...ORGANIZATION EXTERNAL文を使用します。
今まで何度か使用している商品マスタについて、その内容をCSVファイルにしたものを外部表としてアクセスしてみたいと思います。

例)
CREATE TABLE syomst_load
  (syo_cd         VARCHAR2(10),
   syo_name   VARCHAR2(20),
   bnrui             VARCHAR2(20),
   price             NUMBER(10))
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY temp_dir
   ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY ','
             (syo_cd,
              syo_name,
              bnrui,
              price
             )
     )
   LOCATION ('syomst.txt')
  )
REJECT LIMIT UNLIMITED;

 上記のSQL文にあるように、temp_dir というディレクトリ（実際は、c:\temp ）に、「syomst.txt」 というカンマ区切りの以下のようなCSVファイルを置いておきます。

A0001,チョコレート,お菓子,120
A0002,ビスケット,お菓子,200
A0003,ガム,お菓子,100
A0004,スナック,お菓子,140
B0001,りんご,果物,100
B0002,桃,果物,160
B0003,みかん,果物,80
B0004,梨,果物,120
C0001,キャベツ,野菜,160
C0002,にんじん,野菜,150
C0003,じゃがいも,野菜,100
C0004,玉ねぎ,野菜,150

ここまでの設定で、以下のSQLを実行すると、CSVファイルのデータを、あたかもテーブルが既に存在するかのように表示することができます。

SELECT * FROM syomst_load;

CSVデータの中身を変更してみます。
1行目と2行目のPRICEを130,210と変更して、CSVファイルを保存します。その後、再度先ほどのSQL文を実行します。

SELECT * FROM syomst_load;

結果は、たしかに、1行目の金額が130円、2行目が210円に変わっていることが確認できました。

上記は、カンマ区切りのCSVファイルでしたが、当然固定長のファイルでも可能です。
また今回は、CREATE TABLE のときの外部表の型として、ORACLE_LOADER型を使用しましたが、もう一つ、ORACLE_DATAPUMP型というのもあります。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルを使用しての処理が可能となります。これについては、また機会があれば、試してみたいと思います。

今日は以上まで

にほんブログ村