SQL*Plusの問い合わせ結果をファイルに出力する方法(Oracle)

SQL*Plusから問い合わせを行った結果をファイルに出力したい、なんてことは結構あると思います。

普段使わないと忘れがちなので、SQL*Plusの問い合わせ結果をファイルへ出力する方法をまとめておきます。

spoolコマンドによるファイル出力

SQL*Plusからファイルへ出力する方法はとても簡単で、spoolコマンドを利用します。

コマンド形式(細かなオプション等は省略)

spool ファイル名
select * from ~
spool off

具体的には例はこんな感じ。

SQL> spool C:\test.txt
SQL> select * from SPOOL_TEST;

  ID   NAME
−−−−− −−−−−−−−−−−−−−−−−−−−
  1     item1
  2     item2
  3     item3

SQL> spool off

出力ファイルの指定と書き込みの開始

SQL> spool C:\test.txt

で指定したファイルへの書き込みを開始します。

ただし、spolコマンドはselectの問い合わせ結果を書き込むのではなくSQL*Plusのコマンドライン上の出力を全て書き込むため注意が必要です。

書き込みの終了

spool off

で書き込みを終了します。

基本的にはこれで書き込みができます。

ファイル出力時の問題と解決策

次に応用。というよりもむしろこれらのオプションを覚えないと使える形で出力できない（笑）

改行されすぎ問題

例えば問い合わせ結果や項目名が長すぎると....

SQL> select * from spool_test2;

 ID   NAME
−−−−− −−−−−−−−−−−−−−−−−−−−
NOTES
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

UP_DATE
−−−−
  1    item1
This is Item No.1
10-12-05


SQL>

出力結果もこの通り改行されて、とても見づらい。
なぜこんなことになるのかというと、1行あたりの出力バイト数がSQL*Plusのデフォルトで小さな値になっているため。（その他コマンドライン等の出力バッファサイズも影響します）

1行あたりの出力桁数を変更するにはset linesizeコマンドを利用します。

set linesize 1行あたりの表示バイト数

デフォルトは80で，指定できるバイト数の上限はシステム(OS)依存のようです。

set linesizeを実行することで、指定したバイト数分を1行に出力できるようになります。

SQL> set linesize 1000;
SQL> select * from spool_test2;

  ID   NAME                 NOTES
−−−−− −−−−−−−−−−−−−−−−−−−−  −−−−−−−−−....
  1    item1                This is Item No.1 ....

SQL>

1行長すぎ問題

１行が無駄に長く折り返してしまう場合はcolumnコマンドを利用します。

column カラム名 FORMAT 'フォーマット文字列'

カラム名にはフォーマットを設定する列名、フォーマットには出力形式を指定します。

SQL> column id FORMAT '09';
SQL> column NAME FORMAT 'a10';
SQL> column NOTES FORMAT 'a30';
SQL> SELECT * FROM spool_test2;

 ID   NAME                  NOTES              UPDATE_AT
−−−−− −−−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−  −−−−−−−−−−−−
 1    item1                 This is Item No.1  10-12-05

SQL>

「09」は数値型に対する書式で、2桁の値にすることを意味しています。
「a10」は文字型に対する書式で、この場合は10バイトの文字列を意味しています。

これで1行の表示桁数をコントロールできます。

見出しが何度も出る問題

出力するデータ件数が多い場合に、見出しが何度も出てきます。

これは1ページ当たりの出力行数を変更することで対処可能です。set pagesizeコマンドを使います。

set pagesize 1ページ当たりの出力行数

1ページあたりの出力行数を0にすると，列見出しが無くなります。

以上、SQL*Plusの出力について諸々をまとめてみました！

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

SQL ゼロからはじめるデータベース操作