Skip to main content

Ping/notify user on event


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?


    PROCEDURE ajax_ping
        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
        ) 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;


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)


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


  1. The approach you used here is more an heart-beat approach and not websockets. Websocket use single persistent TCP connection and receive multiple responses over it

  2. Very useful and informative to create the chat app in apex, good work, keep it up


Post a Comment