こんにちは、亀井です。

Oracle DatabaseでCLOBをVARCHAR2で取得する必要があることがたまにあるため、記事として共有しておくようにしました。

更新履歴

日付更新概要
2022/01/19記事公開

対応バージョン

製品名バージョン
Oracle Database11.2 – 19.6

CLOBの変換について

CLOBをVARCHAR2に変換する場合、DBMS_LOBパッケージを利用して変換することが出来ます。
変換するにはVARCHAR2の最大値（PL/SQL上では最大32767Bytes）を意識して変換する必要があります。

変換のPL/SQLコード

SQL*Plusにログインし、以下のコードを実行します。
l_amountは一度に出力する文字の長さです。
UTFにしたときに1文字が3～4Bytesを取りうることを考慮し、8000程度にしておくと良いと思います。

set serveroutput on

declare
  l_data                         CLOB;
  l_dest_offset                  INTEGER  := 1;
  l_src_offset                   INTEGER  := 1;
  l_amount                       INTEGER  := 4;

  l_lob_total_size               INTEGER;

begin
  -- CLOBデータの生成
  l_data := to_clob('CLOBのテスト');
  -- CLOBデータの長さを取得
  l_lob_total_size := DBMS_LOB.GETLENGTH(l_data);

  -- l_src_offsetは1～出力が完了した場所まで（CLOBの長さまでループ）
  WHILE l_src_offset < l_lob_total_size LOOP

    IF l_src_offset + l_amount > l_lob_total_size THEN
      -- 最大サイズよりバッファが大きい場合、最後のバイトまで調整
      l_amount := l_lob_total_size - l_src_offset + 1;
    END IF;

    -- CLOBデータの取得(DBMS_LOB.SUBSTR)とコンソールへの出力(DBMS_OUTPUT.PUT)
    -- l_src_offsetを開始位置として、l_amountの文字数だけ取得する
    DBMS_OUTPUT.PUT(DBMS_LOB.SUBSTR(l_data, l_amount, l_src_offset));

    -- 出力したデータ分、開始位置を進める
    l_src_offset   := l_src_offset + l_amount;
  END LOOP;
  -- 最後に改行を出力
  DBMS_OUTPUT.NEW_LINE;
end;
/