title: Decode base64 image into BLOB with PL/SQL
body: <p>I'm using the script below in order to fetch JSON file from MongoDB, parse it and then insert it into Oracle table. </p>
  
  <ul>
  <li>The script works fine in a sense that it inserts all values correctly into Oracle table. That includes the value <em>Photo</em> which is an image of base64 formate and it is much larger than 32KB. </li>
  <li><p>The column <em>Photo</em> in the table <em>Appery_Photos</em> is of the type CLOB while column <em>DecodedPhoto</em> is of the type BLOB.</p></li>
  <li><p>The problem lies in the line <code>blobOriginal := base64decode1(Photo);</code> which I used to decode the CLOB into BLOB. The function <em>base64decode1</em> has been replaced with several functions (i.e. <a href="https://erikwramner.wordpress.com/2010/02/23/coding-and-decoding-base64-in-plsql/" rel="nofollow noreferrer">decode_base64</a> , <a href="http://wonderingmisha.blogspot.com/2011/05/base64-encoding-in-plsql.html" rel="nofollow noreferrer">base64DecodeClobAsBlob_plsql</a>, <a href="http://oracle-base.com/dba/script.php?category=miscellaneous&amp;file=base64decode.sql" rel="nofollow noreferrer">base64decode</a> , <a href="https://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle">from_base64</a> &amp; finally <a href="https://sourceforge.net/projects/pljson/" rel="nofollow noreferrer">JSON_EXT.DECODE</a>).</p></li>
  <li><p><strong>The result</strong> was the same for all of them. That is, the resultant BLOB object cannot be openned as an image in any of images editors (I'm using Oracle SQL Developer to download it). </p></li>
  <li><p>I checked CLOB, and I could not find any newlines <em>\n</em>, nor could I find any spaces (only + signs found). Furthermore, I inserted CLOB value into the <a href="http://www.askapache.com/online-tools/base64-image-converter/" rel="nofollow noreferrer">base64-image-converter</a> and it displays the image correctly. In addition, I tried to encode the resultant BLOB in base64 back in order to further validate (using the opposite functions provided in the links above), the resultant base64 is not the same at all.</p></li>
  </ul>
  
  <pre>
       BEGIN
        l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D' , 'GET' , 'HTTP/1.1');
        -- ...set header's attributes
        UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
        l_http_response := UTL_HTTP.get_response(l_http_request);
        BEGIN
          LOOP
            UTL_HTTP.read_text(l_http_response, buf);
            l_response_text := l_response_text || buf;
          END LOOP;
        EXCEPTION
        WHEN UTL_HTTP.end_of_body THEN
          NULL;
        END;
        l_list := json_list(l_response_text);
        FOR i IN 1..l_list.count
        LOOP
          A_id  := json_ext.get_string(json(l_list.get(i)),'_id');
          l_val := json_ext.get_json_value(json(l_list.get(i)),'Photo');
          dbms_lob.createtemporary(Photo, true, 2);
          json_value.get_string(l_val, Photo);
          dbms_output.put_line(dbms_lob.getlength(Photo));
          dbms_output.put_line(dbms_lob.substr(Photo, 20, 1));
          blobOriginal := base64decode1(Photo);
          A_Name       := json_ext.get_string(json(l_list.get(i)),'Name');
          Remarks      := json_ext.get_string(json(l_list.get(i)),'Remarks');
          Status       := json_ext.get_string(json(l_list.get(i)),'Status');
          UserId       := json_ext.get_string(json(l_list.get(i)),'UserId');
          A_Date       := json_ext.get_string(json(l_list.get(i)),'Date');
          A_Time       := json_ext.get_string(json(l_list.get(i)),'Time');
          MSG_status   := json_ext.get_string(json(l_list.get(i)),'MSG_status');
          Oracle_Flag  := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');
          acl          := json_ext.get_string(json(l_list.get(i)),'acl');
        INSERT
          INTO Appery_Photos
            (
              A_id,
              Photo,
              DecodedPhoto,
              A_Name,
              Remarks,
              Status,
              UserId,
              A_Date,
              A_Time,
              MSG_status ,
              Oracle_Flag,
             acl
            )
            VALUES
            (
              A_id,
              Photo,
              blobOriginal,
              A_Name,
              Remarks,
              Status,
              UserId,
              A_Date,
              A_Time,
              MSG_status ,
              Oracle_Flag,
              acl
            );
          dbms_lob.freetemporary(Photo);
        END LOOP;
        -- finalizing
        UTL_HTTP.end_response(l_http_response);
      EXCEPTION
      WHEN UTL_HTTP.end_of_body THEN
        UTL_HTTP.end_response(l_http_response);
      END;
  </pre>
  
  <p>Any help is deeply appreciated.</p>