Handling Large Files Using UTL_ENCODE.BASE64_ENCODE / DECODE (Doc ID 469064.1)

Last updated on MAY 21, 2013

Applies to:

PL/SQL - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 21-May-2013***

Goal

Because of the fact that the UTL_ENCODE functions BASE64_ENCODE and BASE64_DECODE take RAW parameters , they are subject to the 32Kb limitation on the size of a PL/SQL variable.

This makes handling large files with these procedures difficult because Base64 encoding normally requires the whole file to be processed at once, and you cannot join together 2 base64 encoded files unless you know how big each part was in the first place.

However, we can use the fact that Base64 encoding creates files with 76 byte fixed length records to get around this. When data is Base64 encoded, the resulting data is larger than the original by a factor of 4/3.  If we therefore process input data in 57 bytes chunks, then the resulting data will be a series of 76 byte records which will be readable by third party decoders.

By the same token , if we need to decode a large Base64 file we use the reverse technique and process the file a record at a time in text mode, rather than trying to load the whole file into memory at once.

This document shows example scripts that do this.

To run these scripts you need to have granted the CREATE ANY DIRECTORY privilege to your oracle user so that they can read and write the files to/from the operating system:

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms