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); line 24 , 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 using any of image 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 as the original one 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.