2020-07-10追記: Oracleの話です。あと2010年に書いたものです。
- 力技そのいち
- SQL*Plusの列区切り文字を「,」にする。
- 利点は、なんといっても手軽さ。
- 欠点
- 可変長項目が固定長になってしまう。
- その為NULLとスペース埋めや、末尾スペースの存在等が判別不可。
- 複数テーブルを一気に取得できない。
- 力技そのに
- PL/SQL
- 利点
- 頑張れば出力項目を選別するとか拡張出来る。
- 複数テーブルを一気に取得できる。
- 欠点
- バージョン依存度が高い。(10gR2以降)
- その場でさらっと書いて実行するのは現実的でない。
- 利点
- SQLを作るSQLで作ったSQLを実行した結果をファイルに書き出す
- 今回書いている方法。
- 日本語で書くと長ったらしいが、実際の手間は殆ど無い。
- 単純なSQLなので、やり方さえ判っていればその場で書ける。
- おそらく一番現実的で汎用的。
以下のSQLを任意のファイル名(OutputCsv.sql等)で保存し、SQL*Plusで対象テーブルと出力ファイル名を指定して実行すると、対象テーブルのデータがCSV形式で出力されます。
SQLファイル内容
set feedback off set trimspool on set linesize 32767 set pagesize 0 set verify off ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; spool _OutputCsv.sql prompt spool &2 --列名表示 SELECT DECODE(COLUMN_ID, '1', 'SELECT ', ' || '','' || '), '''' || COLUMN_NAME || '''', DECODE(COLUMN_ID, MAX(COLUMN_ID) OVER(PARTITION BY TABLE_NAME), ' FROM DUAL;') FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&1') ORDER BY COLUMN_ID; --データ取得、値はダブルクォテーションで囲む SELECT DECODE(COLUMN_ID, '1', 'SELECT ', ' || '','' || '), '''"'' || ' || COLUMN_NAME || ' || ''"''', DECODE(COLUMN_ID, MAX(COLUMN_ID) OVER(PARTITION BY TABLE_NAME), ' FROM ' || TABLE_NAME || ';') FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&1') ORDER BY COLUMN_ID; prompt spool off spool off @_OutputCsv.sql exit
実行コマンド(例)
sqlplus USER/PASS @OutputCsv.sql TEST_TABLE output_TEST_TABLE.csv
複数テーブルを対象とする場合は、単純にテーブル数分コマンドを実行してください。出力ファイルは上書きされますので、複数テーブル対象時は特に、被らないように気をつけてください。
処理内容
出力ファイル内容(例)
COL1,COL2,COL3,COL4 "1","2010-01-08 22:51:30","","a1" "2","1900-01-01 00:00:00","A ","a123" "3","2010-01-08 23:55:41","ABCDEFG","a12 " "4","2010-01-09 00:46:26"," ",""
上記内容が出力されるテーブルの定義
SQL> desc TEST_TABLE 名前 NULL? 型 ------------------ -------- --------------------- COL1 NUMBER(5) COL2 DATE COL3 CHAR(7) COL4 VARCHAR2(20)
おまけ
全テーブルを出力するコマンドを作るSQL
SELECT 'sqlplus user/pass @OutputCsv.sql ' || TABLE_NAME || ' output_' || TABLE_NAME || '.csv' FROM USER_TABLES;