Introduction:–
In Oracle APEX, seamless navigation between two applications without requiring users to log in again enhances user experience and efficiency. This can be achieved by securely passing authentication details from one APEX application to another. When a user logs into the first application, their session details can be used to grant access to a second application without requiring re-authentication. This method is particularly useful for organizations managing multiple APEX applications while ensuring security and a smooth transition between them.
The following technologies has been used to achieve the same.
- Oracle APEX
- Java script
Why we need to do: –
Improved User Experience – Users can navigate between multiple APEX applications without repeatedly entering login credentials, making the process more efficient and user-friendly.
Increased Productivity – Eliminates the extra step of logging in multiple times, allowing users to focus on their tasks without interruptions.
Enhanced Security – Ensures that only authenticated users can access the second application using session-based authentication, preventing unauthorized access.
Efficient Application Integration – Useful for organizations managing multiple APEX applications, allowing seamless data sharing and workflow continuity across different systems.
How do we solve: –
Step 1: Create a global page item named P0_REF_ID.
Step 2: Create the following tables to store authentication data and error logs.
CREATE TABLE APEX_WINDOW_AUTHEN (
USER_ID VARCHAR2(100),
REF_ID VARCHAR2(100),
L_DATE DATE,
MENU VARCHAR2(100)
);
CREATE TABLE ERROR_LOG_2FA (
SEQ_NO NUMBER,
MODULE_NAME VARCHAR2(100),
SUB_INFORMATION VARCHAR2(100),
ERROR_DESC VARCHAR2(1000),
CREATED_ON DATE
);
CREATE SEQUENCE SEQ_2FA
MINVALUE 1
MAXVALUE 9999999999999
START WITH 1
INCREMENT BY 1
NOCACHE;
Step 3: Create the following stored procedures.
CREATE OR REPLACE PROCEDURE p_Ins_Errorlog(
i_module_name VARCHAR2,
i_sub_information VARCHAR2,
i_error_desc VARCHAR2
) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ERROR_LOG_2FA (
SEQ_NO, MODULE_NAME, SUB_INFORMATION, ERROR_DESC, CREATED_ON
) VALUES (
SEQ_2FA.NEXTVAL, i_module_name, i_sub_information, i_error_desc, SYSDATE
);
COMMIT;
END p_Ins_Errorlog;
/
CREATE OR REPLACE PROCEDURE p_Get_Auth_REF_ID(
i_USER_ID VARCHAR2,
i_menu IN VARCHAR2,
o_Msg OUT VARCHAR2
) AS
REF_ID APEX_WINDOW_AUTHEN.REF_ID%TYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
— Delete existing record for the user
DELETE FROM APEX_WINDOW_AUTHEN WHERE UPPER(USER_ID) = UPPER(i_USER_ID);
— Generate a unique REF_ID
SELECT TRUNC(DBMS_RANDOM.VALUE(1, 999999)) ||
UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(i_USER_ID)))
INTO REF_ID FROM DUAL;
— Insert new record
INSERT INTO APEX_WINDOW_AUTHEN (USER_ID, REF_ID, L_DATE, MENU)
VALUES (i_USER_ID, REF_ID, SYSDATE, i_menu);
— Retrieve REF_ID
SELECT REF_ID INTO o_Msg FROM APEX_WINDOW_AUTHEN WHERE UPPER(USER_ID) = UPPER(i_USER_ID);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_Ins_Errorlog(‘PROCEDURE’, ‘p_Get_Auth_REF_ID’, SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
o_Msg := SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
END p_Get_Auth_REF_ID;
/
Step 4: Create a button with a dynamic action to navigate to the target application.
Action 1 (PL/SQL Code):
BEGIN
IF :P0_REF_ID IS NULL THEN
p_Get_Auth_REF_ID(i_USER_ID => :APP_USER, i_menu => ‘Home’, o_msg => :P0_REF_ID);
END IF;
END;
Action 2 (JavaScript Code):-
window.open(‘https://apex.oracle.com/pls/apex/r/pothiarunkannan/2fa-app2/login?P9999_REF_ID=’ + $(“#P0_REF_ID”).val());
Step 5: Disable the default login region.
Step 6: In the target application’s login page, create three hidden page items: P9999_REF_ID, P9999_ERR_MSG, and P9999_USERNAME.
Step 7: Add the following PL/SQL code before the login page rendering.
DECLARE
I_USER_ID VARCHAR2(200);
l_Msg VARCHAR2(100);
l_SESSION VARCHAR2(500) := :APP_SESSION;
l_Diff_Sec NUMBER;
BEGIN
:P9999_ERR_MSG := ”;
IF :P9999_REF_ID IS NOT NULL THEN
BEGIN
— Calculate the time difference in seconds
SELECT (SYSDATE – w.L_DATE) * 24 * 60 * 60 INTO l_Diff_Sec
FROM APEX_WINDOW_AUTHEN w WHERE w.Ref_Id = :P9999_REF_ID;
— Check if time difference exceeds 30 seconds
IF l_Diff_Sec > 30 THEN
:P9999_ERR_MSG := ‘Error: Time difference exceeds 30 seconds’;
ELSE
— Retrieve user ID
SELECT w.USER_ID INTO I_USER_ID FROM APEX_WINDOW_AUTHEN w WHERE w.Ref_Id = :P9999_REF_ID;
:P9999_USERNAME := I_USER_ID;
— Perform login
wwv_flow_custom_auth_std.login(
P_UNAME => :P9999_USERNAME,
P_PASSWORD => ”,
P_SESSION_ID => :APP_SESSION,
P_FLOW_PAGE => :APP_ID || ‘:1’
);
— Redirect to home page
apex_util.redirect_url(‘home?session=’ || l_SESSION);
END IF;
— Delete the record after verification
DELETE FROM APEX_WINDOW_AUTHEN WHERE UPPER(Ref_Id) = UPPER(:P9999_REF_ID);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN :P9999_ERR_MSG := ‘Error: No data found for the provided REF ID.’;
WHEN OTHERS THEN :P9999_ERR_MSG := ‘Unexpected error: ‘ || SQLERRM;
END;
END IF;
END;
Step 8: Create a static region with the following HTML code.
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Error Message</title>
<style>
body {
font-family: Arial, sans-serif;
display: flex;
justify-content: center;
align-items: center;
height: 100vh;
background-color: #f8f9fa;
}
.error-message {
display: none;
padding: 15px;
background-color: #ff4d4d;
color: white;
font-weight: bold;
border-radius: 5px;
animation: fadeIn 1s ease-in-out;
}
@keyframes fadeIn {
from { opacity: 0; transform: scale(0.9); }
to { opacity: 1; transform: scale(1); }
}
.logout-message {
text-align: center;
padding: 20px;
background-color: #ffc107;
border-radius: 5px;
box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.2);
}
.logout-message a {
display: inline-block;
margin-top: 10px;
padding: 10px 15px;
background-color: #007bff;
color: white;
text-decoration: none;
border-radius: 5px;
transition: background 0.3s;
}
.logout-message a:hover {
background-color: #0056b3;
}
</style>
</head>
<body>
<div class=”error-message” id=”error”>&P9999_ERR_MSG.</div>
<div class=”logout-message”>
<p>Oops! You are logged out. Please log in again.</p>
<a href=”https://apex.oracle.com/pls/apex/r/pothiarunkannan/2fa-authentication/login?”>Click here to login</a>
</div>
</body>
</html>
Conclusion: –
Implementing auto-disappearing success messages in Oracle APEX is a simple yet effective way to enhance the user experience. By dynamically hiding these messages based on specific conditions, we can maintain a clean and intuitive interface, reduce distractions, and ensure efficient communication with users.
Screenshot:-
Source Application to call :-
Target Application Home page:-
Without call procedure access Target Application:-
