Decode base64 image into BLOB with PL/SQL

Question

I'm using the script below in order to fetch JSON file from MongoDB, parse it and then insert it into Oracle table.

• The script works fine in a sense that it inserts all values correctly into Oracle table. That includes the value Photo which is an image of base64 formate and it is much larger than 32KB.

• The column Photo in the table Appery_Photos is of the type CLOB while column DecodedPhoto is of the type BLOB.

• The problem lies in the line blobOriginal := base64decode1(Photo); which I used to decode the CLOB into BLOB. The function base64decode1 has been replaced with several functions (i.e. decode_base64 , base64DecodeClobAsBlob_plsql, base64decode , from_base64 & finally JSON_EXT.DECODE).

• The result 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).

• I checked CLOB, and I could not find any newlines \n, nor could I find any spaces (only + signs found). Furthermore, I inserted CLOB value into the base64-image-converter 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.

     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;

Any help is deeply appreciated.

Answer 1

It is Base64 or HexBinary...

This works for HexBinary

function DESERIALIZE_HEX_BLOB(P_SERIALIZATION CLOB)
return BLOB
is
  V_BLOB BLOB;
  V_OFFSET INTEGER := 1;
  V_AMOUNT INTEGER := 32000;
  V_INPUT_LENGTH NUMBER := DBMS_LOB.GETLENGTH(P_SERIALIZATION);
  V_HEXBINARY_DATA VARCHAR2(32000);
begin
  if (P_SERIALIZATION is NULL) then return NULL; end if;
  DBMS_LOB.CREATETEMPORARY(V_BLOB,TRUE,DBMS_LOB.CALL);
  while (V_OFFSET <= V_INPUT_LENGTH) loop
    V_AMOUNT := 32000;
    DBMS_LOB.READ(P_SERIALIZATION,V_AMOUNT,V_OFFSET,V_HEXBINARY_DATA);
    V_OFFSET := V_OFFSET + V_AMOUNT;
    DBMS_LOB.APPEND(V_BLOB,TO_BLOB(HEXTORAW(V_HEXBINARY_DATA)));
  end loop;
  return V_BLOB;
end;
--

And could probably be modified to handle Base64 without too much trouble.

Answer 2

I found that is not in the function I used in the base64 decoding. Instead, the value I have is not base64 encoded strings, but base64 encode dataURi's, something like

data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAA

So I have to use something like: clobbase642blob( substr( Photo, instr( Photo, ',' ) + 1 ) )

The following script is inspired by Oracle Community answer

DECLARE
  l_param_list VARCHAR2(512);
  l_http_request UTL_HTTP.req;
  l_http_response UTL_HTTP.resp;
  l_response_text CLOB;
  --l_response_text  VARCHAR2(32767);
  buf VARCHAR2(32767);
  l_list json_list;
  l_val json_value;
  A_id VARCHAR2(100);
  Photo CLOB;
  A_Name      VARCHAR2(100);
  Remarks     VARCHAR2(100);
  Status      VARCHAR2(100);
  UserId      VARCHAR2(100);
  A_Date      VARCHAR2(100);
  A_Time      VARCHAR2(100);
  MSG_status  VARCHAR2(100);
  Oracle_Flag VARCHAR2(100);
  acl         VARCHAR2(100);
  obj json_list;
  blobOriginal BLOB := empty_blob();
  clobInBase64 CLOB;
  substring VARCHAR2(2000);
  tmp BLOB;
  n pls_integer                := 0;
  substring_length pls_integer := 2000;
  ------------------------------------------------------
  FUNCTION clobbase642blob(
      p_clob CLOB )
    RETURN BLOB
  IS
    t_blob BLOB;
    t_buffer VARCHAR2(32767);
    t_pos    NUMBER := 1;
    t_size   NUMBER := nls_charset_decl_len( 32764, nls_charset_id( 'char_cs' ) );
    t_len    NUMBER;
    t_tmp raw(32767);
  BEGIN
    dbms_lob.createtemporary( t_blob, true );
    t_len := LENGTH( p_clob );
    LOOP
      EXIT
    WHEN t_pos     > t_len;
      t_buffer    := REPLACE( REPLACE( SUBSTR( p_clob, t_pos, t_size ), chr(10) ), chr(13) );
      t_pos       := t_pos + t_size;
      WHILE t_pos  0
      LOOP
        t_buffer := t_buffer || REPLACE( REPLACE( SUBSTR( p_clob, t_pos, 1 ), chr(10) ), chr(13) );
        t_pos    := t_pos + 1;
      END LOOP;
      t_tmp := utl_encode.base64_decode( utl_raw.cast_to_raw( t_buffer ) );
      dbms_lob.writeappend( t_blob, utl_raw.length( t_tmp ), t_tmp );
    END LOOP;
    RETURN t_blob;
  END;
  ------------------------------------------------------
  BEGIN
    -- service's input parameters
    -- preparing Request...
    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');
      --deal with base64 URI photo >32KB
      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 := clobbase642blob( SUBSTR( Photo, 24 ) );
      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;
  /