csvってどうやって出力してますか？

このエントリは JPOUG Advent Calendar 参加ブログで一日目です。

Advent Calendarについては、このあたりを見ていただくとして、早速本題に入ります。

地味な話題ですが、みなさん、Oracle　Databaseからcsvってどうやって出力していますか？
私は仕事柄結構csv出力する機会があり、こんな風にSQLPLUSから出力してました。

set lin 999 --１行の長さを999文字にする
set colsep ,--区切り文字を「,」にする
set feedback off -- xx行が表示されました　のようなメッセージ（フィードバック）表示をOFFにする
set pages 0 --ページをOFF(ヘッダ、フッタ等を非表示にする）
set echo off -- スクリプト実行時にエコー表示しない

spool test2.csv
select 'EMPNO','ENAME','JOB','MGR','HIREDATE','SAL','COMM','DEPTNO' from dual;--列名表示
--colsepだけだと列が固定長で表示されてしまうため、||で各列を連結している表示
select EMPNO||','||ENAME||','||JOB||','||MGR||','||HIREDATE||','||SAL||','||COMM||','||DEPTNO from EMP;
spool off;

実行例

SQL>@csv.sql

出力例(test2.csv)

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,80-12-17,800,,20
7499,ALLEN,SALESMAN,7698,81-02-20,1600,300,30
7521,WARD,SALESMAN,7698,81-02-22,1250,500,30
7566,JONES,MANAGER,7839,81-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,81-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,81-05-01,2850,,30
7782,CLARK,MANAGER,7839,81-06-09,2450,,10
7788,SCOTT,ANALYST,7566,87-04-19,3000,,20
7839,KING,PRESIDENT,,81-11-17,5000,,10
7844,TURNER,SALESMAN,7698,81-09-08,1500,0,30
7876,ADAMS,CLERK,7788,87-05-23,1100,,20
7900,JAMES,CLERK,7698,81-12-03,950,,30
7902,FORD,ANALYST,7566,81-12-03,3000,,20
7934,MILLER,CLERK,7782,82-01-23,1300,,10

出力自体はこれでほとんど問題ないのですが、実行するSQLは毎回違うので、
その度に、csv.sqlみたいなものを毎回書き直すの面倒で仕方ないです。

そこで、csv出力のいい方法がないか調べてみました。

1. Oracle SQL Developer
Oracle 11g以降はOracle Clientインストール時に「ランタイム」もしくは、
「管理者」を選ぶともれなく、Oracle SQL Developerもインストールされます。
このSQL Developerがなかなか便利でPL/SQLの開発だけでなく、SQL文の実行も出来たりするのですが、
SQLの実行結果を様々なフォーマットでエクスポートする機能があり、
csv形式でエクスポートする事ができます。エクスポートされたcsvは各列が「"」で括ってあるため、
改行を含んだものでも綺麗な形でEXCEL等で取り込むことが出来ます。

なお、Oracle SQL Developerは初回起動時にJavaのパス入力を求められますが、Oracle Clientインストール時に
合わせてJDKもインストールされるため、Javaがインストールされていない環境でも「ORACLE_HOME\jdk\bin\java.exe」を指定すれば
Oracle SQL Developer　を使うことが出来ます。

2. ちょっとperl スクリプト書いちゃう
Oracle Client(Windows版）インストール時に「ランタイム」もしくは、「管理者」を選ぶともれなく、perlもインストールされます。
このPerlですが、Oracle Databaseへ接続するためのライブラリ DBD::Oracleも含まれているため、
Oracle Clientをインストールしておけば、ちょっとしたOracle Database操作のスクリプトを書くことが出来ます。

DBD::Oracleの習作も含めて、任意のSQL文をcsv出力するperl スクリプトを書いてみます。
（にわかperlerなので、DBIの使い方はもろもろ、stackoverflow.comとperlmonks.orgをベースにスクリプト書いてます。

db2csv.pl

#以下をほぼほぼ使用して、引数にOracle接続子、標準入力にSQL文を入れるようにしている
#http://stackoverflow.com/questions/65447/getting-data-from-an-oracle-database-as-a-csv-file-or-any-other-custom-text-for
#http://www.perlmonks.org/?node_id=264623


use DBI;
use DBD::Oracle;

#引数を/,@で区切ってそれぞれ、ユーザ名、パスワード、TNS名として取得
@args=split(/\/|@/,$ARGV[0]);
$user = $args[0];
$pass = $args[1];
$db = $args[2];

$dbh = DBI->connect( "dbi:Oracle:$db", $user, $pass );

print "SQL>";

$sql="";
while (<STDIN>){
  $sql=$sql.$_;
  last if($sql=~/;/);
}
#入力で「;」があったらSQLの終了と判定。本当は/でも判定したいけど、除算の/と区別つかないので断念
$sql =~ s/;//;

$dbh->{LongReadLen} = 65535;
$dbh->{PrintError} = 0;
$sth = $dbh->prepare($sql);

$sth->execute();


if ( $dbh->err() ) { die $dbh->errstr(); }

#列名の出力
$colnames = $sth->{NAME};
print "\"";
print join "\",\"", @$colnames;
print "\"\n";

#各列データの値に「"」と「,」を付与して（csv形式にして）出力
while ( $arrayref = $sth->fetchrow_arrayref ) {
    print "\"";
    print join "\",\"", @$arrayref;
    print "\"\n";
}

$dbh->disconnect();

実行例（Oracle Clientに付属のperlにPATHを通して実行）

C:\Documents and Settings\Administrator>set PATH=%PATH%;C:\app\Administrator\product\11.2.0\client_1\perl\bin
C:\Documents and Settings\Administrator>perl db2csv.pl scott/tiger@ora11g
SQL>select * from emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
"7369","SMITH","CLERK","7902","80-12-17","800","","20"
"7499","ALLEN","SALESMAN","7698","81-02-20","1600","300","30"
"7521","WARD","SALESMAN","7698","81-02-22","1250","500","30"
"7566","JONES","MANAGER","7839","81-04-02","2975","","20"
"7654","MARTIN","SALESMAN","7698","81-09-28","1250","1400","30"
"7698","BLAKE","MANAGER","7839","81-05-01","2850","","30"
"7782","CLARK","MANAGER","7839","81-06-09","2450","","10"
"7788","SCOTT","ANALYST","7566","87-04-19","3000","","20"
"7839","KING","PRESIDENT","","81-11-17","5000","","10"
"7844","TURNER","SALESMAN","7698","81-09-08","1500","0","30"
"7876","ADAMS","CLERK","7788","87-05-23","1100","","20"
"7900","JAMES","CLERK","7698","81-12-03","950","","30"
"7902","FORD","ANALYST","7566","81-12-03","3000","","20"
"7934","MILLER","CLERK","7782","82-01-23","1300","","10"

まとめ
・Oracle SQL Developer便利
・Oracle Client入れるともれなくperlも使えるよ！

明日は
明日はRyota Watabe さんです。よろしくお願いします。