decoding clob in oracle and then encoding this blob in classic asp does not give the same clob

Question

I have two column in a table whose datatype are clob and blob . I insert a clob data in clob column and insert this data to blob data by decoding this clob in oracle by the following code :

function decode_base64(p_clob_in in clob) return blob is
    v_blob blob;
    v_result blob;
    v_offset integer;
    v_buffer_size binary_integer := 48;
    v_buffer_varchar varchar2(48);
    v_buffer_raw raw(48);
  begin
    if p_clob_in is null then
      return null;
    end if;
    dbms_lob.createtemporary(v_blob, true);
    v_offset := 1;
    for i in 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size) loop
      dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
      v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
      v_buffer_raw := utl_encode.base64_decode(v_buffer_raw);
      dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
      v_offset := v_offset + v_buffer_size;
    end loop;
    v_result := v_blob;
    dbms_lob.freetemporary(v_blob);
    return v_result;
  end decode_base64;

Then I get this blob data in asp.net by the following code :

strSQL ="SELECT BIO_DATA FingerData ,  DATA_LENGTH len_of_data , SERIAL_NO sl_no FROM FP_BIOMETRIC_DATA WHERE   CUST_NO =" & trim(Request("name")) & "  "
        Set objExec = Conn.Execute(strSQL)
fingerData1 = objExec("FingerData")

Then I am encoding this data into base64 by the following code :

Function Base64Encode(sText)
        Dim oXML, oNode

        Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
        Set oNode = oXML.CreateElement("base64")
        oNode.dataType = "bin.base64"
        oNode.nodeTypedValue =sText
        Base64Encode = oNode.text
        Set oNode = Nothing
        Set oXML = Nothing
    End Function

Then I am trying to compare this data and clob data in oracle database by this website . This website tells that the two data are different . Why ? Where is the error ? How can I get blob data by decoding a clob data in oracle ?

How do you call this function? Your select does not call it. How do you insert `BLOB` data into text? How do you copy binary data to `textdiff.com` page? Can you provide some sample data? — Wernfried Domscheit, Dec 21 '16 at 07:09
And what do you expect? For sure this is not a BASE64 encoded XML file. Try one of these online converters: http://www.motobit.com/util/base64-decoder-encoder.asp or http://base64decode.net/base64-image-decoder for testing — Wernfried Domscheit, Dec 21 '16 at 08:33
I want to get blob by decoding this clob data in oracle . Then I want to encode this blob data and want to get clob data in classic asp . My requirement is initially the clob data and and the clob data in classic asp should be same . — Christopher Marlowe, Dec 21 '16 at 08:54
The raw data you provided looks for me like rubbish when I use one of the online converters. Do you get proper binary data when you test with your raw data? — Wernfried Domscheit, Dec 21 '16 at 08:59
The data givn by me is clob data .It is not binary data nor xml data . I want to decode this in oracle and then encode the the blob in classic asp . — Christopher Marlowe, Dec 21 '16 at 09:03
Sorry your requirements are still not clear, I cannot help you in this case. — Wernfried Domscheit, Dec 21 '16 at 09:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131134/discussion-between-sagor-ahmed-and-wernfried-domscheit). — Christopher Marlowe, Dec 21 '16 at 09:24

score 1 · Accepted Answer · answered Dec 20 '16 at 12:22

I think the problem is at this line

dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);

v_buffer_size is fixed at 48 characters, however your BASE64 string may contain NEW_LINE characters which are ignored for decoding but they are counted for v_buffer_size.

You have to remove all NEW_LINE characters before you read your buffer or increase value of v_buffer_size by number of NEW_LINE characters in substring.

Try this one:

CREATE OR REPLACE FUNCTION DecodeBASE64(InBase64Char IN OUT NOCOPY CLOB) RETURN BLOB IS

    res BLOB;
    clob_trim CLOB;

    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    read_offset INTEGER := 1;
    ClobLen INTEGER;

    amount INTEGER := 1440; -- must be a whole multiple of 4
    buffer RAW(1440);
    stringBuffer VARCHAR2(1440);

BEGIN
    IF DBMS_LOB.GETLENGTH(InBase64Char) IS NULL THEN 
        RETURN NULL;
    END IF;

    -- Remove all NEW_LINE from base64 string
    ClobLen := DBMS_LOB.GETLENGTH(InBase64Char);
    DBMS_LOB.CREATETEMPORARY(clob_trim, TRUE);
    LOOP
        EXIT WHEN read_offset > ClobLen;
        stringBuffer := REPLACE(REPLACE(DBMS_LOB.SUBSTR(InBase64Char, amount, read_offset), CHR(13), NULL), CHR(10), NULL);
        DBMS_LOB.WRITEAPPEND(clob_trim, LENGTH(stringBuffer), stringBuffer);
        read_offset := read_offset + amount;
    END LOOP;

    read_offset := 1;
    ClobLen := DBMS_LOB.GETLENGTH(clob_trim);
    DBMS_LOB.CREATETEMPORARY(res, TRUE);
    LOOP
        EXIT WHEN read_offset > ClobLen;
        buffer := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(clob_trim, amount, read_offset)));
        DBMS_LOB.WRITEAPPEND(res, DBMS_LOB.GETLENGTH(buffer), buffer);
        read_offset := read_offset + amount;
    END LOOP;

    RETURN res;    

END DecodeBASE64;

I have applied this function . But the problem is same as before . Could you rewrite the function ? — Christopher Marlowe, Dec 21 '16 at 04:18
I derived it from here: http://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle/3806265#40852152 Only difference is it returns a `CLOB` but I don't see a reason why is should not work. — Wernfried Domscheit, Dec 21 '16 at 07:10

decoding clob in oracle and then encoding this blob in classic asp does not give the same clob

1 Answers1