It is common for Oracle APEX developers to need to handle files. This blog post will show you how to preview and download files stored in a database or a local directory. The most common approach in Oracle APEX is to store files directly in the database, typically within a BLOB (Binary Large Object) column. The primary reason is that this method simplifies data management and backup.
Sometimes, you may need to handle files stored directly on the server's file system rather than in the database. This is common for large files or when files are managed by other systems.
We will cover the basic steps and code snippets required for each method. Before approaching this tutorial, you must need to cover (preview image in large scale and save file into database/local directory) these two tutorial.
Step 1: Create a blank page and then create 3 regions. Set these three regions properties as like below-
Region-1:
Name: Tab Container
Template: Tabs Container
Region- 2:
Name: From Database
Type: Interactive Report
Source:
Location: Local Database
Type: SQL Query
SQL Query:
SELECT employee_id
, first_name||' '||last_name AS emp_name
, job_id
, department_id
, salary
, phone_number
, hire_date
, CASE file_format WHEN 'PDF' THEN
'<div class="rpt-container rpt-img" style="background-image: url(#APP_FILES#pdf.png)">'
WHEN 'IMAGE' THEN
'<div class="rpt-container rpt-img"><img style="width:100px; height:80px" src="'||apex_util.get_blob_file_src('P15_EMP_PHOTO', employee_id)||'">'
END ||'
<div class="download">
<div id="'||employee_id||'" style="margin-right:5px" onclick="downloadFile(this)"><span class="download-img fa fa-download"></span></div>
<div id="'||employee_id||'" style="margin-left:5px" onclick="previewFile(this)"><span class="download-img fa fa-eye-slash"></span></div>
</div>
</div>' AS file_handle
FROM employees
WHERE storage_type = 'S2T'
Parent Region: Tab Container
Slot: Tabs
Region- 3:
Name: Local Directory
Type: Interactive Report
Source:
Location: Local Database
Type: SQL Query
SQL Query:
SELECT employee_id
, first_name||' '||last_name AS emp_name
, job_id
, department_id
, salary
, phone_number
, hire_date
, CASE file_format WHEN 'PDF' THEN
'<div class="rpt-container rpt-img" style="background-image: url(#APP_FILES#pdf.png)">'
WHEN 'IMAGE' THEN
'<div class="rpt-container rpt-img"><img style="width:100px; height:80px" src="'http://localhost:9090/i/IMAGE_DIR/'||employee_id||'.'||file_type||">'
END ||'
<div class="download">
<div id="'||employee_id||'" style="margin-right:5px" onclick="downloadFile(this)"><span class="download-img fa fa-download"></span></div>
<div id="'||employee_id||'" style="margin-left:5px" onclick="previewFile(this)"><span class="download-img fa fa-eye-slash"></span></div>
</div>
</div>' AS file_handle
FROM employees
WHERE storage_type = 'S2D'
Parent Region: Tab Container
Slot: Tabs
Note: Replace local directory using you own files path.
Step 2: Create Ajax Callback Process to prepare the files for preview & download.
Name: GET_FILETYPE
Location: Local Database
PL/SQL Code:
BEGIN
apex_json.open_array;
apex_json.open_object;
FOR i IN (
SELECT file_format
, storage_type
, file_type
, CASE storage_type WHEN 'S2T' THEN
apex_web_service.blob2clobbase64(emp_photo)
END file_src
FROM employees
WHERE employee_id = apex_application.g_x01
) LOOP
apex_json.write('FILE_FORMAT', i.file_format);
apex_json.write('STORAGE_TYPE', i.storage_type);
apex_json.write('FILE_TYPE', i.file_type);
apex_json.write('FILE_SRC', i.file_src);
END LOOP;
apex_json.close_object;
apex_json.close_all;
END;
Step 3: Create Javascript function in page level 'Function and Global Variable Declaration' section to preview and download files from database & local directory-
Open code editor and paste the below code-
function previewFile(file) {
apex.message.clearErrors();
var empId = file.id;
apex.server.process(
'GET_FILETYPE'
, {
x01: empId
}
, {
dataType: 'json',
success: function(pData) {
var div = document.createElement('div');
div.setAttribute('id', 'image-display');
var spn = document.createElement('span');
spn.innerText = 'x';
var src;
if (pData[0].FILE_FORMAT === 'IMAGE') {
var img = document.createElement('img');
var srcFile = file.closest('.download').previousElementSibling;
src = srcFile.getAttribute('src');
img.setAttribute('src', src);
div.appendChild(img);
} else {
var iFrame = document.createElement('iframe');
if (pData[0].STORAGE_TYPE === 'S2D') {
src = 'http://localhost:9090/i/IMAGE_DIR/'+empId+'.'+pData[0].FILE_TYPE;
} else {
src = 'data:application/pdf;base64,'+pData[0].FILE_SRC ;
}
iFrame.setAttribute('src', src);
iFrame.setAttribute('height', '600px');
iFrame.setAttribute('width', '100%');
div.appendChild(iFrame);
}
div.appendChild(spn);
document.body.appendChild(div);
document.querySelector('#image-display').style.display = 'block';
document.querySelector('#image-display span').onclick = () => {
document.querySelector('#image-display').remove();
}
}
}
)
};
function downloadFile(file) {
apex.message.clearErrors();
var empId = file.id;
apex.server.process(
'GET_FILETYPE'
, {
x01: empId
}
, {
dataType: 'json',
success: function(pData) {
var link = document.createElement('a');
if (pData[0].STORAGE_TYPE === 'S2D') {
link.href = 'http://localhost:9090/i/IMAGE_DIR/'+empId+'.'+pData[0].FILE_TYPE;
} else {
if (pData[0].FILE_FORMAT === 'IMAGE') {
link.href = 'data:image/'+pData[0].FILE_TYPE+';base64,'+pData[0].FILE_SRC;
} else {
link.href = 'data:application/'+pData[0].FILE_TYPE+';base64,'+pData[0].FILE_SRC;
}
}
link.download = empId+'.'+pData[0].FILE_TYPE;
link.click();
}
}
)
}
Conclusion:
Both methods have their pros and cons. The database approach is generally recommended for its simplicity and robustness, but there is a little bit limitation of previewing large pdf file in this way while the local directory approach is better suited for specific use cases, such as handling very large files or integrating with other file-based systems. By understanding these techniques, you can choose the best method for your specific application requirements.
Happy developing!
Very insightful content. Thanks for sharing
ReplyDelete