1. Overview
This document explains about how to fetch response and save multiple blob contents into database table using JavaScript API & Promises and AJAX callback from server in Oracle APEX.
2. Technologies and Tools Used
The following technologies has been used to achieve the same.
- Oracle APEX
- SQL
- JavaScript
- AJAX Callback
3.Use Case
To automate response fetching and save blob contents directly into database table. Easy retrieval and management of documents through Oracle APEX. All documents are stored in a single, secure location.
4.Architecture
Step 1: Here I have used Interactive grid primary key values as colon separated string and saved it in hidden page item for iteration.
Dynamic Action For Interactive grid selection change, to populate hidden page item
Event : Selection change
Selection type : Region
Region : Grid region—User defined
Action : Execute JavaScript code
Code:
var i, i_id=”,
model = this.data.model;
for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
i_id += model.getValue( this.data.selectedRecords[i], “ID”) + “:”;
}
apex.item( “P18_GRID_ID” ).setValue(i_id);
Step 2: Copy the below code in the Javascript Function and global variable declaration section.
async function getData(id){
const pdfrespose=await generatePdf(id).then((responses) => {
console.log(‘tested ok’);
apex.message.showPageSuccess( “PDFs saved!” );
}) .catch((error) => {
console.error(‘Error fetching data:’, error);
});}
async function generatePdf(id) {
var varr = theme.split(/[:]/);
try {
const results = await Promise.all(
varr.map(async (arr) => {
const blob = await fetch(‘https://hostname:port:report?¶meter1=’ + arr)
.then((response) => {
if (!response.ok) {
throw new Error(‘Network response was not ok’);
}
return response.blob();
});
const reader = new FileReader();
reader.readAsDataURL(blob);
reader.onload =async function(event)
{const base64Data= event.target.result;
const response = await apex.server.process(“insert_blob”, {
x01: base64Data,
x02: ‘test’,
x03: arr,
x04: ‘DIS’
}, {
datatype: “json”,
success:function(data){
console.log(data.success);}
});
return response;}
})
);
return results;
} catch (error) {
console.error(‘Error generating PDFs:’, error);
throw error;
}
};
Note: In the above code Fetch API will get the response from the server only when the instance is mapped with SSL certification(TLS) i.e it must have https protocol. Otherwise we need to whitelist the instance to avoid CORS error.
Step 3:Create AJAX callback process for inserting blob contents into database table.
Code:
DECLARE
FLAG VARCHAR2(1000);
l_base64 CLOB;
l_ddl clob;
v_count number;
l_blob blob;
BEGIN
l_base64:=APEX_APPLICATION.g_x01;
l_blob:=UTL_ENCODE.base64_decode(l_base64);
select count(*) into v_count from PDF_RESPONSE where file_id=APEX_APPLICATION.g_x03 AND src_type=APEX_APPLICATION.g_x04 ;
if v_count=0 then
INSERT INTO PDF_RESPONSE(FILE_CONTENT,file_name,file_id,mimetype,src_type,bs_id)
VALUES(l_blob,APEX_APPLICATION.g_x02||’.pdf’,APEX_APPLICATION.g_x03,’application/pdf’,APEX_APPLICATION.g_x04);
elsif v_count>0 then
update PDF_RESPONSE set FILE_CONTENT=l_blob,
file_name=APEX_APPLICATION.g_x02||’.pdf’,
mimetype=’application/pdf’
where file_id=APEX_APPLICATION.g_x03 and src_type=APEX_APPLICATION.g_x04;
end if;
apex_json.open_object;
apex_json.write(
p_name =>’success’,
p_value => ‘y’
);
apex_json.close_object;
exception
when others then
apex_json.open_object;
apex_json.write(
p_name =>’failed’,
p_value => ‘n’
);
apex_json.close_object;
END;
Step 4: Create new button and dynamic action for on click event to call the javascript function.
5.Conclusion
This is all about how to fetch response and save multiple blob contents into database table using JavaScript API & Promises in Oracle APEX,which helps to automate response fetching and save blob contents directly into database table. Easy retrieval and management of documents through Oracle APEX. All documents are stored in a single, secure location.
6.Screenshots
