I am trying to send recorded audio from the web browser to Oracle Apex but a problem is happening when the audio is quite long. The code works very well when the audio is less than two minutes.
What I know, the data is sent by URL, therefore it is being sent in text format. Oracle has a 32k limit for the string, so if the blob exceeds that limit, it must be sent in an array divided into parts of 30k each. So I am suspecting that the array is not being sent in the correct format, but I don't know how to confirm it.
The code I am using is as follows: (I built a plugin for Apex to send the audio)
// builds a js array from long string
clob2Array: function(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 DataURI to base64 string
dataURI2base64: function(dataURI) {
var base64 = dataURI.substr(dataURI.indexOf(',') + 1);
return base64;
},
blobToDataURL: function(blob, callback) {
var a = new FileReader();
a.onload = function(e) {callback(e.target.result);}
a.readAsDataURL(blob);
},
// save to DB function
save2Db: function(pAjaxIdentifier, pRegionId, pAudio, callback) {
apexAudio.blobToDataURL(pAudio, function(data){
// audio DataURI to base64
var base64 = apexAudio.dataURI2base64(data);
// split base64 clob string to f01 array length 30k
var f01Array = new Array();
f01Array = apexAudio.clob2Array(base64, 30000, f01Array);
// Apex Ajax Call
apex.server.plugin(pAjaxIdentifier, {
f01: f01Array,
}, {
dataType: 'html',
// SUCESS function
success: function() {
// add apex event
$('#' + pRegionId).trigger('apexaudio-saved-db');
// callback
callback();
},
// ERROR function
error: function(xhr, pMessage) {
// add apex event
$('#' + pRegionId).trigger('apexaudio-error-db');
console.log('save2Db: apex.server.plugin ERROR:', pMessage);
// callback
callback();
}
});
});
}
DECLARE
--
l_collection_name VARCHAR2(100);
l_blob BLOB;
l_filename VARCHAR2(100);
l_mime_type VARCHAR2(100);
l_token VARCHAR2(32000);
--
BEGIN
-- get defaults
l_filename := 'audio_' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') || '.webm';
l_mime_type := 'audio/webm';
-- build BLOB from f01 30k Array
dbms_lob.createtemporary(l_blob,
TRUE,
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(l_blob
,to_blob(utl_encode.base64_decode(utl_raw.cast_to_raw(l_token))));
END IF;
END LOOP;
l_collection_name := 'APEX_AUDIO';
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
p_collection_name => l_collection_name);
-- add collection member (only if BLOB not null)
IF dbms_lob.getlength(l_blob) IS NOT NULL THEN
apex_collection.add_member(p_collection_name => l_collection_name,
p_c001 => l_filename, -- filename
p_c002 => l_mime_type, -- mime_type
p_d001 => SYSDATE, -- date created
p_blob001 => l_blob); -- BLOB audio content
END IF;
END;
I repeat, the code works perfectly if the audio is short, but if it is long, the following error arises:
2020-02-20T20:09:27.169Z SEVERE <P-fvMwI2WpKybDySZRumRQ> java.sql.SQLException: ORA-06550: line 2, column 2:
PLS-00306: number or wrong type arguments when calling 'AJAX'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
InternalServerException [statusCode=500, reasons=[]]
at oracle.dbtools.apex.ModApexContext.handleError(ModApexContext.java:288)
at oracle.dbtools.apex.OWA.execute(OWA.java:206)
at oracle.dbtools.apex.ModApex.handleRequest(ModApex.java:310)
at oracle.dbtools.apex.ModApex.doPost(ModApex.java:188)
at oracle.dbtools.apex.ModApex.service(ModApex.java:112)
at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:126)
[...]
Tecnology:
Thus you are sending your request using AJAX with content type "application/x-www-form-urlencoded" Tomcat is limiting the max allowed POST size (defaults to 2MB). To make it work in APEX you have possibly 2 ways
1) Do some kind of double chunked upload, so first you split the file itself with e.g. file.slice() and then you build the 30k base64 array of each file chunk and upload this, chunk by chunk
2) Use a "multipart/form-data" content type with an form submit, thus here you are not running in Tomcats limitation of 2MB.
I built a file uploader plugin some time ago, just have a look at this function:
The uploaded file will then be inserted into apex_application_files automatically, from there you can grab it:
I would not recommend doing it with ORDS when you can do it inside of your APEX app, thus you have to deal with security, additional authentication etc...