【Oracle】外部表（External Table）の使い方完全ガイド｜CSVファイルをSQLで直接読み込む方法・ORACLE_LOADER・エラー処理

-- DBA権限でディレクトリオブジェクトを作成する
CREATE OR REPLACE DIRECTORY ext_data_dir
    AS '/data/external_files';
-- Windows の場合: AS 'C:\data\external_files'

-- 外部表を使うユーザーに読み取り権限を付与する
GRANT READ ON DIRECTORY ext_data_dir TO hr;

-- 書き込み権限（LOGファイル・BADファイルの出力先に必要）
GRANT WRITE ON DIRECTORY ext_data_dir TO hr;

-- ディレクトリ一覧を確認する
SELECT directory_name, directory_path
FROM all_directories
ORDER BY directory_name;

CREATE TABLE emp_csv_ext (
    emp_id      NUMBER,
    name        VARCHAR2(100),
    department  VARCHAR2(50),
    salary      NUMBER,
    hire_date   DATE
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir         -- ディレクトリオブジェクト名
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE       -- 行区切り
        CHARACTERSET UTF8                  -- 文字コード
        SKIP 1                             -- ヘッダー行をスキップ
        FIELDS TERMINATED BY ','           -- カンマ区切り
        OPTIONALLY ENCLOSED BY '"'         -- ダブルクォート囲み（任意）
        MISSING FIELD VALUES ARE NULL      -- 不足フィールドはNULL
        (
            emp_id,
            name,
            department,
            salary,
            hire_date DATE 'YYYY-MM-DD'    -- 日付フォーマット指定
        )
    )
    LOCATION ('employees.csv')             -- ファイル名
)
REJECT LIMIT UNLIMITED;                    -- エラー行の許容数（UNLIMITEDで全件許容）

-- 通常のテーブルと同じように SELECT できる
SELECT emp_id, name, department, salary
FROM emp_csv_ext
WHERE department = '開発部'
ORDER BY salary DESC;

-- 集計もJOINもそのまま使える
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_salary
FROM emp_csv_ext
GROUP BY department
ORDER BY avg_salary DESC;

CREATE TABLE log_tsv_ext (
    log_date    DATE,
    log_level   VARCHAR2(10),
    message     VARCHAR2(500)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        CHARACTERSET UTF8
        FIELDS TERMINATED BY '\t'         -- タブ区切り
        MISSING FIELD VALUES ARE NULL
        (
            log_date DATE 'YYYY-MM-DD HH24:MI:SS',
            log_level,
            message
        )
    )
    LOCATION ('app.log')
)
REJECT LIMIT UNLIMITED;

CREATE TABLE emp_csv_ext (
    emp_id      NUMBER,
    name        VARCHAR2(100),
    salary      NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        BADFILE ext_data_dir:'emp_bad.log'     -- エラー行の出力先
        LOGFILE ext_data_dir:'emp_load.log'    -- 処理ログの出力先
        SKIP 1
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (emp_id, name, salary)
    )
    LOCATION ('employees.csv')
)
REJECT LIMIT 10;   -- エラー行が10件を超えたらSELECT全体がエラーになる

-- 書き出し用の外部表を作成する
CREATE TABLE emp_dump_ext
    ORGANIZATION EXTERNAL (
        TYPE ORACLE_DATAPUMP
        DEFAULT DIRECTORY ext_data_dir
        LOCATION ('emp_dump.dmp')
    )
AS SELECT employee_id, name, salary FROM employees;
-- CREATE TABLE AS SELECT で外部表を作成すると、
-- SELECT結果がData Pump形式のファイルに書き出される

-- 読み込み用の外部表を作成する（別の環境で使う場合）
CREATE TABLE emp_load_ext (
    employee_id NUMBER,
    name        VARCHAR2(100),
    salary      NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY ext_data_dir
    LOCATION ('emp_dump.dmp')
);

-- 通常テーブルと同様にSELECTできる
SELECT * FROM emp_load_ext WHERE salary > 400000;

-- 本テーブルが既に存在する場合
INSERT /*+ APPEND PARALLEL(e, 4) */ INTO employees e
SELECT emp_id, name, department, salary, hire_date
FROM emp_csv_ext;
COMMIT;

-- 本テーブルを新規作成して一括取り込み（CTAS）
CREATE TABLE employees_new AS
SELECT * FROM emp_csv_ext;

CREATE TABLE sales_csv_ext (
    sale_date   DATE,
    product     VARCHAR2(100),
    amount      NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        CHARACTERSET UTF8
        SKIP 1
        FIELDS TERMINATED BY ','
        (sale_date DATE 'YYYY-MM-DD', product, amount)
    )
    LOCATION ('sales_2025_01.csv', 'sales_2025_02.csv', 'sales_2025_03.csv')
)
REJECT LIMIT UNLIMITED;
-- 3ファイルが結合されて SELECT * FROM sales_csv_ext で全件取得できる

-- 外部表にデフォルト並列度を設定する
ALTER TABLE emp_csv_ext PARALLEL 4;

-- または SELECT 時にヒントで指定する
SELECT /*+ PARALLEL(e, 4) */ emp_id, name, salary
FROM emp_csv_ext e
WHERE salary > 300000;