テーブルのデータをCSV出力する(PL/SQLでやってみる)
これは試行錯誤の途中経過です。まとめ→http://d.hatena.ne.jp/irof/20100108/p2
-
-
- -
-
「余計なスペースが入って欲しくない場合」かつ「対象テーブルまたは列が沢山ある場合」で、力技なんてやってられない場合のやり方。
この場合は、諦めて出力するツールを使いたい所だけれども、許可されていなかったり、面倒だったり、微妙なカスタマイズが出来なかったりして困る場合があります。標準で出力できて良い気もしますが、軽く調べただけでは見当たりませんでした。前述のようなやり方が紹介されているくらいなので、無いのでしょう。
この条件で使える物を考えてみると、Oracleなので、PL/SQLが使えます。列名ならUSER_TAB_COLUMNSがあります。なら出来るじゃないか、と仕事中の待ち時間が暇だったので書いてみた。
set serveroutput on set feedback off set verify off set trimspool on set linesize 32767 ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; prompt ********************************************************************** prompt &_DATE CSVファイル出力を開始します。 set term off spool '&2' DECLARE vTableName VARCHAR2(30) := '&1'; vColumns VARCHAR2(32767); vSelectSql VARCHAR2(32767); vFirstFlag BOOLEAN := TRUE; TYPE cusorType IS REF CURSOR; c cusorType; vOutputLine VARCHAR2(32767); BEGIN vColumns := NULL; vSelectSql := 'SELECT '; FOR vRec IN ( SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER(vTableName) ORDER BY COLUMN_ID ) LOOP IF NOT(vFirstFlag) THEN vColumns := vColumns || ','; vSelectSql := vSelectSql || ' || '','' || '; ELSE vFirstFlag := FALSE; END IF; vColumns := vColumns || vRec.COLUMN_NAME; vSelectSql := vSelectSql || vRec.COLUMN_NAME; END LOOP; vSelectSql := vSelectSql || ' FROM ' || vTableName; DBMS_OUTPUT.PUT_LINE(vColumns); OPEN c FOR vSelectSql; LOOP FETCH c INTO vOutputLine; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(vOutputLine); END LOOP; CLOSE c; END; / spool off set term on prompt &_DATE CSVファイル出力が完了しました。(ファイル名:&2) prompt ********************************************************************** exit
実行コマンドはこんな感じ。上記の内容を「OutputCsv.sql」の名前で保存し、そのディレクトリでSQL*Plusを実行する。tablenameはテーブル名、output.csvは出力ファイル名。
sqlplus -s user/pass @OutputCsv.sql tablename output.csv
サイレントモードにしているのは、接続されましたとかのログが邪魔っぽかったからです。実行結果はこんな感じになります。
C:\work>sqlplus -s user/pass @CsvOutput.sql tablename output.csv ********************************************************************** 2010-01-07 23:03:43 CSVファイル出力を開始します。 2010-01-07 23:03:43 CSVファイル出力が完了しました。(ファイル名:output.csv) ********************************************************************** C:\work>
複数テーブル出したいなら並べてしまえば良いです。ユーザ名は対象テーブルが入っているスキーマにしないといけません。USER_TAB_COLUMNSを使っているので。ALL_TAB_COLUMNSにしてOWNERを条件に入れ、テーブル名をスキーマ修飾すれば良いのですが、パラメータが多くなると面倒なので。
sqlplus -s user/pass @OutputCsv.sql tablename1 output1.csv sqlplus -s user/pass @OutputCsv.sql tablename2 output2.csv sqlplus -s user/pass @OutputCsv.sql tablename3 output3.csv sqlplus -s user/pass @OutputCsv.sql tablename4 output4.csv sqlplus -s user/pass @OutputCsv.sql tablename5 output5.csv
【注意点】
10gR2以降専用です。
古いバージョンでは、DBMS_OUTPUTで出力できるサイズの制限*1 *2に引っかかる可能性があるので、使い物にならないかもしれません。それでも使いたい場合、&_DATEを消した上で、とりあえずバッファサイズを最大にしてみて実行してみてください。それで駄目なら、UTL_FILEを使うとか、一時テーブルに格納するとかしてください。どちらにせよDBサーバ自体をいじる事になります。
対象テーブルが見当たらない場合はエラーになりますが、画面上は表示してくれません。出力ファイルをみれば駄目だった事は判ります。でも駄目だった事しか判りません。例外処理に力を入れるべきものでもないので、先にテーブルの存在くらいは別ルートで確認すること。出力ファイルの内容は以下のような感じになります。
DECLARE * 行1でエラーが発生しました。: ORA-00936: 式がありません。 ORA-06512: 行31
項目数が多いとかデータ形式によっては使えない可能性も十分あります。DATA_TYPEとかDATA_LENGTHとかを見て、取得列を絞るようにすればそのようなテーブルでも使えるようにはなりますが、使う対象として想定していないので気にしない事にします。