My previous blog post was about HTML5 camera integration in Oracle Application Express. The article demonstrates how you can take a photo with your device’s camera and upload it to the database using AJAX. Daniel Hochleitner wrote an interesting comment on that article pointing out that it’s better to use a chunked file upload than uploading files as CLOB.
One thing that can be improved is to use a chunked file upload (30k chunks) to the application process instead of the complete CLOB. I did similar things with my Dropzone plugin and the CLOB way doesn´t work with the old OHS or web tier, because the Apache bundled with it can only handle parameters that are not bigger than 30k…ORDS is fine and does work out well!
You can see it for PL/SQL here:
https://github.com/Dani3lSun/apex-plugin-dropzone#save-to-db-using-plsqland for the client side Javascript code here:
https://github.com/Dani3lSun/apex-plugin-dropzone/blob/master/server/js/apexdropzone.js
Line 28-45 and line 189-207
I examined the source code of the Dropzone APEX plugin and decided to put together a little demo. The demo lets you upload multiple files with AJAX and of course in a chunked manner. You can give it a try here.
The demo is based on code taken from the Dropzone plugin, so all credits go to Daniel. Follow the below five steps to build your own chunked multi-file upload solution.
Or just use the Dropzone APEX plugin if you want to make it really easy for yourself…
1. You need a place to store the uploaded files
You can insert the uploaded files directly into a table, or you can use a temporary collection like I did in the demo. I initialize the UPLOADED_FILES collection in an After Header PL/SQL process.
declare lco_collection_name constant apex_collections.collection_name%type := 'UPLOADED_FILES'; begin if not apex_collection.collection_exists(lco_collection_name) then apex_collection.create_collection( p_collection_name => lco_collection_name ); end if; end;
2. Create a File Browse… page item
Create a File Browse… item on the page and allow it to select multiple files. You can accomplish this by putting multiple in the Custom Attributes page item property.
I also created a button which is responsible for initiating the process of uploading files to the server. Do not let the button submit the page – set its action to Defined by Dynamic Action.
3. JavaScript variables and functions
Put the following code in the Function and Global Variable Declaration page attribute.
var fileInputElem = document.getElementById('P130_FILE_UPLOAD'); var fileIndex = 0; // builds a js array from long string function clob2Array(clob, size, array) { loopCount = Math.floor(clob.length / size) + 1; for (var i = 0; i < loopCount; i++) { array.push(clob.slice(size * i, size * (i + 1))); } return array; } // converts binaryArray to base64 string function binaryArray2base64(int8Array) { var data = ""; var bytes = new Uint8Array(int8Array); var length = bytes.byteLength; for (var i = 0; i < length; i++) { data += String.fromCharCode(bytes[i]); } return btoa(data); } // a recursive function that calls itself to upload multiple files synchronously function uploadFile(pFileIndex) { var file = fileInputElem.files[pFileIndex]; var reader = new FileReader(); reader.onload = (function(pFile) { return function(e) { if (pFile) { var base64 = binaryArray2base64(e.target.result); var f01Array = []; f01Array = clob2Array(base64, 30000, f01Array); apex.server.process( 'UPLOAD_FILE', { x01: file.name, x02: file.type, f01: f01Array }, { dataType: 'json', success: function(data) { if (data.result == 'success') { fileIndex++; if (fileIndex < fileInputElem.files.length) { // start uploading the next file uploadFile(fileIndex); } else { // all files have been uploaded at this point fileInputElem.value = ''; $('#uploadedFilesRpt').trigger('apexrefresh'); } } else { alert('Oops! Something went terribly wrong. Please try again or contact your application administrator.'); } } } ); } } })(file); reader.readAsArrayBuffer(file); }
- Line 1: A global variable referencing the File Browse… page item. Replace P130_FILE_UPLOAD with the name of your own page item.
- Line 5 and 14: The clob2Array and binaryArray2base64 functions are two utility functions copied from the Dropzone plugin.
- Line 25: The uploadFile function gets executed for every file that has to be uploaded.
- Line 51: The uploadFile function is a recursive function, which means that it will call itself to start uploading the next file.
- Line 54: Clear the File Browse… item.
- Line 55: Refresh the report region to update the list of uploaded files. The report is based on the UPLOADED_FILES collection.
4. The UPLOAD_FILE AJAX callback process
The UPLOAD_FILE AJAX callback process gets called from the uploadFile JavaScript function. It builds a BLOB variable from the f01 array and adds a member to the UPLOADED_FILES collection.
declare lco_collection_name constant apex_collections.collection_name%type := 'UPLOADED_FILES'; l_blob blob; l_filename varchar2(200); l_mime_type varchar2(200); l_token varchar2(32000); begin l_filename := apex_application.g_x01; l_mime_type := nvl(apex_application.g_x02, 'application/octet-stream'); -- build BLOB from f01 30k array (base64 encoded) dbms_lob.createtemporary(l_blob, false, dbms_lob.session); for i in 1 .. apex_application.g_f01.count loop l_token := wwv_flow.g_f01(i); if length(l_token) > 0 then dbms_lob.append( dest_lob => l_blob, src_lob => to_blob(utl_encode.base64_decode(utl_raw.cast_to_raw(l_token))) ); end if; end loop; -- add collection member (only if BLOB is not null) if dbms_lob.getlength(l_blob) is not null then apex_collection.add_member( p_collection_name => lco_collection_name, p_c001 => l_filename, p_c002 => l_mime_type, p_blob001 => l_blob ); end if; apex_json.open_object; apex_json.write( p_name => 'result', p_value => 'success' ); apex_json.close_object; exception when others then apex_json.open_object; apex_json.write( p_name => 'result', p_value => 'fail' ); apex_json.close_object; end;
5. Create a dynamic action
Create a dynamic action that fires when you click the button from step #2. Put the following JavaScript expression as Condition. This will prevent the dynamic action from executing when the File Browse… item has no files selected.
fileInputElem.files.length != 0
The dynamic action should have one true action that Executes JavaScript Code.
fileIndex = 0; uploadFile(fileIndex);
Remember that the uploadFile function is recursive. After successfully uploading the first file, it will look for other files to be uploaded.
The demo application
The demo application can be downloaded here.
Nice work Nick and kudos to Daniel. Whilst this is an improved approach, it’s still subject to a default 2000 parameter limit which is about 60M of encoding which is about a 46M file.
If you want to upload a file in chunks using multiple AJAX calls with no size restriction (not 1 call as per your demo) you can actually use the slice method: https://w3c.github.io/FileAPI/#slice-method-algo
Kris Rice blogged about it here back in 2013: http://krisrice.blogspot.fi/2013/08/chunked-file-loading-with-apex-listener.html
So you can adapt your approach to use this design to handle larger files.
LikeLiked by 2 people
Hi, I am facing problem file browse column in manual tabular form….please let me know its possible …?
thanks in advance.
LikeLike
The APEX_ITEM package doesn’t include a function to generate a file browse item in a manual tabular form. You’ll have to create the HTML for a file browse item yourself. That’s not too hard, just take a look at the following page: https://www.w3.org/wiki/HTML/Elements/input/file
You can then follow a similar approach as described in the article. Keep in mind that you’ll need a unique ID for each row in order to link uploaded files with the row they belong to. So you’ll have to pass the row ID as a parameter to the AJAX callback process.
LikeLike
Thanks nick for the great post, would you please let me know how to generate HTML for a file browse item for a manual tabular form
Thanks,
Madhu
LikeLike
Hi Madhu,
Add a column to your tabular form query which generates a unique file input HTML element. An example:
Make sure to set the “Escape special characters” column attribute to “No”. Your tabular form should now include a file upload column.
Hope that helps,
Nick
LikeLike
Brilliant. Very helpful post Nick. Can you share the SQL of the report that queries the collection? I can get seq_id, c001, c002 and lengthb(blob001) but I’m not sure how to select the BLOB and have the download link show up. Thanks in advance for any advice.
LikeLike
Here’s the SQL query of the report:
LikeLike
thank you!
LikeLike
I noticed I was able to work while the file was uploading so I added a waitPopup. A bit clunky because it disappears between multiple files, maybe I just need to move it.
var pWait = apex.widget.waitPopup();
apex.server.process(
‘UPLOAD_FILE’,
{
x01: file.name,
x02: file.type,
f01: f01Array
},
{
dataType: ‘json’,
success: function(data) {
if (data.result == ‘success’) {
pWait.remove();
} else {
pWait.remove();
}
}
}
LikeLike
Stop the waitPopup in the else clause:
Initiate the waitPopup before executing “uploadFile” JavaScript function in your DA.
LikeLike
Is there a way to make it more portable so you can use it for multiple file browse objects? How do you get the files from the collection to a table? The dynamic action is asynchronous so adding a step to move files from the collection to the table will not work.
LikeLike
You can pass an extra parameter to the “uploadFile” JavaScript function to specify the corresponding file upload item. You would then be able to use the extra parameter value as a parameter to the “UPLOAD_FILE” PL/SQL process. Save that paramter value in a c00x collection column. That way, you can determine what files belong to each file upload item.
LikeLike
Hello,
Could we use this solution with APEX 4.2?
At first sight you use APIs like apex_json, but I’m not sure that you use anything else, which comes with APEX 5.x.
Thanks in advance!
Kind Regards,
Peter
LikeLike
Hi Peter,
I think you should be able to apply this solution in an APEX 4.2 environment. You’ll only need to replace the APEX_JSON package calls with HTP.P. Just give back the JSON response data as a string.
Nick
LikeLike