ねら～ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

CLOB型の(文字数ではなく)バイト数を返却するファンクションを自作(Oracle Database)

Database Oracle PL/SQL

昔の記事（https://gonsuke777.hatenablog.com/entry/20150129/1422535354）が間違っていたので、訂正を兼ねた記事を作成彡(ﾟ)(ﾟ)

CLOB型の(文字数ではなく)バイト数を返却するファンクションを自作してみます。

CONVERTTOBLOBプロシージャでCLOB型をBLOB型に変換して、そのバイト数をDBMS_LOB.GETLENGTHを変換するやで彡(ﾟ)(ﾟ)

1. ファイルのバイト数と中身

下記のUTF-8 エンコードのテキストファイル(2057850バイト)をOracle Databaseにロードして検証します。ASCII文字とひらがなと4バイトの文字を混ぜています。

ls -la clob_test2.txt
-rw-r--r--. 1 oracle oinstall 2057850 Aug 19 22:29 clob_test2.txt

cat clob_test2.txt

aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…
aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…
：
aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…
aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…

テキストファイルのロードは下記記事を参照彡(ﾟ)(ﾟ)

DBMS_LOB.LoadClobFromFileプロシージャを使って
テキストファイルを Oracle Database の CLOB型列にロードする。
https://gonsuke777.hatenablog.com/entry/20160815/1471259132

2. 自作ファンクションFNC_CLOB_LENBの作成

自作ファンクションの作成、前述した通りCONVERTTOBLOBプロシージャで CLOB型をBLOB型に変換して、そのバイト数をDBMS_LOB.GETLENGTHを変換してます。

CREATE OR REPLACE FUNCTION FNC_CLOB_LENB(
  clob_in IN CLOB
)
RETURN NUMBER
IS
  blob_tmp BLOB;
  i_d_offset INTEGER;
  i_s_offset INTEGER;
  i_l_contxt INTEGER;
  i_warning  INTEGER;
BEGIN
  -- Initialize
  DBMS_LOB.CREATETEMPORARY(blob_tmp, FALSE, DBMS_LOB.CALL);
  i_d_offset := 1;
  i_s_offset := 1;
  i_l_contxt := 0;
  -- CONVERT CLOB to BLOB
  DBMS_LOB.CONVERTTOBLOB(
      blob_tmp                   -- dest_lob
    , clob_in                    -- src_blob
    , DBMS_LOB.LOBMAXSIZE        -- amount
    , i_d_offset                 -- dest_offset
    , i_s_offset                 -- src_offset
    , NLS_CHARSET_ID('AL32UTF8') -- blob_csid
    , i_l_contxt                 -- lang_context
    , i_warning                  -- warning
  );
  RETURN DBMS_LOB.GETLENGTH(blob_tmp);
END;
/

Function created.

3. 検証結果

結果は下記の通り、自作ファンクションの方は ls の結果と一致してるやで！彡(^)(^)

SELECT DBMS_LOB.GETLENGTH(C2), FNC_CLOB_LENB(C2) FROM TEST_CLOB;

DBMS_LOB.GETLENGTH(C2) FNC_CLOB_LENB(C2)
---------------------- -----------------
               1033498           2057850

4. 参考

以下のマニュアルと記事を参考にしました彡(ﾟ)(ﾟ)

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
93 DBMS_LOB
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_LOB.html#GUID-A35DE03B-41A6-4E55-8CDE-77737FED9306
　
oracle CLOBの項目のバイト数を計算するテクニック
https://qiita.com/wagase/items/39c29d3720901e6ef331
　
Oracle Database SQL言語リファレンス 18c
NLS_CHARSET_ID
https://docs.oracle.com/cd/E96517_01/sqlrf/NLS_CHARSET_ID.html#GUID-733B03A0-CD66-4645-A323-401A176499E3