Press "Enter" to skip to content

How to Log Users Login and Logout Details Through Oracle Forms

Log user’s login and logout details in to table through Oracle Forms using POST-LOGON and PRE-LOGOUT triggers to track the user’s login and logout activity for auditing purposes.
Track user login logout activity in Oracle Forms
In this example one table and a sequence object is used to log the data in to table called login_out and the login information would be logged through Post-Logon trigger and logout information would be logged through Pre-Logout trigger in Oracle Forms. Follow the below mentioned steps to perform this task.

1. Create a Sequence object in Oracle Database.

CREATE SEQUENCE login_seq
   START WITH 1
   INCREMENT BY 1
   NOCACHE
/

2. Create a Table in Oracle Database.

CREATE TABLE login_out
(
   srlno     NUMBER (10) PRIMARY KEY,
   loguser   VARCHAR2 (20 BYTE),
   indate    DATE,
   outdate   DATE
)
/

3. Create a Post-Logon Trigger at Form Level in Main Form of Your Application.

DECLARE
   v_seq    NUMBER (10);
   v_user   VARCHAR2 (20) := GET_APPLICATION_PROPERTY (username);
BEGIN
   SELECT login_seq.NEXTVAL INTO v_seq FROM DUAL;
   /* this global variable is created to use on pre-logout trigger to update the correspondent record. */
   :Global.login_seq := v_seq;
   INSERT INTO login_out (srlno, loguser, indate)
       VALUES (v_seq, v_user, SYSDATE);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE form_trigger_failure;
END;

4. Create a Pre-Logout Trigger at Form Level in Main Form of Your Application.

DECLARE
   v_seq    NUMBER (10) := :GLOBAL.login_seq;
BEGIN
   Update login_out
      set outdate = SYSDATE
      where srlno = v_seq;
— No need to commit here it will do automatically
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE form_trigger_failure;
END;

Now run the form and after that you can check the login_out table to view the data as following:
SELECT *
  FROM login_out
 WHERE TRUNC (indate) = TRUNC (SYSDATE)
 /
Note: These triggers should added into Main Form only of your application, not in every form.