SQL*Plusからcsvファイルを出力する方法

今回はSQL*Plusからcsvファイルを出力する簡単な方法をご紹介します。

【ピアソンVUE専用】Oracle監督付き試験用受験チケット(電子チケット)

まず、最もオーソドックスな方法です。

select
'"' || s.saddr ||'",'||
'"' || s.sid ||'",'||
（中略）
'"' || s.creator_addr ||'",'||
'"' || s.creator_serial# ||'",'||
'"' || s.ecid ||'"'
from v$session s;

自力でダブルクォートとカンマをつけて出力する方法です。

出力結果は以下の様になります。

'"'||SYSDATE||'",'||'"'||P.SPID||'",'||'"'||S.SADDR||'",'||'"'||S.SID||'",'||'"'
--------------------------------------------------------------------------------
"20-NOV-20","8234","0000000112EFC8D8","268","26982","1007574","0000000112A6E920"
,"48","DBSNMP","0","2147483644","","","INACTIVE","DEDICATED","48","DBSNMP","orac
le","1234","test","59181","unknown","JDBC Thin Client","USER","00","0","","","",
"","00000001023A55B8","211580878","4mua4wc69sxyf","0","20-NOV-20","16777775","",
"","","","emagent_SQL_oracle_database","1131189607","incident_meter","2282512683
","","817072","-40016239","201","28417","0","94","18-NOV-20","50","NO","NONE","N
ONE","NO","OTHER_GROUPS","DISABLED","ENABLED","ENABLED","0","","NO HOLDER","",""
,"NO HOLDER","","","9186","388","SQL*Net message from client","driver id","14136
97536","0000000054435000","#bytes","1","0000000000000001","","0","00","272316890
8","6","Idle","0","50","WAITING","50037024","-1","0","SYS$USERS","DISABLED","FAL
SE","FALSE","FIRST EXEC","133","0000000112A6E920","1",""

set linesize でラインサイズを指定しないと途中で改行されてしまうので注意が必要です。

何より、カラム一つずつ手作業でダブルクォートとカンマを付けないといけないので、とても面倒ですね。

そこで方法２。set colsepでカラム区切りを指定します。

方法1に比べると大分、楽ですが・・・

SQL> set colsep ","
SQL>  select * from v$session where type='USER';

出力結果は以下の様になります。

000000007919B590, 5, 50223,4294967295,0000000079049098,2147483620
SYSRAC
0,2147483644, , ,INACTIVE,DEDICATED
0
SYS
oracle
212346
hostname.local , 0
pts/0 ,oraagent.bin@hostname.local (TNS V1-V3)
USER ,00 , 0, ,
, ,00000000D778ADC0, 222495440,7jycxu86n60qh
0,10-NOV-20, 33554439,
, ,

oraagent.bin@hostname.local (TNS V1-V3) , 3815317949
, 0

378, 771, 1, 5084, 0
94,10-NOV-20, 864923,NO ,NONE ,NONE ,NO
,DISABLED,ENABLED ,ENABLED

これも方法１同様、set linesize でラインサイズを指定しないと途中で改行されてしまうのですが、それより何よりchar型カラムの桁数分、スペースが出力されてしまうという大きな欠点があります。

そこで、方法3。set markup csv onを使う方法です。

SQL> set markup csv on
SQL> select * from v$session where type='USER';

出力結果は以下の様になります。

"000000007919B590",5,50223,4294967295,"0000000079049098",2147483620,"SYSRAC",0,2147483644,,,"INACTIVE","DEDICATED",0,"SYS","oracle","212346","hostname.local",0,"pts/0","oraagent.bin@hostname.local (TNS V1-V3)","USER","00",0,,,,,"00000000D778ADC0",222495440,"7jycxu86n60qh",0,"10-NOV-20",33554439,,,,,"oraagent.bin@hostname.local (TNS V1-V3)",3815317949,,0,,378,771,1,5084,0,94,"10-NOV-20",864529,"NO","NONE","NONE","NO",,"DISABLED","ENABLED","ENABLED",0,,"UNKNOWN",,,"UNKNOWN",,,166,445,"SQL*Net message from client","driver id",1650815232,"0000000062657100","#bytes",1,"0000000000000001",,0,"00",2723168908,6,"Idle",0,864530,"WAITING",8.6453E+11,-1,0,"SYS$USERS","DISABLED","FALSE","FALSE","FIRST EXEC",0,"0000000079049098",1,,0,0,"DISABLED",0,"oracle","FALSE"

set linesize でラインサイズを指定する必要はありません（と言うより、markup csvが有効な時はset linesizeを指定しても無効です）

デフォルトでダブルクォートが付与されますが、以下の様に指定する事でダブルクォート無しの出力も可能です。

SQL> SET MARKUP CSV ON QUOTE OFF

たった1行の指定で簡単にきれいなcsvファイルが出力されます。

但し、これが可能なのは12cR2以降になります。

以前の記事で紹介したSET MARKUP HTML ONが8iの時代から使えた事を思うと、なぜ今まで出来なかったのだろう…という気もしなくは無いですが。 bismarc256.hateblo.jp

 ともあれ12.2以降ならばとても便利な機能なので、それ以降のバージョンではSET MARKUP HTML ONと併用して使いまくろうと思っています。

勿論、csvファイルなので、エクセルに取り込んで表として見やすくすることも可能です。