Ping/notify user on event

I

I had to switch to using materialized view for my custom navigation because quering dictionaries on free cloud tends to be too slow. So now I have a button Publish changes to refresh this MVW and it takes some time. I don't want to keep user waiting while this is done, so I refresh it via background job. Which brings a problem. How to inform user about the result of the background job?

Websockets for poor. I created application process AJAX_PING and call it in Javascript each 6 seconds. This lightweight process just checks content of user_messages table which is filled through app_actions.send_message procedure.

So user click on the Publish button, which triggers DA, which starts background job for MVW refresh, disable the button and show user a message confirming start of the operation. User can wait on the page or freely move through the application.

20 seconds later procrastinating on a different page a result arrive:

I think this is a very user friendly way how to notify user. Don't you?

And I can use this not just for background jobs but for any event when I want to reach user before he move to another page (or refresh). Hey user, your colleague just imported a file, wanna look at it?

Process:

    PROCEDURE ajax_ping
    AS
    BEGIN
        APEX_JSON.OPEN_OBJECT();
        --
        FOR c IN (
            SELECT m.*
            FROM user_messages m
            WHERE m.app_id              = app.get_app_id()
                AND m.user_id           = app.get_user_id()
                AND (m.session_id       = app.get_session_id() OR m.session_id IS NULL)
                AND m.delivered_at      IS NULL
            ORDER BY m.created_at DESC
            FETCH FIRST 1 ROWS ONLY
        ) LOOP
            APEX_JSON.WRITE('message',  c.message_payload);
            APEX_JSON.WRITE('status',   NVL(c.message_type, 'SUCCESS'));
            --
            UPDATE user_messages m
            SET m.delivered_at          = SYSDATE
            WHERE m.app_id              = c.app_id
                AND m.user_id           = c.user_id
                AND m.message_id        = c.message_id;
        END LOOP;
        --
        APEX_JSON.CLOSE_OBJECT();
    END;

Table:

CREATE TABLE user_messages (
    app_id              NUMBER(4)       CONSTRAINT nn_user_messages_app_id      NOT NULL,
    user_id             VARCHAR2(30)    CONSTRAINT nn_user_messages_user_id     NOT NULL,
    message_id          INTEGER         CONSTRAINT nn_user_messages_message_id  NOT NULL,
    message_type        VARCHAR2(16),
    message_payload     VARCHAR2(2000),
    session_id          INTEGER,
    --
    created_by          VARCHAR2(30),
    created_at          DATE,
    delivered_at        DATE,
    --
    CONSTRAINT pk_user_messages
        PRIMARY KEY (app_id, user_id, message_id)
);

Javascript:

(function loop(i) {
    setTimeout(function() {
        apex.server.process (
            'AJAX_PING',
            {},  // params
            {
                async       : true,
                dataType    : 'json',
                success     : function(data) {
                    if (data.message) {
                        if (data.status == 'SUCCESS') {
                            apex.message.showPageSuccess(data.message);
                        }
                        else if (data.status == 'WARNING' || data.status == 'ERROR') {
                            apex.message.showErrors([{
                                type:       apex.message.TYPE.ERROR,
                                location:   ['page'],
                                message:    data.message,
                                unsafe:     false
                            }]);
                        }
                    }
                }
            }
        );
        loop(i);
    }, 6000);  // 6sec forever
})();