Base64 turns any bytes into plain letters, digits, and a few symbols, so binary data can travel in JSON, XML, e-mail, and URLs. UTL_ENCODE encodes and decodes Base64, and also quoted-printable, the encoding e-mail uses for text with special characters.
Code for This Guide
The main examples are in the examples/pkg-raw-text 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
utl_encode.base64_encode(r) -- RAW in, RAW out utl_encode.base64_decode(r) utl_encode.text_encode(text, charset, utl_encode.base64) utl_encode.quoted_printable_encode(r)
Encode and Decode Text
Example:
declare
v_text varchar2(100) := 'Boarding pass NM150, seat 12A';
v_b64 varchar2(200);
begin
v_b64 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_text)));
dbms_output.put_line('Base64: ' || v_b64);
dbms_output.put_line('decoded: ' || utl_raw.cast_to_varchar2(
utl_encode.base64_decode(utl_raw.cast_to_raw(v_b64))));
dbms_output.put_line('text_encode: ' || utl_encode.text_encode('Café crème', 'AL32UTF8',
utl_encode.base64));
dbms_output.put_line('quoted-printable: ' || utl_raw.cast_to_varchar2(
utl_encode.quoted_printable_encode(
utl_i18n.string_to_raw('Café = 5€', 'AL32UTF8'))));
end;
/Output:
Base64: Qm9hcmRpbmcgcGFzcyBOTTE1MCwgc2VhdCAxMkE= decoded: Boarding pass NM150, seat 12A text_encode: Q2Fmw6kgY3LDqG1l quoted-printable: Caf=C3=A9 =3D 5=E2=82=AC PL/SQL procedure successfully completed.
- BASE64_ENCODE works on RAW, so the text is cast to RAW first and the result cast back to read it.
- TEXT_ENCODE does it in one call, with the character set named, so Café crème is encoded as UTF-8 bytes.
- QUOTED_PRINTABLE_ENCODE writes each special byte as =XX, so é becomes =C3=A9, the euro sign =E2=82=AC, and = itself =3D.
Encode a BLOB
BASE64_ENCODE takes RAW, up to 32,767 bytes. A BLOB is encoded in pieces whose size is a multiple of 3, so each piece encodes to whole Base64 groups and the pieces join correctly.
Example:
-- the logo as a data URI, for an HTML page or an e-mail: Base64 in pieces of 3 x 16 bytes
declare
v_logo blob;
v_uri clob := 'data:image/png;base64,';
v_piece raw(48);
v_pos integer := 1;
begin
v_logo := to_blob(bfilename('NIMBUS_FILES', 'logo.png'));
while v_pos <= dbms_lob.getlength(v_logo) loop
v_piece := dbms_lob.substr(v_logo, 48, v_pos);
v_uri := v_uri || replace(replace(utl_raw.cast_to_varchar2(
utl_encode.base64_encode(v_piece)), chr(13)), chr(10));
v_pos := v_pos + 48;
end loop;
dbms_output.put_line(dbms_lob.getlength(v_logo) || ' bytes -> '
|| dbms_lob.getlength(v_uri) || ' characters');
dbms_output.put_line(dbms_lob.substr(v_uri, 70, 1) || '...');
end;
/Output:
117 bytes -> 178 characters data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAIAAACQkWg2AAAA... PL/SQL procedure successfully completed.
The 117-byte logo becomes a data URI ready to put in an HTML img tag. The line breaks that BASE64_ENCODE adds every 64 characters are removed.
Things to Know
- Base64 makes data about a third larger: every 3 bytes become 4 characters.
- Base64 is an encoding, not encryption; anyone can decode it.
- Remove line breaks for JSON and data URIs; keep them for e-mail.
Related Guides
- How to Convert Character Sets with UTL_RAW.CONVERT
- How to Work with Temporary LOBs in PL/SQL (DBMS_LOB)
Conclusion
UTL_ENCODE converts bytes to Base64 and quoted-printable and back. Cast text to RAW in a known character set, encode BLOBs in pieces of a multiple of 3 bytes, and strip line breaks when the result goes into JSON or a data URI.
