ORACLE SQL*PlusからUTF-8 BOM付きファイルへ出力する

 こんにちは。石川さんです。実現するのに調べても答えがなかったので、書いておきます。きっと誰かのお役に立てると思います。

やりたいこと

 外部連携用のCSVデータファイルを作成するのに、相手先がUTF-8でなければならない、ということでした。PowerBuilderのアプリケーションはUTF-8の場合、BOM付きでないとエラーになってしまうので、すべてBOM付きに統一しましょう、となりました。PowerBuilderのアプリケーションはそれで問題なし、となりましたが、SQL*Plusからの出力はBOMなしでしか出力できず。UTF-8にするのは「NLS_LANG=.UTF8」を指定して実行するだけでできたので、簡単に終わると思ったのですけどねぇ。。。と、いうことでSQL*PlusからUTF-8のBOM付きで出力する方法です。

実現方法

 お急ぎの方に簡単に言うと、スクリプト実行時に、事前用意したBOM付きのファイルをコピーしてきて、そこにAPPENDでスプールする、ということだけです。BOM付のファイルってなんなのって、ファイルの先頭に決まった3バイトが入っているかどうか、ということなので、そのファイルを用意して続きを埋めていくようにしておけば、BOM付ファイルの出来上がり、というわけです。

 BOM付きUTF-8のヘッダファイルを用意します。ぼくはサクラエディタを使って用意しました。文字コードセットに「UTF-8」を選んで「BOM」にチェックをつけるだけです。

 これでBOM付きのファイルができました。今回ファイル名は「header.txt」としました。

スクリプトの作成

 SQL*Plusへ読み込ませるスクリプトファイルを作成します。以下はサンプルです。

WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR  EXIT 50
set termout off
set head off
set pagesize 0
set linesize 2000
set trimspool on
set timing off
set time off
set feedback off
set echo off
-- このスクリプトは環境変数に「NLS_LANG=.UTF8」をセットして実行すること。
-- BOM付きUTF8のファイルにするため、スプール(APPEND)を開始するまえに、BOM付きのヘッダ情報ファイルをshohin.csvとして作成しておくこと。
host copy header.txt shohin.csv
spool shohin.csv APPEND
SELECT T1.商品コード||','||
       T1.商品名||','||
       T1.商品価格
  FROM 商品マスタ T1
 WHERE T1.送信対象 = 1
ORDER BY T1.商品コード
;
spool off
exit 0

 このスクリプトの最初のポイントは、「host copy header.txt shohin.csv」のところです。スプールする直前に、ファイルをコピーしています。「host」はWindowsのコマンドを使います、というコマンドで、COPYコマンドを使って単にファイルをコピーしています。(ぼくの実行環境はWindowsサーバーです。Linuxだとcpとかになると思います。)

 第二のポイントは、スプールコマンド(spool)の最後に書いた「APPEND」です。これが記載されることによって、追記されるようになります。「APPEND」を指定しない場合は、デフォルトで「REPLACE」が指定されたことになり、ファイルが存在する場合は上書きされてしまいます。そうすると、せっかくコピーしたBOMが消えてしまいますので、注意が必要です。

 最後に入手したいデータを取り出すSQL文を書いて、「spool off」します。これで完了です。

スクリプトの実行

 上記のサンプルを「targetscript.sql」ファイルに保存したとして、以下のようなコマンドで実行します。これで、ファイルを現在のディレクトリに作成してくれます。実際にはスケジューラーにこのコマンドを環境変数付きで実行したので、もし環境変数が正しく機能しない場合は、ご容赦ください。

set NLS_LANG=.UTF8
sqlplus username/password@connectstring @targetscript.sql

まとめ

 SQL*PlusでBOM付きのUTF8ファイルへスプールするためには、最初にBOM付ファイルをコピーしてきて、APPENDでSPOOLするのがよいでしょう。