のえら

技術備忘とかメモとか.間違いはつっこんでいただきたい所存.アフィリエイトはやっていません.

OracleでSpoolしたときに余計な改行が出力される

Oracleのレコードをcsvファイルに出力したとき、行と行の間になぜか余計な改行が出力されてしまうので原因と回避方法を調査した。

環境

サーバー:Windows Server 2005
DB:Oracle 8i
出力方法:SQL*Plus の spool

現象

spoolを使用してcsvファイルを出力したときに、行と行の間に余計な空白行が出力されていることがあった。
例)
こういう形式で出力されるはず

"hoge","foo","ba
r"
"hoge2","foo2","bar"

が、以下のような出力になっていた。

"hoge","foo","ba
r"
←ここが空白行
"hoge2","foo2","bar"

レコードをいじって発生するデータを確認したところ、出力対象のカラムいずれかに改行コード(LFまたはCRLF)が含まれていると上記のように空白行が出力されることが判明。
改行コードがCRの場合は発生しなかったため、LFを含んでいると発生する様子。
Stirling を使用して出力したcsvファイルを確認してみたところ、LFがCR+LFに変換されて出力されていることがわかった。これ自体はOSに合わせてSQL*Plusが変換してるんだろうと思うのだけど、なぜ余計な空白行出力に繋がるのかは不明。。
文字コードにズレが発生した??

解決方法

Oracle Spool adds extra LF - Stack Overflow

SQL*Plus のオプション RECSEP に OFF を指定して、レコード・セパレータを出力しないようにする。
デフォルトでは WRAPPED になっている。
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05784-01/ch12040.htm#i2699269

#(疑問)このレコード・セパレータについてよく理解していなくて、出力しないようにしたのにレコード単位で行出力されるのはなんでなんだろ??

余談

改行自体をなくしてしまう場合は、対象のカラムに対して REPLACE を使用して改行コードを空白に置換する。

例)
改行コードCR, LF, CRLFすべてを空白に置換する場合
CR -> chr(13)
LF -> chr(10)
CRLF -> chr(13) || chr(10)

select
	replace(replace(replace(FOO, chr(13) || chr(10), ''), chr(10), ''), chr(13), '')
from
	hoge