1. Overview
This document will explains, how to disable the apex application during the release or it is under maintenance by using PLSQL.
2. Technologies and Tools Used
The following technology has been used to achieve Custom Notifications
- Oracle PLSQL
- Oracle Apex
3. Use Case
Assume that there is a requirement to to disable the apex application during the release or it is under maintenance.
4. Architecture
Following steps explains in detail,
Step 1: Set the Application Status using PLSQL
APEX_UTIL.SET_APPLICATION_STATUS(
p_application_id IN NUMBER,
p_application_status IN VARCHAR2,
p_unavailable_value IN VARCHAR2,
p_restricted_user_list IN VARCHAR2);
Types of Status
- Available– Application is available with no restrictions.
- Available with Developer Toolbar– Application is available for use. For developers, the Developer toolbar displays at the bottom of each page. Requires the developer to be logged in to the App Builder in the same browser session.
- Available to Developers Only– Application is available to users having developer privileges.
- Restricted Access– Application is available to developers named in the Restrict to comma separated user list.
- Unavailable– Application cannot be run or edited. The message in Message for unavailable application displays when users attempt to access the application.
- Unavailable (Status Shown with PL/SQL)– Application cannot be run or edited.
- Unavailable (Redirect to URL)– Application cannot be run or edited.
Step 2:
Here is the below procedure to set the single or multiple Application Status.
CREATE OR REPLACE PROCEDURE SJ_SET_APP_STATUS (
P_APP_ID VARCHAR2,
P_APP_STATUS VARCHAR2,
P_MESSAGE VARCHAR2 DEFAULT NULL,
P_DELIMITER VARCHAR2 DEFAULT ‘:’
)
IS
CURSOR SJ_GETAPP_ID
IS
SELECT
APP.WORKSPACE_ID,
APP.APPLICATION_ID
FROM
APEX_APPLICATIONS APP
WHERE
APP.APPLICATION_ID IN (
SELECT COLUMN_VALUE
FROM
TABLE ( APEX_STRING.SPLIT(P_APP_ID,P_DELIMITER) )
);
LV_MESSAGE VARCHAR2(4000);
BEGIN IF P_APP_STATUS NOT IN (
‘AVAILABLE’, ‘AVAILABLE_W_EDIT_LINK’, ‘DEVELOPER_ONLY’,
‘RESTRICTED_ACCESS’, ‘UNAVAILABLE’, ‘UNAVAILABLE_PLSQL’,
‘UNAVAILABLE_URL’
) THEN
RAISE_APPLICATION_ERROR(-20002,’Status ‘|| P_APP_STATUS || ‘ is not supported’);
END IF;
IF P_MESSAGE IS NOT NULL
THEN
LV_MESSAGE :=P_MESSAGE;
ELSE
LV_MESSAGE :=’This application is currently unavailable at this time.’;
END IF;
FOR I IN SJ_GETAPP_ID LOOP
BEGIN
APEX_UTIL.SET_APPLICATION_STATUS
(P_APPLICATION_ID => I.APPLICATION_ID,
P_APPLICATION_STATUS => P_APP_STATUS,
P_UNAVAILABLE_VALUE => LV_MESSAGE
);
END;
END LOOP;
COMMIT;
END;
Output
Status (Unavailable)
Begin
Sj_Set_App_Status
(P_App_Id => ‘73307:37986’,
P_App_Status => ‘UNAVAILABLE’,
P_Message => ‘This application is currently unavailable at this time.’,
P_Delimiter => ‘:’);
Status (Available)
Begin
Sj_Set_App_Status
(P_App_Id => ‘73307:37986’,
P_App_Status => ‘AVAILABLE’,
P_Message => ‘This application is currently unavailable at this time.’,
P_Delimiter => ‘:’);
End;
