CODEWITHSUNDEEP
oracle-databaseplsql
kolobok_ua
kolobok_ua

Reputation: 4200

How to convert CLOB to UTF8 BLOB in Oracle

I need to convert data from CLOB to UTF8-encoded BLOB in Oracle. How can I do it?

Upvotes: 0

Views: 9327

Answers (1)

kolobok_ua
kolobok_ua

Reputation: 4200

Following function can be used:

CREATE OR REPLACE FUNCTION clob_to_blob(src_clob CLOB) RETURN BLOB IS
    tgt_blob BLOB;
    amount INTEGER := DBMS_LOB.lobmaxsize;
    dest_offset INTEGER := 1;
    src_offset INTEGER  := 1;
    blob_csid INTEGER := nls_charset_id('UTF8');
    lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    warning INTEGER := 0;
begin
    if src_clob is null then
        return null;
    end if;

    DBMS_LOB.CreateTemporary(tgt_blob, true);
    DBMS_LOB.ConvertToBlob(tgt_blob, src_clob, amount, dest_offset, src_offset, blob_csid, lang_context, warning);
    return tgt_blob;
end clob_to_blob;

Usage

UPDATE mytable SET column1_blob = clob_to_blob(column2_clob);

Upvotes: 5

Related Questions