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 avoid ORA-06502 errors and optimize memory usage.
Handle Errors: Use exception blocks to manage memory leaks and ensure temporary LOBs are freed.