Requirement
Edit HTML text using a rich text editor, the data is stored as a CLOB.
Acknowledgement
Acknowldgement first as this a direct copy of a post which worked almost perfectly first time. I've shown a few more pictures on where the various processes sit in the editor.
I will not supply as many words exaining how and why (such as 32k buffers, waiting for editors), just the steps.
Solution
A page will be called with the ID/ROW_ID needed to fetch data from the table into a rich text field
This ID field is called P867_ROW_ID (hidden)
The rich text field is called P867_CLOB_COLUMN.
Steps to display the CLOB in the rich text editor.
On page load, create a Javascript dynamic action
Code:
CKEDITOR.on("instanceReady", function(event) {
// Run the Ajax Callback process
var p = apex.server.process('GET_CLOB', {
pageItems:['P867_ROW_ID']
}, {
dataType: 'html'
});
// When the process is done, set the value to the page item
p.done(function(data) {
apex.item('P867_CLOB_COLUMN').setValue(data);
});
});
Now create an Ajax callbak (Type PL/SQL Code) called GET_CLOB. (Called in the above code).
Code:
DECLARE
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;
BEGIN
BEGIN
-- Get the CLOB
SELECT NVL(HELP_TEXT, EMPTY_CLOB())
INTO l_clob
FROM your_Table
WHERE ROWid = :P867_ROW_ID;
-- 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;
With the above pieces of code, when the page loads up, it fetches the data from the DB using the keys provided to it from the calling page using a mixture of Javascript, ajax callback and PL/SQL.
How to send the data back to the CLOB in the database
In this case, the trigger point is a submit button which has been added. You could use other triggers.
My button initiates a dynamic action to call some more JavaScript.
// Function to send CLOB to process
function sendClob(call, array, request) {
if (array[call-1] || call === 1) {
var p = apex.server.process('SET_CLOB', {
x01: call
, x02: array[call-1]
}, {
dataType: 'html'
});
p.done(function(data) {
sendClob(call + 1, array, request);
})
} else {
apex.submit(request);
}
}
var array = apex.item('P867_CLOB_COLUMN').getValue().match(/[\s\S]{1,20000}/g) || [];
sendClob(1, array, 'SAVE');
As with the "Get" method, we will be wanting some more Ajax.(SET_CLOB).
Create an Ajax callbak (Type PL/SQL Code) called SET_CLOB. (Called in the above code).
DECLARE
l_src CLOB;
l_trg CLOB;
BEGIN
IF (APEX_APPLICATION.G_X01 = 1) THEN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'CLOB_CONTENT');
APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'CLOB_CONTENT'
, p_clob001 => APEX_APPLICATION.G_X02);
ELSE
DBMS_LOB.CREATETEMPORARY( lob_loc => l_trg
, cache => true
, dur => DBMS_LOB.CALL);
SELECT clob001
INTO l_src
FROM apex_collections
WHERE collection_name = 'CLOB_CONTENT';
DBMS_LOB.APPEND(l_trg, l_src);
DBMS_LOB.APPEND(l_trg, APEX_APPLICATION.G_X02);
APEX_COLLECTION.UPDATE_MEMBER( p_collection_name => 'CLOB_CONTENT'
, p_seq => '1'
, p_clob001 => l_trg);
END IF;
END;
Now create some PL/SQL code on submit.
Code:
I've simplified mine, since I'm only ever updating an existing BLOB.
Begin
update your_table
set text =
(select clob001 from apex_collections WHERE collection_name = 'CLOB_CONTENT')
where rowid = :P867_ROW_ID;
END;
Finally, sice you've taken the trouble to fetch your CLOB into a rich text editor, don't forget to show the toolbars that allow you to take advantage of all the editing tools. This is done in the setting of your CLOB column.
Comments
Post a Comment