I am currently creating an interface in APEX (ver 22.2.4) for users to be able to update and create payables invoices and import them into Oracle Fusion. The problem I am having is the REST API to create the attachment in Oracle is failing.
I can create the attachment for a payables invoice using Advanced Rest Client successfully with the same url and json that is being used in APEX, but in APEX I get an error “Unable to parse the provided payload ”
POST url https://epix-test.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/invoices/2672218/child/attachments
JSON: {"FileName": "WillowCreek.pdf", "Title": "WillowCreek.pdf","FileContents": “base64encodedtext here”}
I am using a BLOB column in the database that has the contents of the pdf file that is used by the below code to create the json text. When I view the base64 text using sql workshop I can't see it all in APEX because it is too big, but I stripped off the last 20 characters I can see that it is complete and matches to the same base64encoded text when I use the base64.guru to review the text. Everything appears to be setup correctly and mirrors what is done in Advanced REST Client but will not work from APEX. Any idea why? Is there a limitation on the size of the json? Is there something wrong with the BLOB_TO_BASE64 Function(below)? Any help would be greatly appreciated.
SET POSTATTACHJSON = '{'||'"FileName"'||' : '||'"'||filename ||'" ,'||
'"Title"' ||' : '||'"'||filename ||'" ,'||
'"FileContents"'||' : '||'"'|| BLOB_TO_BASE64(invoicepdf) ||'"' ||'}'
create or replace FUNCTION BLOB_TO_BASE64(
P_BLOB BLOB
)
RETURN CLOB
IS
V_CLOB CLOB;
V_CHUNK_SIZE PLS_INTEGER := 23826 ;-- 24000;
BEGIN
FOR V_I IN 0..TRUNC((DBMS_LOB.GETLENGTH(P_BLOB) - 1 ) / V_CHUNK_SIZE) LOOP
V_CLOB := V_CLOB || UTL_RAW.CAST_TO_VARCHAR2(
UTL_ENCODE.BASE64_ENCODE(
DBMS_LOB.SUBSTR(
P_BLOB,
V_CHUNK_SIZE,
V_I * V_CHUNK_SIZE + 1
)
)
);
END LOOP;
RETURN V_CLOB;
END;
