To prevent the same user from logging in multiple times simultaneously in Oracle APEX, you can implement session management using a custom login process and a session tracking table.
Here’s how to do it step by step:
Step 1: Create a Session Tracking Table
Create a new table to store active sessions.
CREATE TABLE user_sessions (
username VARCHAR2(100),
session_id VARCHAR2(100),
login_time DATE,
is_active CHAR(1) DEFAULT 'Y'
);
Step 2: Modify the Login Process
Use a custom authentication process or edit the existing POST-LOGIN procedure.
Here’s what you can do:
Check for an active session:
DECLARE
v_exists NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_exists
FROM user_sessions
WHERE username = :APP_USER
AND is_active = 'Y';
IF v_exists > 0 THEN
APEX_UTIL.set_session_state('LOGIN_ERROR_MESSAGE', 'You are already logged in from another device.');
APEX_UTIL.logout;
-- stop further processing
raise_application_error(-20001, 'Duplicate login not allowed.');
END IF;
-- If no active session, insert a new one
INSERT INTO user_sessions (username, session_id, login_time)
VALUES (:APP_USER, :APP_SESSION, SYSDATE);
COMMIT;
END;
Place this logic in:
Authentication Scheme > Post-Authentication Procedure Name Or inside a Login Process
Step 3: Clear the Session on Logout or Timeout
In Logout URL, add a process to deactivate the session. Set your Logout URL to something like this:
f?p=&APP_ID.:1:&SESSION.::NO::P1_LOGOUT:Y
Then on Page 1 (or your home page), add a Before Header process like this:
BEGIN
IF :P1_LOGOUT = 'Y' THEN
UPDATE user_sessions
SET is_active = 'N'
WHERE username = :APP_USER
AND session_id = :APP_SESSION;
COMMIT;
END IF;
END;
Also, consider writing a DB job to clear stale sessions based on time:
BEGIN
DELETE FROM user_sessions
WHERE login_time < SYSDATE - 1/24; -- older than 1 hour
COMMIT;
END;
Optional: Session Timeout Hook
In Security > Session Timeout, you can configure a redirect to a page that will also mark the session inactive using the same UPDATE logic.
Always keep the username case-sensitive if you're not forcing case handling.
Use APEX_SESSION views for more advanced session tracking (available from APEX 20+).
