1.Introduction
In Oracle APEX, integrating drag-and-drop file uploads with automated compression and storage in the database enhances user experience and data management. By leveraging APEX’s file handling components and PL/SQL capabilities, you can streamline the process of uploading multiple files, compressing them into a ZIP archive, and storing this archive directly in the Oracle database. This approach simplifies file management, improves data organization, and ensures that files are securely stored and easily retrievable. Implementing this functionality involves configuring file upload items, using dynamic actions or processes for automation, and employing PL/SQL packages to handle file compression and storage..
The following technologies has been used to achieve the same.
- Oracle APEX
- JavaScript
- HTML & CSS
2.Why we need to do
A Document Management System enables users to drag and drop multiple documents into a single ZIP file for streamlined submission and archival. For bulk data import, users can upload several data files, compress them into a ZIP archive, and store the compressed file in the database for efficient batch processing. Report aggregation involves collecting various reports from different departments, zipping them into one file, and saving it in the database for easy access and sharing. File backup allows for regular uploads of application files or user-generated content, compressing them into a ZIP file, and storing it in the database for backup and recovery
3.How do we solve
Step1: Create a blank page and add a static region named FileWithin the Employee_Form region, create one page item and a button
- Logfilename File Upload
- Save Button
Step2: Create another region named DATABASE_REPORT and make the region as
Interactive Report
- MIMETYPE_P
- FILENAME_P
- IMG_LAST_UPD_P
- CALLNUMBER_P
- ATTACH_NUMBER_P
- GETLENGTH(LOG_FILE_NAME_P)
The Columns in the table where the converted zip file will be stored in the database
Step3: Create a process and make that process setup for the save button which would convert the
File into zip file and store in the database.
The Process Code is:
DECLARE
file_names_V apex_t_varchar2;
l_zip_file BLOB;
MimeType_V varchar2(250);
Filename_V varchar2(255);
Count_fiLes_V number;
Cursor Sel_Files(f_name_V varchar2) is
SELECT filename,mime_type,blob_content
FROM apex_application_temp_files
WHERE application_id = :APP_ID
and name = f_name_V;
file_V Sel_Files%ROWTYPE;
BEGIN
— Split the file names into an array
file_names_V := apex_string.split (
p_str => :P19_LOGFILENAME,
p_sep => ‘:’ );
–get the number of files
SELECT COUNT(*) INTO Count_Files_V
from apex_application_temp_files;
IF Count_Files_V >1 THEN
— Add files to the zip file
FOR i IN 1 .. file_names_V.count LOOP
— Get the file from the temporary files table
OPEN Sel_Files(file_names_V(i));
FETCH Sel_Files into file_V;
CLOSE Sel_Files;
— Add the file to the zip archive
apex_zip.add_file (
p_zipped_blob => l_zip_file,
p_file_name => file_V.filename,
p_content => file_V.BLOB_CONTENT
);
END LOOP;
— Finalize the zip file
apex_zip.finish (
p_zipped_blob => l_zip_file );
–after that it is up to you how u wanna insert the zip file in the database
— for me i created a function that update the table(columns related to the
— file)
— Callnumber_P VARCHAR2,
— Log_File_Name_P BLOB,
— Mimetype_P VARCHAR2,
— Filename_P VARCHAR2,
— Img_Last_Upd_P VARCHAR2,
— Attach_Number_P NUMBER
insert into RUC values
(:P19_CALLNUMBER,
l_zip_file,
‘application/zip’,
‘download.zip’,
SYSDATE,
1);
— Update the attachment in the database
/* Lgs_Calls_Pck.Upd_Attach(:P19_CALLNUMBER, l_zip_file, ‘application/zip’, ‘download.zip’, SYSDATE, 1);*/
ELSE
OPEN Sel_Files(file_names_V(1));
FETCH Sel_Files into file_V;
CLOSE Sel_Files;
/* Lgs_Calls_Pck.Upd_Attach(:P19_CALLNUMBER,file_V.BLOB_CONTENT,file_V.mime_type,file_V.filename, SYSDATE, 1);*/
insert into RUC values(
:P19_CALLNUMBER,
file_V.BLOB_CONTENT,
file_V.mime_type,
file_V.filename,
SYSDATE,
1);
END IF;
END;
Step6: Save & Run the application
4. Screenshots
Screen Shot: 1 upload the file to be converted to zip
Screen Shot: 2 A download document has been uploaded and click the save button
Screen Shot: 3 On Click of save the file will get converted and in the database it will get stored and the datas are shown in the form of report.
5. Conclusion
To upload files using drag-and-drop in Oracle APEX, start by creating a file browse item on your page configured for multiple uploads if needed. Implement a Dynamic Action with JavaScript to handle file selection and initiate the upload process. After files are uploaded, access them through the APEX_APPLICATION_TEMP_FILES view. Use PL/SQL to compress these files into a ZIP archive, leveraging packages like UTL_COMPRESS. Once compressed, save the ZIP file as a BLOB in a database table. Ensure robust testing to validate the upload, compression, and storage functionalities, providing a seamless solution for file management in Oracle APEX applications.
.
