【Oracle版】A5M2cmdで複数テーブルを順番にSELECTして「テーブル名.csv」で出力するバッチ

A5:SQL Mk-2のコマンドライン版 A5M2cmd.exe を使うと、Oracleの複数テーブルを順番にSELECTし、

TABLE_A.csv
TABLE_B.csv
TABLE_C.csv

のようにテーブル単位でCSV出力できます。

A5M2cmdには、SQLファイルへテーブル名を動的パラメータとして渡す専用機能がないため、今回は、

テーブル一覧
↓
SQLテンプレートの{TABLE}を置換
↓
一時SQLを生成
↓
A5M2cmdで実行
↓
テーブル名.csv

という方法を使います。A5M2cmdのSQLファイルへ動的パラメータを渡す専用オプションはないと開発者から案内されています。

ファイル構成

例として、次の構成にします。

C:\A5Export\
├─ tables.txt
├─ select_template.sql
├─ export_oracle_tables.bat
└─ CSV\

1. テーブル一覧を作る

tables.txt に対象テーブルを1行ずつ記述します。

EMPLOYEE
DEPARTMENT
CUSTOMER
ORDERS

別スキーマを参照する場合は、

MASTER.CUSTOMER
SALES.ORDERS
LOG.ACCESS_LOG

のようにスキーマ名付きでも指定できます。

2. SQLテンプレートを作る

select_template.sql を作成します。

全件出力なら、

SELECT *
FROM {TABLE};

例えば共通の更新日時列を使って絞り込むなら、

SELECT *
FROM {TABLE}
WHERE UPDATE_DATE >= SYSDATE - 7;

とできます。

{TABLE} の部分をバッチ側で実際のテーブル名に置き換えます。

3. Oracle版バッチを作る

export_oracle_tables.bat の例です。

@echo off
setlocal EnableDelayedExpansion

rem ==========================================
rem 基本設定
rem ==========================================

set "A5CMD=C:\A5M2cmd\A5M2cmd.exe"

set "BASE=C:\A5Export"
set "TABLELIST=%BASE%\tables.txt"
set "TEMPLATE=%BASE%\select_template.sql"
set "WORKSQL=%BASE%\work.sql"
set "OUTDIR=%BASE%\CSV"

rem ==========================================
rem Oracle接続設定
rem ==========================================

set "DBUSER=USER01"
set "DBPASS=PASSWORD"
set "DBHOST=oracle.example.local"
set "DBPORT=1521"
set "SERVICE=ORCLPDB1"
set "SCHEMA=USER01"

set "CONNECT=__ConnectionType=Internal;ProviderName=Oracle;Direct=True;ServerName=%DBHOST%;Port=%DBPORT%;Database=SN=%SERVICE%;UserName=%DBUSER%;Password=%DBPASS%;UseUnicode=True;InitialSchemaName=%SCHEMA%"

rem ==========================================
rem 出力先作成
rem ==========================================

if not exist "%OUTDIR%" mkdir "%OUTDIR%"

rem ==========================================
rem テーブルごとに処理
rem ==========================================

for /f "usebackq delims=" %%T in ("%TABLELIST%") do (

    echo ==========================================
    echo Export : %%T
    echo ==========================================

    powershell -NoProfile -Command ^
      "(Get-Content -Raw '%TEMPLATE%').Replace('{TABLE}','%%T') | Set-Content -Encoding UTF8 '%WORKSQL%'"

    "%A5CMD%" ^
      "/Connect=%CONNECT%" ^
      /RunSQL ^
      /Encoding=UTF-8 ^
      "/FileName=%WORKSQL%" ^
      "/FilePattern=%OUTDIR%\%%T.csv"

    if errorlevel 1 (
        echo [ERROR] %%T
    ) else (
        echo [OK] %%T
    )
)

del "%WORKSQL%" 2>nul

echo.
echo Export completed.
endlocal

Oracle直接接続では、

ProviderName=Oracle
Direct=True
ServerName=...
Port=1521
Database=SN=サービス名

という指定ができます。サービス名接続の場合、Database=SN=xxxx とする方法がA5:SQL Mk-2開発者から案内されています。

4. 実行結果

tables.txt が、

EMPLOYEE
DEPARTMENT
CUSTOMER

なら、

C:\A5Export\CSV\
├─ EMPLOYEE.csv
├─ DEPARTMENT.csv
└─ CUSTOMER.csv

が作成されます。

内部では順番に、

SELECT *
FROM EMPLOYEE;

SELECT *
FROM DEPARTMENT;

SELECT *
FROM CUSTOMER;

が生成・実行されます。

CSV出力先について

A5M2cmdの /RunSQL では、/FilePattern にパスを含めることでCSV出力先とファイル名を指定できます。

今回なら、

"/FilePattern=%OUTDIR%\%%T.csv"

としているため、

テーブル名.csv

になります。

Oracleのサービス名とSIDに注意

Oracleでは、

SID
サービス名

を混同すると、

ORA-12505

などの接続エラーになることがあります。

サービス名接続なら、

Database=SN=ORCLPDB1

のようにします。

特にPDBへ接続する場合は、サービス名接続になるケースが多いため注意してください。

OCI接続を使う場合

Oracle Clientがインストールされていて、TNS接続を使う場合は直接接続とは設定が異なります。

例えば概念的には、

ProviderName=Oracle
Direct=False
TNSNAME=接続文字列

という形式になります。A5:SQL Mk-2はOracleへOCI接続と直接接続の両方に対応しています。

Oracle Clientをインストールせずに使いたい場合は、

Direct=True

の直接接続が分かりやすいでしょう。

スキーマ名付きテーブルを使う場合

Oracleでは、

SCOTT.EMP
HR.EMPLOYEES

のようにスキーマ名を付けることがあります。

tables.txt を、

HR.EMPLOYEES
HR.DEPARTMENTS
SALES.ORDERS

としても、そのままSQLへ展開できます。

ただし、その場合CSV名も、

HR.EMPLOYEES.csv
HR.DEPARTMENTS.csv

になります。

スキーマ名をCSV名から除きたい場合は、ファイル名用の変数を別に作ります。

スキーマ名をCSVから除くバッチ

例えば、

HR.EMPLOYEES

を、

EMPLOYEES.csv

として出力したい場合です。

ループ部分を次のように変更します。

for /f "usebackq delims=" %%T in ("%TABLELIST%") do (

    set "FULLTABLE=%%T"

    for %%A in ("%%T") do set "CSVNAME=%%~nxA"

    for /f "tokens=2 delims=." %%A in ("%%T") do set "CSVNAME=%%A"

    echo Export : %%T

    powershell -NoProfile -Command ^
      "(Get-Content -Raw '%TEMPLATE%').Replace('{TABLE}','%%T') | Set-Content -Encoding UTF8 '%WORKSQL%'"

    "%A5CMD%" ^
      "/Connect=%CONNECT%" ^
      /RunSQL ^
      /Encoding=UTF-8 ^
      "/FileName=%WORKSQL%" ^
      "/FilePattern=%OUTDIR%\!CSVNAME!.csv"
)

ただし、スキーマ名がない行も混在する場合は追加の判定が必要です。

運用を簡単にするなら、

tables.txt

にはテーブル名だけを記載し、

InitialSchemaName

で使用スキーマを固定する方法もあります。

Oracle特有の注意点：大文字小文字

Oracleでは通常、

EMPLOYEE

と、

employee

は引用符なしなら同じ識別子として扱われます。

ただし、

CREATE TABLE "Employee" ...

のようにダブルクォート付きで作成されたオブジェクトは、大文字小文字を区別します。

その場合は、

"Employee"

のように記述する必要があります。

一般的なOracle運用であれば、

EMPLOYEE
DEPARTMENT

のような大文字のオブジェクト名にしておくと扱いやすいです。

Oracleの日付条件例

例えば直近7日分だけ出力したいなら、

SELECT *
FROM {TABLE}
WHERE UPDATE_DATE >= SYSDATE - 7;

当日分なら、

SELECT *
FROM {TABLE}
WHERE UPDATE_DATE >= TRUNC(SYSDATE);

前日分なら、

SELECT *
FROM {TABLE}
WHERE UPDATE_DATE >= TRUNC(SYSDATE) - 1
  AND UPDATE_DATE <  TRUNC(SYSDATE);

のようにできます。

CSVをA5:SQL Mk-2へ再インポートできる？

このバッチで出力するのは通常のCSVなので、A5:SQL Mk-2のCSVインポート用途に利用できます。

例えば、

EMPLOYEE.csv

を別テーブルへインポートする、といった使い方が可能です。

ただしCSVに保存されるのは、

SELECT結果

だけです。

そのため、

主キー
インデックス
外部キー
シーケンス
トリガー
テーブル定義

などは保存されません。

Oracleの完全バックアップ用途なら、Data Pumpの、

expdp
impdp

などを利用する方が適しています。

A5M2cmdによるCSV出力は、

データ確認
データ退避
データ移行
Excel等での確認

に向いています。

パスワードをバッチに書く場合は注意

サンプルでは、

set "DBPASS=PASSWORD"

としていますが、本番運用ではバッチファイルにDBパスワードが平文で残ります。

そのため、

バッチファイルのNTFSアクセス権を制限する
専用実行ユーザーだけ読み取り可能にする
共有フォルダへ置かない

などの対策をおすすめします。

A5:SQL Mk-2ではGUIからDB接続情報をエクスポートして接続文字列を作る方法も案内されています。ただしエクスポート時にはパスワードが除外される点に注意が必要です。

まとめ

OracleでA5M2cmdを使って複数テーブルをCSV出力する場合は、

tables.txt
↓
テーブル名取得
↓
{TABLE}を置換
↓
一時SQL生成
↓
Oracleへ接続
↓
A5M2cmd実行
↓
テーブル名.csv

という構成にすると分かりやすくなります。

Oracle直接接続のポイントは、

ProviderName=Oracle
Direct=True
ServerName=ホスト名
Port=1521
Database=SN=サービス名

です。

SQLテンプレートを使うため、

SELECT *
FROM {TABLE};

だけでなく、

SELECT *
FROM {TABLE}
WHERE UPDATE_DATE >= SYSDATE - 7;

など、同じ条件を複数テーブルへ一括適用できます。

複数テーブルの定期CSV出力や、データ退避・調査用データの収集を自動化したい場合に便利な方法です。