外部表（External Table）の完全ガイド ～CSVファイルをSQLで直接読む！～

テーブル

Oracle Databaseでは、CSVファイルなどの外部ファイルをSQLで参照できる「外部表（External Table）」という強力な機能が用意されています。

この記事では、外部表の仕組み、作成・参照手順、正しいユーザー権限の設定方法まで、実務でそのまま使える形で解説します。

🔸 外部表とは？

外部表（External Table）は、OracleがOS上のファイル（CSVなど）をSQLで参照可能にする仮想的な表です。
通常の表と異なり、データベース内にデータは存在せず、あくまでファイルを直接読むことが特徴です。

特徴内容
データソースOracleサーバー上のCSV/TSV/固定長ファイル
読み取り専用SELECTのみ可能。INSERT, UPDATE不可
SQLでの利用条件検索・集計・ビュー連携などが可能
ログ・エラー出力*.logや*.badファイルが生成される場合あり

🔸 実行環境構成（テキスト図）

+----------------------------+
|     Oracle Database        |
|                            |
|  USER: ext_user            |
|  TABLE: ext_emp (外部表)    |
|   ↓ DIRECTORY ext_dir      |
|  /u01/app/oracle/external  |
|        └ data.csv          |
+----------------------------+

🔸 ステップバイステップ：外部表の作成と参照

① CSVファイルを準備

Oracleサーバー上に、以下のCSVファイルを作成します。

📄 /u01/app/oracle/external/data.csv:

1001,田中太郎,東京
1002,山田花子,大阪
1003,佐藤次郎,名古屋
1004,伊藤真一,札幌
1005,中村遥,福岡

※ 所有者をoracleユーザー、パーミッションを644に設定しておきましょう。

② 外部表を使うユーザーの作成（SYSユーザーで実行）

-- 外部表専用ユーザーを作成
CREATE USER ext_user IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- 必要な権限を付与
GRANT CONNECT, CREATE TABLE TO ext_user;

③ DIRECTORYオブジェクトの作成と必要な権限の付与

-- ディレクトリ定義（ファイル配置先と一致させる）
CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/app/oracle/external';

-- ユーザーにREAD + WRITE権限を付与（←重要）
GRANT READ, WRITE ON DIRECTORY ext_dir TO ext_user;

✅ WRITE 権限がないと以下のようなエラーになります：

ORA-29913: ODCIEXTTABLEOPENコールアウトの実行中にエラーが発生しました。
KUP-04074: ディレクトリ・オブジェクトEXT_DIRへの書込みアクセスがありません

🔧 対策：必ず WRITE 権限を付与しましょう。

④ 外部表の作成（ext_userで実行）

-- ext_user に接続して実行
CREATE TABLE ext_emp (
  emp_id   NUMBER,
  emp_name VARCHAR2(100),
  location VARCHAR2(100)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('data.csv')
)
REJECT LIMIT UNLIMITED;

⑤ データ参照の実行

SELECT * FROM ext_emp;

🖥️ 結果：

EMP_IDEMP_NAMELOCATION
1001田中太郎東京
1002山田花子大阪
1003佐藤次郎名古屋
1004伊藤真一札幌
1005中村遥福岡

🔸 外部表で生成される補助ファイル

SELECT実行時に以下のようなファイルがext_dir配下に作成されます：

ファイル名内容
data.logログファイル（成功・失敗の記録）
data.bad読み込みエラーとなったレコード
data.dsc捨てられた（不要と判断された）レコード

✅ これらのファイルを生成・書込みするために WRITE 権限が必要です。

🔸 外部表の利用例

-- 条件付き抽出
SELECT emp_name FROM ext_emp WHERE location = '東京';

-- 件数カウント
SELECT COUNT(*) FROM ext_emp;
[oracle@v19single ~]$ vi /u01/app/oracle/external/data.csv
[oracle@v19single ~]$ cat /u01/app/oracle/external/data.csv
1001,田中太郎,東京
1002,山田花子,大阪
1003,佐藤次郎,名古屋
1004,伊藤真一,札幌
1005,中村遥,福岡
[oracle@v19single ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 金 7月 11 17:00:56 2025
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.



Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
に接続されました。
SQL> CREATE USER ext_user IDENTIFIED BY password
  2    DEFAULT TABLESPACE users
  3    TEMPORARY TABLESPACE temp
  4    QUOTA UNLIMITED ON users;

ユーザーが作成されました。

SQL> GRANT CONNECT, CREATE TABLE TO ext_user;

権限付与が成功しました。

SQL> CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/app/oracle/external';

ディレクトリが作成されました。

SQL> GRANT READ, WRITE ON DIRECTORY ext_dir TO ext_user;

権限付与が成功しました。

SQL> CONNECT ext_user/password
接続されました。
SQL> CREATE TABLE ext_emp (
  2    emp_id   NUMBER,
  3    emp_name VARCHAR2(100),
  4    location VARCHAR2(100)
  5  )
  6  ORGANIZATION EXTERNAL (
  7    TYPE ORACLE_LOADER
  8    DEFAULT DIRECTORY ext_dir
  9    ACCESS PARAMETERS (
 10      RECORDS DELIMITED BY NEWLINE
 11      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 12      MISSING FIELD VALUES ARE NULL
 13    )
 14    LOCATION ('data.csv')
 15  )
 16  REJECT LIMIT UNLIMITED;

表が作成されました。

SQL> set lin 1000
SQL> col emp_name for a20
SQL> col location for a20
SQL> SELECT * FROM ext_emp;

    EMP_ID EMP_NAME             LOCATION
---------- -------------------- --------------------
      1001 田中太郎             東京
      1002 山田花子             大阪
      1003 佐藤次郎             名古屋
      1004 伊藤真一             札幌
      1005 中村遥               福岡

SQL> SELECT emp_name FROM ext_emp WHERE location = '東京';

EMP_NAME
--------------------
田中太郎

SQL> SELECT COUNT(*) FROM ext_emp;

  COUNT(*)
----------
         5

🔸 外部表の注意点

注意点内容
書き込み不可外部表は読み取り専用です（INSERT不可）
データは外部Oracleはファイルを参照するだけ（内部には格納されない）
サーバーローカル対象ファイルはOracleサーバー内のファイルシステムにある必要がある
WRITE権限必要ログ・エラーファイル生成のためにWRITE権限が必要

🔸 まとめ

Oracleの外部表は、CSVなどのファイルをSQLで直接読み取り、集計・分析ができる非常に便利な仕組みです。

今回紹介したように、READとWRITEの両方のディレクトリ権限が必要です。
これを誤るとORA-29913エラーが発生するため、注意しましょう。

[参考]
Oracle Database データベース概要 19c

Oracleテーブル作成とデータ型をマスターする
Oracleデータベースにおけるテーブル作成は、効率的なデータ管理の基本です。本記事では、初心者でも理解しやすいように、テーブル作成の構文や主要なデータ型を具体例とともに詳しく解説します。また、現場で役立つヒントや注意点も盛り込みました。 ...
oracle-master00000.com
2024.12.17

コメント

タイトルとURLをコピーしました