Unable to Transfer BLOB content from Oracle APEX to another DB using ORDS REST API

Question

There is requirement that when user select image/document using 'File Browse' item in Oracle APEX, this image/document needs to be transferred to remote DB via ORDS REST API. ORDS POST API is created on remove DB which takes parameter like content, type etc. and insert details to DB. It is working find but somehow BLOB (image/document) is getting corrupted in remote DB. Below is item details created in Oracle APEX Page -

Image/document uploaded using this item goes to APEX temp table (apex_application_temp_files). Now when user click on 'Upload' following JavaScript gets executed -

//alert('here');
var base64Image = "";
var fileName = $v('P28_NEW');

var getImageDetails = async function() {
    //alert('inside get image details func');
    return apex.server.process(
        "GET_IMAGE_DETAILS", {
            x01: fileName
        }, {
            async: false,
            dataType: "json",
            success: function(pData) {
                console.log('success');
                console.log('requestBody:'+JSON.stringify(pData));
            },
            error: function(request, status, error) {
                alert("Error1:" + error);
            },
        }
    );
};


async function mainFunction() {
    try {
        var callFunction1 = await getImageDetails();

        var apiUrl = '...../ords/..../..../image/api?TITLE=test&FILENAME=test&SUBMITTED_BY=ssss&MIMETYPE=application/pdf';
        var imageContent = callFunction1.image;
        var imageType = callFunction1.mimetype; 
        //console.log('requestBody:'+JSON.stringify(callFunction1));
       
        fetch(apiUrl, {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: imageContent
            })
            .then(function(response) {
                if (response.ok) {
                    console.log('successful');
                } else {
                    console.error('failed');
                }
            })
            .catch(function(error) {
                console.error('Error:', error);
            });

    } catch (e) {
        console.error(e);
    }
}

mainFunction();

This JavaScript function calls one AJAX process to get content of uploaded image/document -

DECLARE
  l_image       CLOB;
  l_mime_type   VARCHAR2 (255);
  l_imag1e varchar2(500):=  APEX_APPLICATION.g_x01;
BEGIN
  SELECT blob_content, mime_type
                  INTO l_image, l_mime_type
                  FROM apex_application_temp_files
                 WHERE name = l_imag1e; 
    
   -- Return the values using apex_json package
    apex_json.open_object;
    apex_json.write('image', l_image);
    apex_json.write('mimetype', l_mime_type);
    apex_json.close_object; 
    exception   
     when others then
        apex_json.open_object; 
        apex_json.write('message', sqlerrm);
        apex_json.close_object; 
        --htp.p(sqlerrm);
END;

ORDS REST API which is created on remote DB. It extracts content using ':body' parameters.

Answer 1

In the PL/SQL code block you take the column blob_content and put it into a l_image, a clob variable.

Have you tried, converting the blob_content via apex_web_service.blob2clobbase64?

DECLARE
  l_image       CLOB;
  l_mime_type   VARCHAR2 (255);
  l_imag1e varchar2(500):=  APEX_APPLICATION.g_x01;
BEGIN
  SELECT apex_web_service.blob2clobbase64(blob_content), mime_type
                  INTO l_image, l_mime_type
                  FROM apex_application_temp_files
                 WHERE name = l_imag1e; 
    
   -- Return the values using apex_json package
    apex_json.open_object;
    apex_json.write('image', l_image);
    apex_json.write('mimetype', l_mime_type);
    apex_json.close_object; 
    exception   
     when others then
        apex_json.open_object; 
        apex_json.write('message', sqlerrm);
        apex_json.close_object; 
        --htp.p(sqlerrm);
END;

Of course, you'll need to convert it back to blob in the target DB, for example with APEX_WEB_SERVICE.CLOBBASE642BLOB.