Introduction

After publishing an article on CKEditor5 Image Upload, I received a comment from Stefan Dobre from the Oracle APEX team that made me think about how to handle image uploads in a more clever way:

And a couple more things that you may still have to think about:

The content probably won't only be viewed from within the editor. Chances are you'll wish to just render the HTML in a static or dynamic content region somewhere. How would you go about appending that session token then?

You will most likely encounter the concept of "posts" as you won't only be editing the same content each time. In that case, how will you link a post to its images? That is, if a post is deleted, how will you also delete its images?

Similarly, say a user uploads an image, but never actually submits the page. What happens to these "unsaved" images?

I still don't have an elegant solution for these problems, but they're worth thinking about.

-Stefan

How to display images outside of the editor

This is a valid questions because, you will probably display it on a report like a classic or an interactive one. The issue is, as we are using the APEX session to secure our endpoints, the image will not be displayed. Remember that we used a downcast in the editor but since you will only be querying the notes table, it will not be possible to use it.

So how to solve that problem? Easy answer: PL/SQL to the rescue! We will just write a simple PL/SQL function to append the session parameter to the url and use that function to retrieve the note.

create or replace function replacelinks(p_content in clob)
    return clob
    is
        l_links apex_t_varchar2 := apex_string_util.find_links(p_content, false);
        l_content clob := p_content;
    begin 
        for i in 1..l_links.count()
        loop
            l_content := replace(l_content, 'src="'||l_links(i)||'"', 'src="'||l_links(i)||'?_apex_session='||v('APP_ID')||','||v('APP_SESSION')||'"');
        end loop;
        return l_content;
    end replacelinks;

Then the query of the report will looks like

select 
      id
    , replacelinks(content) as content
from notes

Note that this is not fully optimized and probably not the best way to do it (If you have other ideas, drop a comment, I will be happy to update the post!)

How to manage the note deletion or not used images

Again, this is really good questions, if a note is deleted then the images will remains as they are not stored in the same column. Even worse, how to manage image that are uploaded but never used... For this again, PL/SQL (and javascript) can solve the problem...

Let the users decide

You could show to the user a report of the uploaded images and allow them to add it again inside a note. To do it, we need a classic report based on the ckeditor_images table. Add the following HTML code to the HTML Expression attribute of the ID column in order to add a Insert Image button and execute the insertImage function when it's clicked.

<button 
    type="button" 
    class="t-Button t-Button--icon t-Button--hot t-Button--iconLeft" 
    data-id="#ID#" 
    onclick="insertImage(this)">
    <span 
        aria-hidden="true" 
        class="t-Icon t-Icon--left fa fa-plus">
    </span>Insert Image
</button>

Now we have to declare the javascript function in the Function and Global Variable Declaration attribute of the page

function insertImage(pThis){
    // get image ID
    let id = apex.jQuery(pThis).data("id");

    // get editor instance
    var editor = apex.item("P4_CONTENT_IMAGE").getEditor();

    // Insert the image
    editor.model.change( writer => {
        const insertPosition = editor.model.document.selection.getLastPosition();
        const imageElement = writer.createElement( 'imageBlock', {
                            src: "YOUR_GET_ENDPOINT_URL" + id
                        } );
        writer.insert( imageElement, insertPosition );
    });
}

When the button is clicked, the image will be added to the editor. The same idea could be used to add a delete button to the same report.

Add an cleanup procedure

The idea here is to run a procedure that will delete images that are not used and have been uploaded for at least a month. What a perfect example of a good fit for using the APEX automations!

Let's create an automation called "CKEditor Image Cleanup" that runs every day at midnight

The PL/SQL code to execute is pretty simple and will delete all images that are not used in a note and are created for at least a month:

begin
    delete
    from ckeditor_images ci
    -- Images not used in a not
    where not exists (
        select 1
        from notes
        where instr(content, 'src="YOUR_GET_ENDPOINT_URL' ||ci.id ||'"') > 0
    )
    -- Created for at least a month
    and add_months(created, 1) < sysdate;
end;

Last words

First, I get a valid comment from Jon Dixon on using OAuth2 to secure the endpoints instead of the APEX session. This is also a good approach and the main advantage is that the endpoint can be used outside of an APEX application.

Finally, I received a question on Twitter about how to limit the file size.

Yes, it would be amazing to have it built-in by default in APEX. @moreaux_louis , when trying to insert a large image, I'm having an error : "File too big (max 100kb)". Where do you configure this limit ?
— Nicolas Pilot (@nicolaspilot) July 22, 2022

I updated the blog post but the solution is to limit that size in the POST handler by using sys.dbm_lob.getlenght and a custom exception:

declare
    l_id number;
    l_blob blob;
    e_file_too_big exception;
begin 
    dbms_lob.createtemporary(l_blob, false);
    l_blob := :body;

    if (sys.dbms_lob.getlength(l_blob) > 100000) then
        raise e_file_too_big;
    end if;

    insert into ckeditor_images (image, content_type) values (l_blob, :content_type)
    returning id into l_id;
    commit;

    dbms_lob.freetemporary(l_blob);

    :status := 201;

    apex_json.open_object;
    apex_json.write(
        p_name => 'url', 
        p_value => owa_util.get_cgi_env('REQUEST_PROTOCOL') ||
                   '://' ||
                   owa_util.get_cgi_env('SERVER_NAME') ||
                   owa_util.get_cgi_env('SCRIPT_NAME')||
                   owa_util.get_cgi_env('PATH_INFO') || 
                   '/' || l_id /*||
                   '?_apex_session=' || :apex_session*/
    );
    apex_json.close_object;
exception
when e_file_too_big then 
    apex_json.open_object;
    apex_json.open_object('error');
    apex_json.write('message', 'File too big (max 100kb).');
    apex_json.close_object;
    apex_json.close_object;
when others then
    apex_json.open_object;
    apex_json.open_object('error');
    apex_json.write('message', 'Something wrong!');
    apex_json.close_object;
    apex_json.close_object;
end;

As mentioned in the documentation, you just have to return a JSON object containing an error object that contains a message attribute:

{
    "error": {
        "message": "The image upload failed because the image was too big (max 1.5MB)."
    }
}

That's all, thank you all for your comments and suggestions for improvement.

Oracle APEX tips and tricks