Oracle外部テーブル（External Table）の使い方：CSVデータをSQLで照会・活用する手順

外部テーブル（External Table）は、データベースの外部に保存されたフラットファイルからデータを受け取り、Oracle®がそのファイル内のデータを照会できるようにする機能です。

はじめに

Oracleは、SQL*Loaderがサポートするあらゆるファイル形式を解析できます。外部テーブルに対してはDML（データ操作言語）による直接的な更新操作は行えませんが、照会・結合・ソートといった操作にはDMLを利用できます。さらに、外部テーブルを対象にビューやシノニムを作成することも可能です。

外部テーブル機能を活用するには、ORACLE_LOADERアクセスドライバを使用し、データファイルがテキスト形式であることを確認してください。また、外部テーブルを作成してクエリを実行できるよう、SQL（Structured Query Language）の基礎知識も必要です。

Oracleデータベースで外部テーブルを作成する

Oracle Loaderを使用して外部テーブルを作成するには、以下の手順に従います。

ステップ1：ディレクトリオブジェクトを作成する

まず、ユーザーから提供されたCSVファイルやテキストファイルが格納されている場所を指すディレクトリオブジェクトを、次のコードで作成します。

create directory vk_dir as '/opt/oracle/app/admin/je1prd/mods/151214_CHG0004529';

ステップ2：外部テーブルを作成する

続いて、次のSQLを使用して外部テーブルを作成します。

create table kumarv5.VK_4529_ext
(
  IBMCU   NCHAR(12),
  IBLITM   NCHAR(25),
  IBANPL   NUMBER
)
organization external
(
   type oracle_loader
   default directory vk_dir
   access parameters
   (
     records delimited by newline
     logfile vk_dir:'VK_4529_ext.log'
     badfile vk_dir:'VK_4529_ext.bad'
     discardfile vk_dir:'VK_4529_ext.dcf'
     skip 1
     fields
     terminated by '~'
     missing field values are null
     reject rows with all null fields
     (
       IBMCU,
       IBLITM,
       IBANPL
     )
   )
   location
   (
     'data14.csv'
   )
)
reject limit unlimited;

ステップ3：ローカルテーブルを作成する

外部テーブルにはDMLによる更新が行えないため、次のコードを使用して、外部テーブルを基にしたローカルテーブルを作成します。

--create
create table kumarv5.VK_4529_int
(
  IBMCU   NCHAR(12),
  IBLITM   NCHAR(25),
  IBANPL   NUMBER
)
tablespace proddtat;

ステップ4：外部テーブルからローカルテーブルへデータをコピーする

次のコードを使用して、外部テーブルからローカルテーブルへデータをコピーします。

--insert
Insert into kumarv5.VK_4529_int   select * from kumarv5.VK_4529_ext;
commit;
select count(*) from kumarv5.VK_4529_int;

ステップ5：本番用メインテーブルを更新する

外部テーブルを基にローカルテーブルを作成したら、次のコードを使用して、ローカルテーブルの内容で本番用メインテーブルを更新できます。

update PRODDTA.F43090 a
    set a.PCMCU='         1AM'
    WHERE (a. IBMCU, a. IBLITM, a. IBANPL) in
       (select b. IBMCU, b. IBLITM, b. IBANPL from kumarv5.VK_4529_int b );

外部テーブルから通常テーブルへのデータロード

次の画像は、外部テーブルから通常テーブルへデータをロードする流れを示したものです。

画像出典: https://dba.fyicenter.com/faq/oracle/Load-Data-from-External-Tables.html

まとめ

外部テーブルを利用すれば、外部ソースにあるデータを、まるでデータベース内のテーブルであるかのように扱えます。ユーザーからスプレッドシートやCSV形式でデータが提供され、そのExcelシートの内容をもとにデータベースのテーブルを更新したい場合に、外部テーブルは非常に便利です。

ご意見やご質問がある場合は、フィードバックタブからお気軽にお寄せください。チャットからすぐに会話を始めることもできます。

データベースについてさらに詳しく学ぶ。