1. Overview
This document will explain about how to display real time notification to the users in Oracle Apex.
2. Technologies and Tools Used
The following technology has been used to implement the real time notification feature,
- Oracle Apex
- PL/SQL
- Javascript
3. Use Case
Real–Time Notification is an automated system that sends you an alert whenever your desired information changes. so users can receive updates and notifications directly on the website itself rather than going to Gmail and other mail service providers.
4. Steps with Screenshot
Steps to be followed,
Step 1: Create a table for having notification details like the below one.
CREATE TABLE fin_user_notifications
(
notification_id NUMBER,
notification_dtls VARCHAR2(4000),
created_by VARCHAR2(100),
created_date DATE,
seen_time TIMESTAMP (6),
seen_type VARCHAR2(1),
message_from VARCHAR2(100),
message_to VARCHAR2(100)
)
Step 2: Create a new page in oracle APEX and then create a AJAX Callback process on the page with the name of createNotificationObject. The process name can be of anything. Paste the following code in the body of the createNotificationObject process.
DECLARE
notification_msg VARCHAR2(4000);
msg_seen VARCHAR2(10);
p_notification_id NUMBER;
l_not_seen NUMBER;
BEGIN
SELECT COUNT(1)
INTO l_not_seen
from fin_user_notifications
where NVL(seen_type,’N’)=’N’;
apex_json.Open_object();
IF l_not_seen>0
THEN
SELECT notification_dtls,
seen_type,
notification_id
INTO notification_msg,
msg_seen,
p_notification_id
FROM fin_user_notifications
WHERE seen_time IS NULL
ORDER BY notification_id
fetch first 1 ROWS ONLY;
apex_json.WRITE(‘MESSAGE’, notification_msg);
apex_json.WRITE(‘SEEN_TYPE’, msg_seen);
UPDATE fin_user_notifications
SET seen_time = localtimestamp,
seen_type = ‘Y’
WHERE notification_id = p_notification_id;
END IF;
apex_json.Close_object();
END;
Step 3: Create a IIFE(Immediately Invoked Function Expression) in the Page level function and global variable declaration section as follows.It is like a recursive function-it calls by itself untill there is no record with column value of seen_type as ‘N’.
try{
(function loop(i) {
setTimeout(function() {
apex.server.process (
‘createNotificationObj’,
{},
{
async : true,
dataType : ‘json’,
success : function(data) {
if (data.MESSAGE) {
if (data.SEEN_TYPE == ‘N’) {
apex.message.showPageSuccess(data.MESSAGE);
}
}
}
}
);
loop(i);
}, 4000);
})();
}
catch(err){
console.log(err)
}
Step 4: Finally, when we insert a row in the fin_user_notifications table with seen_type is set as ‘N’, immediately you will get notified with the notification message at page level as follows.
INSERT INTO fin_user_notifications
(
notification_id,
notification_dtls,
message_from,
message_to,
seen_time,
seen_type,
created_by,
created_date
)
VALUES
(
1,
’This is a notification’,
’admin’,
’user’,
’ ‘,
’N’,
’admin’,
SYSDATE
);
5. Conclusion
This is all about how to display real time notification in Oracle APEX. We can further customize the notification display based on user requirement with the help of CSS and HTML.
