複数のCSVファイルを1つの外部表で読み込む方法と注意点

テーブル

～ext_dirユーザー作成からSELECTまで完全解説～

Oracleの外部表（External Table）は、複数のCSVファイルをまとめて1つの表としてSQL参照できる便利な機能です。

本記事では、部署別に分かれた3つのCSVファイルを、1つの外部表に定義してSQLで一括参照する方法を紹介します。
加えて、使用するext_dirユーザーの作成・権限設定からファイルの配置・実行例まで、すべての手順を解説します。

🔸 構成イメージ（テキスト図）

+----------------------------+
|      Oracle Database       |
|                            |
|  USER: ext_dir             |
|  TABLE: emp_ext（外部表）   |
|       ↓                    |
|  DIRECTORY: ext_dir        |
|       ↓                    |
|  /u01/app/oracle/external  |
|  ├ sales_dept.csv          |
|  ├ dev_dept.csv            |
|  └ admin_dept.csv          |
+----------------------------+

🔸 ステップ①：ext_dirユーザーを作成（SYSで実行）

CREATE USER ext_dir IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO ext_dir;

✅ このユーザーで外部表を作成・参照します。

🔸 ステップ②：CSVファイルを用意してサーバーに配置

📁 /u01/app/oracle/external/ に以下のCSVファイルを保存します。内容は部署ごとに異なりますが、列構造（列数・順序）は同一です。

📄 sales_dept.csv

2001,佐藤一郎,営業
2002,高橋由美,営業

📄 dev_dept.csv

3001,山本健太,開発
3002,鈴木あかね,開発

📄 admin_dept.csv

4001,伊藤真理子,管理
4002,中村信一,管理

※ oracleユーザーで読み取り可能なようにパーミッションを設定（例：644）

🔸 ステップ③：DIRECTORYオブジェクトの作成と権限付与（SYSで実行）

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

GRANT READ, WRITE ON DIRECTORY ext_dir TO ext_dir;

✅ WRITE権限がないと外部表でエラー（ORA-29913）になります。
ログやエラーファイル（.log, .bad）出力に必要です。

🔸 ステップ④：外部表を作成（ext_dirユーザーで実行）

-- ext_dir で接続して実行
CREATE TABLE emp_ext (
  emp_id     NUMBER,
  emp_name   VARCHAR2(100),
  department 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 (
    'sales_dept.csv',
    'dev_dept.csv',
    'admin_dept.csv'
  )
)
REJECT LIMIT UNLIMITED;

✅ 複数のファイルは LOCATION にカンマ区切りで並べて指定します。

🔸 ステップ⑤：データ確認（SQL実行）

SELECT * FROM emp_ext;

🖥️ 結果例：

EMP_IDEMP_NAMEDEPARTMENT
2001佐藤一郎営業
2002高橋由美営業
3001山本健太開発
3002鈴木あかね開発
4001伊藤真理子管理
4002中村信一管理

🔸 ステップ⑥：検索・集計も可能

-- 管理部の人数をカウント
SELECT COUNT(*) FROM emp_ext WHERE department = '管理';
[oracle@v19single ~]$ vi /u01/app/oracle/external/sales_dept.csv
[oracle@v19single ~]$ cat /u01/app/oracle/external/sales_dept.csv
2001,佐藤一郎,営業
2002,高橋由美,営業
[oracle@v19single ~]$ vi /u01/app/oracle/external/dev_dept.csv
[oracle@v19single ~]$ cat /u01/app/oracle/external/dev_dept.csv
3001,山本健太,開発
3002,鈴木あかね,開発
[oracle@v19single ~]$ vi /u01/app/oracle/external/admin_dept.csv
[oracle@v19single ~]$ cat /u01/app/oracle/external/admin_dept.csv
4001,伊藤真理子,管理
4002,中村信一,管理
[oracle@v19single ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 金 7月 11 17:38:55 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_dir IDENTIFIED BY password
  2    DEFAULT TABLESPACE users
  3    TEMPORARY TABLESPACE temp
  4    QUOTA UNLIMITED ON users;

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

SQL> GRANT CONNECT, CREATE TABLE TO ext_dir;

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

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

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

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

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

SQL> CREATE TABLE emp_ext (
  2    emp_id     NUMBER,
  3    emp_name   VARCHAR2(100),
  4    department 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 (
 15      'sales_dept.csv',
 16      'dev_dept.csv',
 17      'admin_dept.csv'
 18    )
 19  )
 20  REJECT LIMIT UNLIMITED;

表が作成されました。

SQL> set lin 1000
SQL> col emp_name for a20
SQL> col department for a20
SQL> SELECT * FROM emp_ext;

    EMP_ID EMP_NAME             DEPARTMENT
---------- -------------------- --------------------
      2001 佐藤一郎             営業
      2002 高橋由美             営業
      3001 山本健太             開発
      3002 鈴木あかね           開発
      4001 伊藤真理子           管理
      4002 中村信一             管理

6行が選択されました。

SQL> SELECT COUNT(*) FROM emp_ext WHERE department = '管理';

  COUNT(*)
----------
         2

🔸 注意点とエラー対策

注意点解説
列構造の一致が必須各CSVファイルで列数・順序・区切り文字が一致していないと読み込み失敗
ファイルが存在しない場合はエラーORA-29913 / KUP-04040 が発生
ファイル追加時は再作成が必要ALTER TABLEでLOCATIONは変更できないため、DROP TABLE→再作成
WRITE権限の不足に注意data.logやdata.badなどの生成に必要

🔸 エラー例（ファイルが存在しない場合）

ORA-29913: ODCIEXTTABLEOPENコールアウトの実行中にエラーが発生しました。
KUP-04040: 外部テーブル emp_ext に指定されたファイル admin_dept.csv が存在しません。

✅ ファイルの存在チェックをOSやPL/SQLで事前に実施するのが推奨です。

🔸 まとめ

Oracleの外部表では、内容が異なる複数のCSVファイルを1つの表としてまとめて参照できます。
ext_dirユーザーでの設定から実行までを一貫して行うことで、日次・部門別ファイルを柔軟に扱えます。

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

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

コメント

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