Oracle Database のデータを SQL*Plus で CSV 出力する

概要

• Oracle Database のテーブルから SELECT 文で取得したデータを CSV 出力する
• 検証環境: Oracle Database 12c Release 2 (12.2.0.1.0) Enterprise Edition (on Docker) + SQL*Plus: Release 12.2.0.1.0 Production + macOS Catalina

CSV 出力する方法

SET MARKUP CSV ON

SQL> SET MARK[UP] CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

SETシステム変数の一覧 - 12.41.36 SET MARK[UP]

レポートをCSV形式で出力します。

有効にするには、動的レポート出力の値を変更するSET MARKUPコマンドを、問合せ出力を生成する文の前に発行する必要があります。問合せ出力を生成する最初の文によって、DELIMITERおよびQUOTEの設定を反映したCSVデータの出力がトリガーされます。

splplus -M CSV ON

$ splplus -M[ARKUP] CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

• SQL*Plusの起動 - 3.5.1.6 MARKUPオプション

CSV出力の場合、SQLPLUS -MARKUP CSV ONを使用して、出力をCSV形式で生成します。DELIMITERオプションを使用して、デリミタ文字を指定できます。QUOTE OFFを使用して、引用符なしでテキストを出力することもできます。

CSV 出力例

サンプルデータの準備

数値型、文字列型、日時データ型のカラムを持つテーブルを作成する。

SQL> CREATE TABLE my_table (
  2    id NUMBER(4) GENERATED ALWAYS AS IDENTITY,
  3    message VARCHAR2(16 CHAR),
  4    updated_at TIMESTAMP(6)
  5  );

レコードを追加する。

SQL> INSERT INTO my_table (message, updated_at) VALUES ('Hello World', LOCALTIMESTAMP);

SQL> INSERT INTO my_table (message, updated_at) VALUES ('こんにちは世界', LOCALTIMESTAMP);

CSV の天敵、カンマとダブルクォートも入れておく。

SQL> INSERT INTO my_table (message, updated_at) VALUES ('🍣ダブルクォート"カンマ,🍣', LOCALTIMESTAMP);

SQL> SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;

        ID MESSAGE                     TO_CHAR(UPDATED_AT,'YYYY-M
---------- --------------------------- --------------------------
         1 Hello World                 2020-09-11 12:49:29.387803
         2 こんにちは世界                 2020-09-11 12:49:34.014894
         3 🍣ダブルクォート"カンマ,🍣        2020-09-11 12:49:45.946029

SET MARKUP CSV ON

SET MARKUP CSV ON を入力。

SQL> SET MARKUP CSV ON

SELECT 文で CSV を出力できる。
1行目に空行、2行目に列ヘッダー、3行目からレコードの内容が出力される。
テキストデータには両端にダブルクォートが付加される。
テキストデータ内のダブルクォートはエスケープされる。

SQL> SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;

"ID","MESSAGE","TO_CHAR(UPDATED_AT,'YYYY-MM-DDHH24:MI:SS.FF6')"
1,"Hello World","2020-09-11 12:49:29.387803"
2,"こんにちは世界","2020-09-11 12:49:34.014894"
3,"🍣ダブルクォート""カンマ,🍣","2020-09-11 12:49:45.946029"

QUOTE で囲み文字を制御

QUOTE OFF オプションで囲み文字を出力しないように設定できる。
ただし、データにカンマが含まれていてもエスケープされたりはしないので注意。

SQL> SET MARKUP CSV ON QUOTE OFF

SQL> SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;

ID,MESSAGE,TO_CHAR(UPDATED_AT,'YYYY-MM-DDHH24:MI:SS.FF6')
1,Hello World,2020-09-11 12:49:29.387803
2,こんにちは世界,2020-09-11 12:49:34.014894
3,🍣ダブルクォート"カンマ,🍣,2020-09-11 12:49:45.946029

DELIMITER で区切り文字を指定

DELIMITER オプションで区切り文字を指定できる。

SQL> SET MARKUP CSV ON DELIMITER '|' QUOTE OFF

SQL> SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;

ID|MESSAGE|TO_CHAR(UPDATED_AT,'YYYY-MM-DDHH24:MI:SS.FF6')
1|Hello World|2020-09-11 12:49:29.387803
2|こんにちは世界|2020-09-11 12:49:34.014894
3|🍣ダブルクォート"カンマ,🍣|2020-09-11 12:49:45.946029

区切り文字には1文字しか指定できない。2文字以上を指定するとエラーになる。

SQL> SET MARKUP CSV ON DELIMITER '||'          
SP2-1660: オプションが無効です。単一文字のみを指定します。

SET HEADING で列ヘッダーを制御

SET HEADING OFF で列ヘッダーを出力しないように設定できる。

SQL> SET MARKUP CSV ON
SQL> SET HEADING OFF

SQL> SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;
1,"Hello World","2020-09-11 12:49:29.387803"
2,"こんにちは世界","2020-09-11 12:49:34.014894"
3,"🍣ダブルクォート""カンマ,🍣","2020-09-11 12:49:45.946029"

sqlplus -M 'CSV ON' で CSV ファイルを出力

splplus コマンドに -M 'CSV ON' オプションで CSV (Comma-separated values) 出力を指定することができる。
これを利用してファイルに出力できる。

$ cat <<__EOF__ | sqlplus -S -M 'CSV ON' your_account/your_password@localhost:1521/your_db > output.csv
  SET FEEDBACK OFF
  SET HEADING OFF
  SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;
__EOF__

出力された output.csv の中身。

1,"Hello World","2020-09-11 12:49:29.387803"
2,"こんにちは世界","2020-09-11 12:49:34.014894"
3,"🍣ダブルクォート""カンマ,🍣","2020-09-11 12:49:45.946029"

DELIMITER にタブ文字を指定して TSV ファイルを出力

DELIMITER にタブ文字を指定して TSV (Tab-separated values) ファイルを出力できる。

$ cat <<__EOF__ | sqlplus -S -M 'CSV ON DELIMITER "	" QUOTE OFF' your_account/your_password@localhost:1521/your_db > output.tsv
  SET FEEDBACK OFF
  SET HEADING OFF
  SELECT id, message, to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM my_table;
__EOF__

出力された output.tsv の中身。

1	Hello World	2020-09-11 12:49:29.387803
2	こんにちは世界	2020-09-11 12:49:34.014894
3	🍣ダブルクォート"カンマ,🍣	2020-09-11 12:49:45.946029

参考資料

• SQL*Plusユーザーズ・ガイドおよびリファレンス, リリース2 (12.2)
• SQL*Plusの起動 - 3.5.1.6 MARKUPオプション
• SQL*Plusの起動 - 3.5.1.10 SILENTオプション
• SETシステム変数の一覧 - 12.41.23 SET FEED[BACK] {6 | n | ON | OFF | ONLY}
• SETシステム変数の一覧 - 12.41.26 SET HEA[DING] {ON | OFF}
• SETシステム変数の一覧 - 12.41.36 SET MARK[UP]
• Oracle DatabaseのテーブルをCSV出力する6つの方法 - Keep It Simple, Stupid