OracleでSelectした結果をCSVで出力したいなんてケースは結構多いと思いますが、Oracle単品で出力から圧縮までしたいと思ったことはありませんか?
私はしたいとは思いませんでしたが、お客さんから「したい」と言われました。
PLSQLでZipファイルを作成する。
Oracleには「UTL_COMPRESS」というパッケージがあり、圧縮、解凍が可能ですが、残念ながらZipには対応していません。
「AS_ZIP」と呼ばれるパッケージを導入することで簡単に対応することができました。
gunzipとZipの違いはヘッダーの違いだけなので、バイナリでゴリゴリ直せばZipになるそうです。(素晴らしい)
AS_ZIPを使ったZIP作成のサンプル
Zip作成プロシージャ
基本的には必要なファイルを add1file して finish_zip で作成、 save_zipで書き出しするだけ。
書き出し先はOracleDirectoryを指定しています。
CREATE_ZIP.sql
CREATE OR REPLACE PROCEDURE CREATE_ZIP
IS
-- OracleDirectory
DIRECTORY_NAME CONSTANT VARCHAR2(100) := 'DIR';
-- 直接記述時、書き出し文字コード
TXT_CODE CONSTANT VARCHAR2(100) := 'JA16SJIS';
vFileName1 VARCHAR2(256);
vFileName2 VARCHAR2(256);
vZipName VARCHAR2(256);
bZipped BLOB;
BEGIN
-- csvファイルを作成する
vFileName1 := 'test.csv';
create_csv(vFileName1);
-- 作成したファイルを追加
as_zip.add1file( bZipped, vFileName1, as_zip.file2blob (DIRECTORY_NAME,vFileName1));
-- ディレクトリの指定も可能
vFileName2 := 'dir/test.txt';
-- テキスト内容を直接記述して追加
as_zip.add1file( bZipped, vFileName2, utl_i18n.string_to_raw( 'dirの中にいる' , TXT_CODE ) );
-- ファイルの追加を終了
as_zip.finish_zip( bZipped );
vZipName := 'test.zip';
-- 書き出し
as_zip.save_zip( bZipped, DIRECTORY_NAME, vZipName);
END CREATE_ZIP;
/
Csv作成プロシージャ
おそらくよくやるSJISのCSVの作成プロシージャも公開。
utl_fileはputでもできますが、駄目文字で死ぬ場合があるためput_rawを使用してます。
CREATE_CSV.sql
CREATE OR REPLACE PROCEDURE CREATE_CSV(
vFileName IN VARCHAR2
)
IS
-- OracleDirectory
DIRECTORY_NAME CONSTANT VARCHAR2(100) := 'DIR';
-- CSV作成時に利用する定数
CSV_CODE CONSTANT VARCHAR2(100) := 'JA16SJIS';
CR_CODE CONSTANT VARCHAR2(10) := CHR(13);
LF_CODE CONSTANT VARCHAR2(10) := CHR(10);
NEW_LINE_CODE CONSTANT VARCHAR2(100) := CR_CODE||LF_CODE;
-- ファイル作成時に利用するファイルハンドル
vHandle UTL_FILE.FILE_TYPE;
BEGIN
vHandle := utl_file.fopen(DIRECTORY_NAME, vFileName, 'WB', 32767);
-- Testテーブルの中身を明細行として出力
for rec in (
select col1 || ',' || col2 as var -- csvの明細行を作成
from test
order by col1
) loop
-- 文字コードを指定して改行コードと共に出力
utl_file.put_raw(vHandle, utl_i18n.string_to_raw( rec.var || NEW_LINE_CODE , CSV_CODE ), TRUE );
end loop;
utl_file.fclose(vHandle);
END CREATE_CSV;
/
結果
きちんとできてますね。