Oracle BLOB to Base64 and Base64 to BLOB: PL/SQL
This guide provides Oracle PL/SQL functions to convert BLOB to Base64 and Base64 to BLOB, with detailed code examples and explanations. These functions are critical for handling binary data in web applications, APIs, or data exchanges. Each example includes a description to clarify its purpose and usage.
Convert BLOB to Base64 in Oracle PL/SQL
This function converts a BLOB (e.g., images, PDFs) to a Base64-encoded string using Oracle's UTL_ENCODE
package. It processes the BLOB in chunks to manage large data efficiently and returns a CLOB containing the Base64 string.
CREATE OR REPLACE FUNCTION blob_to_base64(p_blob IN BLOB) RETURN CLOB IS
v_clob CLOB;
v_result CLOB;
v_offset NUMBER := 1;
v_chunk_size NUMBER := 32760;
v_raw RAW(32767);
BEGIN
IF p_blob IS NULL THEN
RETURN NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_blob) / v_chunk_size) LOOP
v_raw := DBMS_LOB.SUBSTR(p_blob, v_chunk_size, v_offset);
v_clob := v_clob || UTL_ENCODE.BASE64_ENCODE(v_raw);
v_offset := v_offset + v_chunk_size;
END LOOP;
v_result := UTL_RAW.CAST_TO_VARCHAR2(v_clob);
DBMS_LOB.FREETEMPORARY(v_clob);
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_LOB.ISTEMPORARY(v_clob) = 1 THEN
DBMS_LOB.FREETEMPORARY(v_clob);
END IF;
RAISE;
END blob_to_base64;
/
Example: Convert Image BLOB to Base64
This query retrieves a BLOB (e.g., an image) from a table and converts it to a Base64 string, ideal for displaying images in web applications or sending via APIs.
SELECT blob_to_base64(image_column) AS base64_image
FROM images_table
WHERE image_id = 100;
Example: Store Base64 Output in a Table
This example converts a BLOB to Base64 and stores the result in a temporary table for further processing, such as exporting to JSON or external systems.
INSERT INTO temp_base64 (id, base64_data)
SELECT image_id, blob_to_base64(image_column)
FROM images_table
WHERE image_id = 100;
Example: PL/SQL Block for BLOB to Base64 Conversion
This PL/SQL block retrieves a BLOB, converts it to Base64, and outputs the length of the resulting string for debugging or validation purposes.
DECLARE
v_blob BLOB;
v_base64 CLOB;
BEGIN
SELECT image_column INTO v_blob
FROM images_table
WHERE image_id = 100;
v_base64 := blob_to_base64(v_blob);
DBMS_OUTPUT.PUT_LINE('Base64 Length: ' || DBMS_LOB.GETLENGTH(v_base64));
END;
/
Convert Base64 to BLOB in Oracle PL/SQL
This function converts a Base64-encoded string (CLOB) back to a BLOB using UTL_ENCODE.BASE64_DECODE
. It processes the input in chunks to handle large data and returns the reconstructed BLOB.
CREATE OR REPLACE FUNCTION base64_to_blob(p_base64 IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_raw RAW(32767);
v_offset NUMBER := 1;
v_chunk_size NUMBER := 32760;
BEGIN
IF p_base64 IS NULL THEN
RETURN NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_base64) / v_chunk_size) LOOP
v_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_base64, v_chunk_size, v_offset)));
DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);
v_offset := v_offset + v_chunk_size;
END LOOP;
RETURN v_blob;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_LOB.ISTEMPORARY(v_blob) = 1 THEN
DBMS_LOB.FREETEMPORARY(v_blob);
END IF;
RAISE;
END base64_to_blob;
/
Example: Insert Base64 String as BLOB
This query inserts a Base64 string (e.g., from an API payload) into a table as a BLOB, suitable for storing images or files in the database.
INSERT INTO images_table (image_id, image_column)
VALUES (101, base64_to_blob(:base64_string));
Example: Update BLOB with Base64 String
This updates an existing BLOB column with a new BLOB derived from a Base64 string, useful for updating image or file data in a record.
UPDATE images_table
SET image_column = base64_to_blob(:base64_string)
WHERE image_id = 101;
Example: PL/SQL Block for Base64 to BLOB Conversion
This PL/SQL block converts a Base64 string to a BLOB and inserts it into a table, showing how to handle Base64 input programmatically.
DECLARE
v_base64 CLOB := 'YOUR_BASE64_STRING_HERE';
v_blob BLOB;
BEGIN
v_blob := base64_to_blob(v_base64);
INSERT INTO images_table (image_id, image_column)
VALUES (102, v_blob);
COMMIT;
END;
/
Example: Batch Convert Base64 to BLOB
This block processes multiple Base64 strings from a temporary table, converting them to BLOBs and storing them in the target table, ideal for bulk data imports.
BEGIN
FOR rec IN (SELECT id, base64_data FROM temp_base64 WHERE processed = 'N') LOOP
INSERT INTO images_table (image_id, image_column)
VALUES (rec.id, base64_to_blob(rec.base64_data));
UPDATE temp_base64 SET processed = 'Y' WHERE id = rec.id;
END LOOP;
COMMIT;
END;
/
Best Practices for Oracle BLOB and Base64 Conversion
- Validate Input: Check Base64 strings for proper formatting using regular expressions to prevent decoding errors.
- Optimize Chunk Size: Set
v_chunk_size
to 32,760 bytes or lower to avoidORA-06502
errors and optimize memory usage. - Handle Errors: Use exception blocks to manage memory leaks and ensure temporary LOBs are freed.
Resources
- Oracle Documentation: UTL_ENCODE Package - Official guide for Base64 encoding/decoding.
- Oracle Documentation: DBMS_LOB Package - Details on handling BLOBs and CLOBs.
- Oracle Base: Working with BLOBs and CLOBs - Practical tips for managing large objects.
- Stack Overflow: Oracle Base64 Encoding - Community discussions on Base64 in Oracle.
- Base64 Encoding Explained - Overview of Base64 encoding principles.