OracleDBの情報は若干レガシー扱いになっていますが、最新版もリリースされていれば、現役で動き続けているデータベースです。
Oracleからデータを出力させる方法は色々ありますが、一番簡単な方法がsqlplusを使用する方法です。
新たに別言語でプログラムを用意する必要がありませんし、バッチファイルとの相性もいいのもポイントです。
sqlplusからデータをCSV出力するのに必要な情報をまとめましたので、解説します。
今回はOracle 12.1の環境で確認しています。
12.2から違うコマンドでできるようです。
都度追記していたメモがいい感じに熟成したので、参考にしてみてください
索引元
[https://docs.oracle.com/cd/E82638_01/sqpug/index.html]{.underline}
最近のOracleのドキュメントはだいぶ読みやすくなりました。日本語もおおくなったし
SQLPLUSの起動
SQLPLUS <ユーザ名>/<パスワード>@<サーバ名>/サービス名 @スクリプトファイル名
これから解説するスクリプトをファイル名し、バッチファイルなどで実行します。
開発中はバッチファイルの最後にPAUSEを入れてあげるとコンソールが終了せずに、結果を見れます。
サービス名がなくポート指定の場合は省略できます。
サンプル
例)export.sql set echo off set linesize 1000 set pagesize 10000 set trimspool on set feedback off set heading on set underline off set colsep ',' spool c:tempexport.csv select * from sample; spool off
例)run.bat SQLPLUS <ユーザ名>/<パスワード>@<サーバ名>/サービス名 @export.sql
CSV出力される仕組み
set オプション
spool ファイル名 出力文字←テキスト出力されます spool off
開かれたファイルに対してspool offされるまでの間にコンソールに出力された文が出力されます。
おおよその場合は、selectの結果を出力する場合はspool内でselectを指定してあげると出力されます。
print で指定の文字列を出力もできます。
そしてspool前に記述しているsetオプションで様々な指定をします
set オプション
| オプション名 | 内容 |
|---|---|
| set echo off | コンソールに入力したコードを非表示にします |
| set feedback off | SELECT後の ～行が選択されましたを非表示にします |
| set termout off | SELECT結果の行を表示しない。 ただし行は出力されます。 |
| trimspool on | SELECTすると右に大量のスペースが発生します。右端のスペースを削除します。 |
| colsep ',' | 列と列の間の区切り文字を指定します。 例は「,」カンマを指定しています。 |
| set linesize n | 1行の最大文字数を指定します。 999など多めに指定してあげます |
| set pagesize n | 出力行数を指定します。指定行数を超えると区切行として空行が挿入されます。 999など多めに指定してあげます。 0を指定すると区切行が挿入されませんが、列名も同時に出力されません。 列名が必要で0指定する場合は別途列名をprintさせます |
| set heading on off | selectの列名の表示のオン・オフ |
| set underline on off | selectの列名と行の間に表示される区切り行「-----------」の表示のオン・オフ |
SELECTにバインド変数を使用する場合
VARIABLE <変数名> <型> EXECUTE :<変数名> := 値
例) VARIABLE code varchar2(10) EXECUTE :code := 'ABC123' VARIABLE codeNum number EXECUTE :codeNum := '123456'
バッチ実行時に引数を指定
spool開始前に指定します。
VARIABLE code varchar2(10) EXECUTE :code := '&1' VARIABLE codeNum number EXECUTE :codeNum := &2
SQLPLUS <ユーザ名>/<パスワード>@<サーバ名>/サービス名 @スクリプトファイル名 "ABC123" "123456"
引数は可変長にはできず、引数の順番に&1、&2と指定します。
出力した列の長さが可変長にできない
SELECTした列が固定長になってしまうので、列の中に大量のスペースが発生してしまいます。
それの回避策として|| ',' || で列を結合して、1つの列として出力させる方法があります。
普通にSELECTを使うならselect * from やselect col,col2 from 等したいですが、残念ながらできません。
select col1 || ',' || col2 from sample;
列名が出力できない
上記のSELECTにすると列が表示できなくなります。
そこで列専用のSELECTやprintを実行して列を出力させます。
CSVに列情報が不要の場合はこの処理は省けます。
例1)DAULを使う
select '列名1' as col , '列名2' as col2 from dual
例2)
print '列名1,列名2'
DUALはダミーテーブルと呼ばれ、今回のような変則的なテーブル定義の際に使われます。
SQLを別ファイルにする
副問合せをする場合等SQLが長くなるとSQLの保守性が下がってしまいます。
SQLをスクリプトファイルと分離させて全体の保守性を高めます
方法としてsqlを書いたテキストファイルを作成し、@SQLファイル名として記述するとSELECTできます。
例)
spool sample.csv @SQLファイル名.sql spool off
