A CLOB holds characters and a BLOB holds bytes. Turning text into bytes, for hashing, encryption, compression, or sending over the network, means choosing a character set. DBMS_LOB.CONVERTTOBLOB encodes a CLOB into a BLOB in the character set you name, and CONVERTTOCLOB decodes it back.
Code for This Guide
The main examples are in the examples/pkg-dbms-lob folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_lob.converttoblob(dest_blob, src_clob, amount, dest_offset_in_out,
src_offset_in_out, blob_csid, lang_context_in_out, warning_out);
dbms_lob.converttoclob(dest_clob, src_blob, amount, dest_offset_in_out,
src_offset_in_out, blob_csid, lang_context_in_out, warning_out);Round Trip in UTF-8
Example:
declare
v_text clob := 'Café São Paulo';
v_bytes blob;
v_back clob;
v_dest integer := 1;
v_src integer := 1;
v_lang integer := dbms_lob.default_lang_ctx;
v_warn integer;
begin
dbms_lob.createtemporary(v_bytes, true);
dbms_lob.converttoblob(v_bytes, v_text, dbms_lob.lobmaxsize, v_dest, v_src,
nls_charset_id('AL32UTF8'), v_lang, v_warn);
dbms_output.put_line(dbms_lob.getlength(v_text) || ' characters = '
|| dbms_lob.getlength(v_bytes) || ' bytes in UTF-8');
dbms_lob.createtemporary(v_back, true);
v_dest := 1; v_src := 1;
dbms_lob.converttoclob(v_back, v_bytes, dbms_lob.lobmaxsize, v_dest, v_src,
nls_charset_id('AL32UTF8'), v_lang, v_warn);
dbms_output.put_line('back: ' || v_back);
end;
/Output:
14 characters = 16 bytes in UTF-8 back: Café São Paulo PL/SQL procedure successfully completed.
The text has 14 characters but 16 bytes in UTF-8, because é and ã take two bytes each. Decoding the BLOB with the same character set gives the original text back.
Different Character Sets
Example:
declare
v_text clob := 'Café São Paulo';
v_bytes blob;
v_dest integer;
v_src integer;
v_lang integer;
v_warn integer;
begin
for cs in (select column_value as name
from table(sys.odcivarchar2list('AL32UTF8', 'WE8ISO8859P1',
'US7ASCII'))) loop
dbms_lob.createtemporary(v_bytes, true);
v_dest := 1; v_src := 1; v_lang := dbms_lob.default_lang_ctx;
dbms_lob.converttoblob(v_bytes, v_text, dbms_lob.lobmaxsize, v_dest, v_src,
nls_charset_id(cs.name), v_lang, v_warn);
dbms_output.put_line(rpad(cs.name, 13) || dbms_lob.getlength(v_bytes) || ' bytes, '
|| rawtohex(dbms_lob.substr(v_bytes, 4, 1)) || '..., warning '
|| v_warn);
dbms_lob.freetemporary(v_bytes);
end loop;
end;
/Output:
AL32UTF8 16 bytes, 436166C3..., warning 0 WE8ISO8859P1 14 bytes, 436166E9..., warning 0 US7ASCII 14 bytes, 43616665..., warning 1 PL/SQL procedure successfully completed.
- AL32UTF8 encodes é as the two bytes C3A9.
- WE8ISO8859P1 encodes it as the single byte E9, so the text takes 14 bytes.
- US7ASCII has no é: it is replaced by e, and WARNING is 1, DBMS_LOB.WARN_INCONVERTIBLE_CHAR.
Things to Know
- Always decode with the character set used to encode, or non-ASCII characters come back wrong.
- Check WARNING after converting to a smaller character set, since lost characters do not raise an error.
- Reset the offsets and language context before each new conversion with the same variables.
Related Guides
Conclusion
DBMS_LOB.CONVERTTOBLOB turns text into bytes in a chosen character set, and CONVERTTOCLOB turns them back. Use UTF-8 unless a system needs something else, decode with the same character set, and check the warning for characters that could not be converted.
