How to Encode Base64 with UTL_ENCODE

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.