convert blob(image) to varchar in oracle

Question

I am trying to read blob (image) from an oracle db and display it in html. The image is larger than the buffer size so I have to split it first and then append all the sub-strings. My approach is as below (there will be a loop to go through the blob):

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(FILE_CONTENTS,2000,1)) as mystring from doc where file_name='test.png'

The problem is that the converted string looks scrambled

I did not specify the char_set for converting, could that be the reason? If so, how can I know which one to use?

Thanks.

I assume you need to encode the image to a [Base64](https://en.wikipedia.org/wiki/Base64) string. In this case have a look at https://stackoverflow.com/questions/42085147/can-i-store-binary-string-in-clob-column/42086105#42086105 — Wernfried Domscheit, Mar 01 '19 at 06:38
Image data is binary, not character data, so why are you trying to convert it to text? — eaolson, Mar 03 '19 at 01:05
@WernfriedDomscheit Thanks, that is what exactly I want to do. I checked your answer in that post. However, I received an error from db indicating an error while declaring 'BlobLen', which is not really clear to me. I think you codes are absolutely correct. — snakemia, Mar 04 '19 at 01:24
@WernfriedDomscheit No worries, I fixed the problem, thank you so much. Would you please put your answer below so that I can accept? — snakemia, Mar 04 '19 at 01:57

score 0 · Accepted Answer · answered Mar 04 '19 at 06:58

Here is a function to convert a BLOB into a Base64 string:

FUNCTION EncodeBASE64(InBlob IN BLOB) RETURN CLOB IS

    BlobLen INTEGER := DBMS_LOB.GETLENGTH(InBlob);
    read_offset INTEGER := 1;

    amount INTEGER := 1440; -- must be a whole multiple of 3
    -- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters 
    buffer RAW(1440);
    res CLOB := EMPTY_CLOB();

BEGIN

    IF InBlob IS NULL OR NVL(BlobLen, 0) = 0 THEN 
        RETURN NULL;
    ELSIF BlobLen <= 24000 THEN
        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(InBlob));
    ELSE
        -- UTL_ENCODE.BASE64_ENCODE is limited to 32k, process in chunks if bigger
        LOOP
            EXIT WHEN read_offset >= BlobLen;
            DBMS_LOB.READ(InBlob, amount, read_offset, buffer);
            res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));       
            read_offset := read_offset + amount;
        END LOOP;
    END IF;
    RETURN res;

END EncodeBASE64;

convert blob(image) to varchar in oracle

1 Answers1