check table every one second

Question

I have a table that I want to check the status column of this table every second and send a message if the value is 1 and if the value is 2 another message.

How can I do this?

My oracle is 21.3 and apex is 20.1 and ords 21.4

Answer 1

This can be achieved using an ajax callback process. Here is an example of an ajax process that gets a random ename (from emp/dept sample table) same and sets that value in page item P51_ENAME_RANDOM_JSON every 3 seconds.

• Create an Ajax Callback process (Page Processing > Ajax Callback) :
  • name: GETMESSAGE
  • source:

DECLARE  
  l_emp emp%ROWTYPE;
BEGIN  
  SELECT *
    INTO l_emp
    FROM emp 
   ORDER BY dbms_random.value fetch first 1 rows only;
  APEX_JSON.open_object; -- employee {
  APEX_JSON.write('ename', l_emp.ename);
  APEX_JSON.close_object; -- }    
  APEX_JSON.free_output;
END;

• Create a javascript function to invoke the callback and set an interval to execute every 3 seconds (Page Properties > Function and Global Variable Declaration)

$(document).ready(function() {
 setInterval('getRandomEnameJson()', 3000);
});

function getRandomEnameJson () {
    apex.server.process(
        'GETMESSAGE', Callback name
        {},  
        {
            success: function (pData) {       
            console.log(pData);
            apex.item( "P51_ENAME_RANDOM_JSON" ).setValue(pData.ename);
        },
            dataType: "json"                     
        }
    );
}

Notice that every second really is a lot. If you refresh a message every 3 or 5 seconds that is usually ok. If a single has a screen open for an hour and a message request is done every second then that will be 3600 individual database calls - just for that message query. If the numbers of users is higher then this needs to be multiplied. Consider a higher interval.