Oracle APEX Application – Set Availability Status Using PLSQL

Overview

This blog will explains, how to disable the APEX application by end user during the release or it is under maintenance .

Technologies and Tools Used

The following technology has been used to achieve the same.

• Oracle APEX
• PLSQL

Use Case

Oracle APEX end users as “ADMIN” role can able to set availability  of the application.

Examples

There are different types of status is available in Oracle APEX application. They are

• 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.

  Set the Application Status using PLSQL

  Types of Status

  • AVAILABLE
  • AVAILABLE_W_EDIT_LINK
  • DEVELOPER_ONLY
  • RESTRICTED_ACCESS
  • UNAVAILABLE
  • UNAVAILABLE_PLSQL
  • UNAVAILABLE_URL

  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;

Conclusion