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\]](https://docs.oracle.com/cd/E82638_01/sqpug/SET-system-variable-summary.html#GUID-0AA910C4-C22A-4A9E-BE13-AAA059CC7919)

> レポートを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オプション](https://docs.oracle.com/cd/E82638_01/sqpug/starting-SQL-Plus.html#GUID-B3B93959-2119-447D-A9D3-2349DDD70435)

> 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 の中身。

```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 の中身。

```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\)](https://docs.oracle.com/cd/E82638_01/sqpug/)
- [SQL\*Plusの起動 \- 3.5.1.6 MARKUPオプション](https://docs.oracle.com/cd/E82638_01/sqpug/starting-SQL-Plus.html#GUID-B3B93959-2119-447D-A9D3-2349DDD70435)
- [SQL\*Plusの起動 \- 3.5.1.10 SILENTオプション](https://docs.oracle.com/cd/E82638_01/sqpug/starting-SQL-Plus.html#GUID-D4929F5A-29F5-41FB-AF88-AC52ED7C75D9)
- [SETシステム変数の一覧 \- 12.41.23 SET FEED\[BACK\] \{6 \| n \| ON \| OFF \| ONLY\}](https://docs.oracle.com/cd/E82638_01/sqpug/SET-system-variable-summary.html#GUID-2C90B73B-A7E0-4357-9382-5EBAF53BF528)
- [SETシステム変数の一覧 \- 12.41.26 SET HEA\[DING\] \{ON \| OFF\}](https://docs.oracle.com/cd/E82638_01/sqpug/SET-system-variable-summary.html#GUID-3C66D0E2-9EA1-4478-BB64-6F55405BEEC7)
- [SETシステム変数の一覧 \- 12.41.36 SET MARK\[UP\]](https://docs.oracle.com/cd/E82638_01/sqpug/SET-system-variable-summary.html#GUID-0AA910C4-C22A-4A9E-BE13-AAA059CC7919)
- [Oracle DatabaseのテーブルをCSV出力する6つの方法 \- Keep It Simple, Stupid](http://yskwkzhr.blogspot.com/2017/12/6-ways-to-output-oracle-database-tables-in-csv-format.html)