Oracleからデータを取り出してファイル形式で出力する方法。度々調べているのでまとめておく。

前提

検証に用いたバージョンは、Oracle 11g Release2。

Oracle 12以降でも動くとは思われるが、12c Release2以降はSET MARKUP CSV ONを使えばCSV出力ができるため、このページで紹介する方法を使う理由はない。

SELECTの実行結果をファイルに出力する

CSVで出力する

以下のように実行することで、SELECT文で実行した内容をresult.csvに出力することができる。

set heading off
set trimspool on
set feedback off
set linesize 10000
set pagesize 0
set colsep ','

SPOOL result.csv

SELECT * FROM table_name;

SPOOL OFF

ただしコンマで区切るだけであり、データ部がダブルクォートで括られるわけではない点に注意が必要。
データにコンマが含まれる場合は想定のCSVのフォーマットとならない。

TSV（Tab区切り）で出力する

CSVは一般的な形式だが、データにコンマやスペースが含まれる場合はTSVで出力したほうがよい。
また出力結果をシェルコマンドで扱うような場合も、Tab区切りのほうが親和性が高い。

以下のように、colsepの設定をTab文字に置換すればよい。

set heading off
set trimspool on
set feedback off
set linesize 10000
set pagesize 0
set colsep '	'

SPOOL result.tsv

SELECT * FROM table_name;

SPOOL OFF

コンマもTab文字もデータ部で使われている場合の回避策

コンマやスペース、Tab文字のいずれもデータ部に含まれている場合は、以下のような方法でできる…はず。

• ①全角コンマや全角スペースに置き換えて問題ない場合は、SQLで置換処理を行った上で出力する
  例：半角コンマや半角スペースを全角に置換してCSV出力する。
• ②全角コンマや全角スペースを半角に統一しても問題ない場合は、①の後にシェルコマンドで半角へ置換する
  例：半角コンマや半角スペースを全角に置換してCSV出力する。出力後は全角コンマと全角スペースを半角に戻す。
• ③SQLでTabをデータに含まれない文字列へ置換して出力し、その後シェルコマンド等で元の文字列へ戻す
  例：元々のデータには"@“文字が使われていないため、Tab文字を”@“に置換してTSV出力する。出力後は”@“をTab文字へ置換する。
• ④SQLで何らかの文字を一時的にエスケープし、出力した後にシェルコマンドで元の文字列へ戻す
  例：元々のデータ”!“を”!!"、"@“を”@@“にそれぞれ置換し、Tab文字を”!@“に置換してTSV出力する。出力後は”!@“をTab文字に置換した後、”!!“を”!“に、”@@“を”@“に置換する。

シェルからSQLファイルを指定したコマンドで実行する

併せて使うことが多いので、これもメモしておく。

シェルコマンドからSQLファイルを指定して実行したい場合は、以下のようにユーザ名やパスワード等の接続情報の他、SQLを記述したファイル名を@をつけて指定すればよい。

sqlplus user/password@xe @filename.sql

こうすることで、シェルスクリプトの中から一行でSQLを実行＋ファイルへ出力できるため、シェルスクリプトの一部として組み込みやすくなる。