Oracle Apex: Manipulating a large text CKEDITOR static REGION via an interactive report, CLOBs and database processes.
Requirement
Because we needed to use an older version of CKEDITOR, it was necessary to load the text editor as a region rather than an item. Similar to what has been explained here:
But now the requirement was to inject text into the editor on demand. That injected text itself was on the database. The text would be selected from a report. The size of the region could easily be over 32k meaning that workarounds were required to overcome current (Apex 21 and below) limitations.
Solution
1. Create a report for selecting the text to inject.
2. Create a Javascript process for injecting the text, called by the report
3 Create an application process to store the current contents of the editor as a CLOB in a collection.
4. Create an application process for adding the injected text, returning a BLOB
Explanation.
1. Create a report for selecting the text to inject
My report is an interactive report displaying a description of the data. The CLOB is not presented, but the ID to the row is hidden. You will select the CLOB using this ID in step 4.
id
from .
..
Hide the interactive report header with some inline CSS
display:none;
}
The ID is a link column as a link attribute like this
onClick="$s('P42_TEMPLATE_ID',#ID#);saveTemplate();"
i.e Save the ID to a page item P42_TEMPLATE_ID, then call a Javascript procedure called saveTemplate.
Ultimately you could use a button or anything else that calls a Javascript. I needed a report because the user had a selection of different text to insert.
2. Create a Javascript process for injecting the text, called by the report
It is commented but the key thing here is how we handle the potentially large amount of data.
- Take the value from the editor and split it up into an array
- Pass the array to a application process to save to a CLOB collection
- Call another process to do some manipulation on that data (adding a template in my case), then pass it back, setting the editor value with the manipulated data.
//Pass the array to the application process to get saved to a collection
apex.server.process("SAVE_EMAIL", {
f01: pData
}, {
}).done(function(pData) {
//Once done, call the application process to add the template to the collection
var p = apex.server.process('ADD_TEMPLATE_TO_EMAIL',
{
pageItems: "#P42_TEMPLATE_ID"
}, {
dataType: 'html'
});
// When the process is done, set the value (passed back from the application process) to the editor
p.done(function(data) {
CKEDITOR.instances.editor1.setData(data);
});
});
};
//Called from report link
function saveTemplate(){
// This will substr the big string into an array on 10000 chars
var chunkData = CKEDITOR.instances.editor1.getData().match(new RegExp('.{1,' + 10000 + '}', 'g'));
saveEmail(chunkData);
};
3 Create an application process to store the current contents of the editor as a CLOB in a collection.
SAVE_EMAIL: This simply saves the array into a single collection member, passing back a dummy output object.
l_final_clob CLOB;
BEGIN
for i in 1..apex_application.g_f01.count LOOP
l_final_clob := l_final_clob || apex_application.g_f01(i) ;
END LOOP;
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'FACES_EMC_DRAFT_EMAIL');
APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'FACES_EMC_DRAFT_EMAIL'
, p_clob001 => l_final_clob);
-- Dummy JSON obj for output
apex_json.open_object;
apex_json.close_object;
END;
4. Create an application process for adding the injected text, returning a BLOB
ADD_TEMPLATE_TO_EMAIL appends another clob stored in a table, to the top of the text that you've just saved in your collection. The name of your table "report table" will be from the interactive report where you initially called all of this code from. (Where you passed the ID from).
One you have your new text as you want it, all standard code to return the large object back to Javascript which in turns sets it to the editor.
l_clob CLOB;
l_file BLOB;
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_ctx PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
l_csid PLS_INTEGER := NLS_CHARSET_ID('UTF8');
l_blob_warn PLS_INTEGER;
v_template clob;
v_in_progress_email clob;
BEGIN
BEGIN
dbms_lob.createtemporary(l_clob,false,dbms_lob.call);
dbms_lob.createtemporary(v_template,false,dbms_lob.call);
dbms_lob.createtemporary(v_in_progress_email,false,dbms_lob.call);
-- Get the template
select content_clob
into v_template
from (report table)
where id = :P42_TEMPLATE_ID; --This was stored in a previous step.
--get the email
select clob001
into v_in_progress_email
from apex_collections
where collection_name = 'FACES_EMC_DRAFT_EMAIL';
DBMS_LOB.APPEND(l_clob, v_template);
DBMS_LOB.APPEND(l_clob, to_clob('<br>'));
DBMS_LOB.APPEND(l_clob, v_in_progress_email);
-- Exception handling when no data were found for the ID (you are creating new row
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_clob := EMPTY_CLOB();
END;
-- If we don't have a CLOB, just return empty content
IF (DBMS_LOB.GETLENGTH(l_clob) = 0) THEN
HTP.P('');
ELSE
-- Create BLOB from CLOB
DBMS_LOB.CREATETEMPORARY( lob_loc => l_file
, cache => true
, dur => DBMS_LOB.CALL);
DBMS_LOB.CONVERTTOBLOB(l_file, l_clob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset, l_csid, l_lang_ctx, l_blob_warn);
-- Download BLOB
OWA_UTIL.MIME_HEADER('text/html', false);
HTP.P('Content-Length: ' || dbms_lob.getlength(l_file));
HTP.P('Content-Disposition: attachment; filename="content.html"');
OWA_UTIL.HTTP_HEADER_CLOSE();
WPG_DOCLOAD.DOWNLOAD_FILE(l_file);
END IF;
END;
Acknowledgement
How to pass CLOB in apex.server.process? - Oracle Forums
CLOB in APEX Rich Text Editor – APEX Corner (stinolez.com)
Getting and Saving Data - CKEditor 4 Documentation
Page items and Javascript: More than $s and $v ... (oracle.com)
How to run a Dynamic Action when clicking on a link in a report - Oracle Forums
Comments
Post a Comment