PL/SQLでループ処理中BLOB→CLOB変換してBDMS_LOB.INSTR()を使用する

はじめに

この記事では次のような要件を実現するためのPL/SQLを掲載しています。これが最適の解かどうかは微妙ですが、ともかくも私はこう切り抜けた、という話です。

要件

一連のBLOBデータとしてOracle DBに格納されているHTMLファイルのなかに、（これまた）一連のキーワードが含まれているかいないかを判定したい。

制約

BLOBのままではOracle DBのDBMS_LOB.INSTR()関数による文字列検索は実施できない。
DBMS_LOB.CONVERTTOCLOB()プロシージャは（まさにそれがプロシージャであるために）通常のSQL構文中では使用できない。

実装

要件を実現するために（そして制約を克服するために）、以下のような実装を行うことになりました。

PL/SQLでBLOB→CLOB変換を行うネストした関数を定義
対象のデータをカーソルを使用したループ処理をコード
ループの中で対象カラムを前述の関数でBLOB→CLOB変換
変換結果に対してINSTR()関数で文字列検索を実施して結果を出力

データ構造

仕様上いたしかたない実装としてこのようなエンティティ関連があるとします。

記事（article）エンティティと画像（picture）エンティティがあり、記事エンティティは何かしらのHTMLファイルを管理し、画像エンティティは画像のバイナリファイルとそのファイル名を管理しています。画像は記事の本文に埋め込んで表示するためのものです。画像エンティティ側には記事エンティティの特定レコードのIDを参照する外部キーのカラム、つまり記事IDカラム（article_ID）があります。制約のあるなしはでどうでもいいです。

f:id:m12i:20130323070722p:plain

問題は、この記事IDで画像と記事がデータ構造上関連づけられているからといって、記事に関連づけられた画像が記事の本文（body）中で使用されているとは限らないということです。記事本文中では画像をファイル名で参照しています。記事や画像がどのように加工されてどのような仕組みで最終的にシステムのユーザが操作するブラウザ画面に出力されることになるかについてはここでは問題としません。

記事のレコードを登録する際にその本文内容をチェックして、都度画像エンティティや関係テーブルを更新するような次善策もあるでしょうが、現状そのような仕組みはないものとします。

図はMySQL Workbenchでつくっていますが、実際のDBMS想定はOracleです。

問題発生

さて、これらのデータを使用して記事制作機能を提供するC#.NETベースのシステムがリリースされてからはや10年弱、画像ファイルは貯まりにたまってものすごい量です。その上悪いことに、記事を新規作成する際に既存の記事内容を流用する機能のおかげで、記事に紐付く画像も無闇矢鱈とコピーを繰り返され、ひとつの記事に紐付く画像数が1,000件を超す事態となりました。

こうなるとただに画像エンティティのデータの総量をもって表領域を圧迫するのみならず、システムの処理中に特定のロジックで非常な負荷がかかってOutOfMemory例外が発生する事態にまで発展します。繰り返しエラーが発生すると、システムエラー画面の背後ではシステム・リソースの食いつぶしをトリガーにして、IISの自動リサイクルが頻発。ハード自体にもあまりよい影響はなさそうです。さいわいアプリケーションサーバ上に同居するシステムはほかになかったのですが･･･。

そもそもロジックもイケてないのはあきらかでしたが、なにぶん改修をしようにもそのような工数は割けない状態です。だいたい来月から大規模改修プロジェクトがはじまるのでコードフリーズがかかります。だったらもうデータを削除するしかありません。そして／とはいえ、現に記事中で使用されているデータを削除してしまうわけにもいきません。削除対象となるのは記事IDによって記事エンティティと紐付けられていながら実際には記事本文中で使用されていない画像です。

そのような絞り込みを行うには記事本文のLOBデータ内を画像ファイル名で検索してみるしかありません。

SQLの制約

さて、エンティティ関連のほかにもう2つ問題がありました。

OracleデータベースにはDBMS_LOBというパッケージ（データベース・オブジェクト）があり、DBMS_LOB.INSTR()関数はCLOBデータのなかに特定の文字列が出現する位置を返します。けれどもBLOBは検索対象にできません。
さいわいにもDBMS_LOB.CONVERTTOCLOB()プロシージャはBLOB型データをCLOB型データに変換できます。けれどもこのプロシージャで変換結果のCLOB型データを得るには、CLOB型の変数を出力パラーメータとして与える必要があります。このような操作は、手続き型言語の考え方に基づくものであり、実際SQLの構文では実現不可能な操作です。

PL/SQLによる解法

したがって、ODBC接続を行ってデータベースに検索するC#なりJavaなりのプログラムを書くのでなければ（あるいはLL言語でスクリプトを組むのでなければ）、PL/SQLでロジックを記述する必要があります。

--いろいろ設定
set SERVEROUTPUT on size 1000000;
set TRIMOUT on;
set LINESIZE 32767;
set PAGESIZE 50000;
set LONG 1000000;
set LONGC 1000000;

--処理ブロックの定義を開始
declare

    --BLOB型データをCLOB型データに変換して返す関数 
    function blob_to_clob( src_blob in BLOB )
    return CLOB
    is

        --変換処理の際の各種パラメータ
        amount       INTEGER := DBMS_LOB.LOBMAXSIZE;
        dest_offset  INTEGER := 1;
        src_offset   INTEGER := 1;
        default_csid INTEGER := DBMS_LOB.DEFAULT_CSID;
        lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
        warning      INTEGER;
        
        --変換処理結果を格納するCLOB型変数
        dest_clob CLOB;
        
    begin

        --CLOB型変数を初期化する
        dbms_lob.createtemporary( dest_clob, TRUE, dbms_lob.call );

        --BLOB型データをCLOB型データに変換する
        dbms_lob.converttoclob(
            dest_clob,    -- 宛先LOBインスタンスのLOBロケータ。
            src_blob,     -- ソースLOBインスタンスのLOBロケータ。
            amount,       -- ソースLOBから変換するバイト数。
            dest_offset,  -- 宛先LOBの書込み開始位置を示すオフセット。（出力パラメタでもあるのでリテラルを与えてはダメ。）
            src_offset,   -- ソースLOBの読込み開始位置を示すオフセット。バイト数で指定します。（出力パラメタでもあるのでリテラルを与えてはダメ。）
            default_csid, -- 変換データの目的とするキャラクタ・セットID。
            lang_context, -- 現行の変換に関する、シフト・ステータスなどの言語コンテキスト。（出力パラメタでもあるのでリテラルを与えてはダメ。）
            warning       -- 警告メッセージ。このパラメータは、変換時になんらかの異常が発生したことを示します。警告メッセージを確認する必要があります。（出力パラメタなのでリテラルを与えてはダメ。）
        );

        --変換したデータを返却する
        return dest_clob;

    end;

-- 主処理を開始
begin

    -- 非明示的カーソルをオープンしループ処理
    for rec in(
        -- カーソルの元になるSELECT文
        select
            A.id         article_id
            ,A.body      article_body
            ,P.id        picture_id
            ,P.file_name picture_file_name
            /* ･･･その他必要なカラム･･･ */
        from
            Article A
        inner join
            Picture P
        on
            A.id = P.article_id
        where
            /* ･･･何かしらの抽出条件･･･ */
        order by
            /* ･･･何かしらの順序条件･･･ */
    )loop
        -- ループ処理

        dbms_output.put_line( ''
                || to_char( rec.article_id ) || chr( 9 ) 
                || to_char( rec.picture_id ) || chr( 9 ) 
                || to_char( rec.picture_file_name ) || chr( 9 ) 
                || to_char(
                        -- BLOB→CLOB変換とCLOB全文検索処理を実施（とりあえず結果をy/nで出力してみる）
                        case 
                        when dbms_lob.instr( blob_to_clob( rec.article_body ), rec.picture_file_name, 1, 1 ) > 0 then 'y'
                        else 'n'
                        end
                )
        );

    end loop;

--ブロック中でエラー発生した場合の処理
exception

    --エラー発生時のメッセージ出力
    when others then
        dbms_output.put_line( '***** Error *****' );
        dbms_output.put_line( substr( DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 200 ) );

end;
/

変換結果を一時的に格納するCLOB型変数は使用前に初期化処理が必要で、使用後には適切な後始末も必要のようです。DBMS_LOB.CONVERTTOCLOB()の使用例をWeb上で探すと、単発の使用はあっても、ループ処理のなかでくりかえし変数を使用するような例はなく、実際単純に初期化／後始末をしたコードでは2件目移行の処理でエラーとなってしまいました。

そのためBLOB→CLOB変換処理をブロック内にネストしたかたちで定義した関数にて行い、ループ処理内では変数を使い回さなくてもよいようにしました。