如何将BLOB对象转换为PL JSON？转换后格式异常如何修复

Hey there, let's break down why your image data is getting messed up when putting it into JSON and fix it properly!

The Root Cause #

Your problem stems from a core mismatch between data types:

• A BLOB stores raw binary image bytes with no character encoding attached.
• A CLOB is designed for text data tied to a specific character set.

When you try to directly convert a BLOB to a CLOB, Oracle tries to interpret those raw image bytes as text in your database's default character set—which they aren't! That's exactly why you're seeing garbled, corrupted data. On top of that, JSON doesn't support raw binary data natively, so we need a safe way to represent those bytes as a valid JSON string.

The Fix: Use Base64 Encoding #

Base64 is the standard solution here—it converts any binary data into a printable ASCII string that plays perfectly with JSON. Here's your modified function to handle this correctly:

FUNCTION get_person_image(v_file_name varchar2) RETURN json AS
    tmp_blob BLOB DEFAULT EMPTY_BLOB();
    tmp_bfile BFILE := NULL;
    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    base64_str VARCHAR2(32767); -- Use CLOB instead if dealing with large images
    v_ret_json JSON := JSON();
BEGIN
    -- Load the BFILE into a temporary BLOB
    tmp_bfile := BFILENAME('PICTURES', v_file_name);
    DBMS_LOB.OPEN(tmp_bfile, DBMS_LOB.FILE_READONLY);
    DBMS_LOB.CREATETEMPORARY(tmp_blob, TRUE);
    DBMS_LOB.LOADFROMFILE(
        dest_lob => tmp_blob,
        src_lob => tmp_bfile,
        amount => DBMS_LOB.LOBMAXSIZE,
        dest_offset => dest_offset,
        src_offset => src_offset
    );
    DBMS_LOB.CLOSE(tmp_bfile);

    -- Convert BLOB to Base64 string (safe for JSON serialization)
    base64_str := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(tmp_blob));

    -- Add the valid Base64 string to your JSON object
    v_ret_json.put('image_data', base64_str);
    v_ret_json.put('file_name', v_file_name); -- Optional: include filename for reference

    -- Clean up temporary resources
    DBMS_LOB.FREETEMPORARY(tmp_blob);

    RETURN v_ret_json;
EXCEPTION
    WHEN OTHERS THEN
        -- Ensure resources are released even if an error occurs
        IF DBMS_LOB.ISOPEN(tmp_bfile) = 1 THEN
            DBMS_LOB.CLOSE(tmp_bfile);
        END IF;
        IF DBMS_LOB.ISTEMPORARY(tmp_blob) = 1 THEN
            DBMS_LOB.FREETEMPORARY(tmp_blob);
        END IF;
        RAISE;
END;
/

Key Changes Explained #

1. Base64 Conversion: We use UTL_ENCODE.BASE64_ENCODE to turn the BLOB into a Base64-encoded RAW, then UTL_RAW.CAST_TO_VARCHAR2 to convert that RAW to a JSON-compatible string.
2. Resource Cleanup: Added explicit cleanup for the temporary BLOB and BFILE in both the main flow and exception block to avoid resource leaks.
3. JSON Compatibility: Instead of forcing binary/CLOB data into JSON, we store the Base64 string—this is the industry standard for including binary data in JSON payloads.

For Larger Images #

If your images exceed the VARCHAR2 limit (typically 32KB), switch to a CLOB for the Base64 string:

DECLARE
    base64_clob CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(base64_clob, TRUE);
    UTL_ENCODE.BASE64_ENCODE(src => tmp_blob, dest => base64_clob);
    v_ret_json.put('image_data', base64_clob);
    DBMS_LOB.FREETEMPORARY(base64_clob);
END;

This will keep your image data intact when serialized into JSON, no more garbled formatting!

内容的提问来源于stack exchange，提问作者Mohsen