Instant Maintenance Mode for Oracle APEX Apps

I needed to set maintenance mode for my 15 APEX applications in the production environment.

Typically, the process is simple - just set Application Definition - Status to “Unavailable”:

And your users will be logged out and will see this ugly, default message:

But, it is much better to show customers a nice message like this, right? (Thanks to Ricardo’s blog post for inspiration to use much better HTML Code)

Ok, now for my 15x applications, I need to manually set every app’s status to Unavailable and then set it back to Available after the upgrade. No way that I’m gonna click it :)

We’ve got Oracle APEX API to do this !

💡
APEX_UTIL.SET_APPLICATION_STATUS (for >= APEX 24.2 use APEX_APPLICATION_ADMIN.SET_APPLICATION_STATUS instead)

That’s why I prepared this simple PL/SQL script that automates setting all applications in my workspace to Unavailable status. It also sets a custom HTML page with a friendly message and an image, so users know the system is temporarily down for improvements.

The script:

• Retrieves workspace ID

• Loops through all applications in the workspace

• Sets each application status to UNAVAILABLE

• Applies a visually appealing maintenance page

set define off
DECLARE
  v_unavailable_value VARCHAR2(4000) := 
'<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Maintenance Mode</title>
  <style>
    body {
      margin: 0;
      padding: 0;
      background-color: #f0f0f0;
      font-family: ''Arial'', sans-serif;
      display: flex;
      align-items: center;
      justify-content: center;
      height: 100vh;
      text-align: center;
      color: #333;
    }
    .container {
      background: #fff;
      padding: 40px;
      border-radius: 8px;
      box-shadow: 0 2px 10px rgba(0,0,0,0.1);
      max-width: 600px;
      width: 90%;
    }
    h1 {
      margin-top: 0;
      font-size: 2.5em;
    }
    p {
      font-size: 1.2em;
      margin: 20px 0;
    }
    img {
      max-width: 100%;
      border-radius: 8px;
      margin-top: 20px;
    }
    .footer {
      margin-top: 20px;
      font-size: 0.9em;
      color: #666;
    }
  </style>
</head>
<body>
  <div class="container">
    <h1>Maintenance Mode</h1>
    <p>We are currently performing some improvements. We''ll be back shortly!</p>
    <img src="https://miro.medium.com/v2/resize:fit:1100/format:webp/0*Zcn7ZCwN4DGFtmo8.png" width="600" height="400" alt="Maintenance Image">
    <div class="footer">
      &copy; 2025 Pretius.
    </div>
  </div>
</body>
</html>';

  v_workspace_id NUMBER;
BEGIN
  -- Retrieve the workspace ID
  SELECT workspace_id
  INTO v_workspace_id
  FROM apex_workspaces
  WHERE workspace = 'SANDBOX';

  -- Set the security group ID
  apex_util.set_security_group_id(p_security_group_id => v_workspace_id);

  FOR cur IN (
    SELECT application_id 
    FROM apex_applications
    WHERE workspace_id  = v_workspace_id
  )
  LOOP
    apex_util.set_application_status(
      p_application_id    => cur.application_id,
      p_application_status => 'UNAVAILABLE',
      p_unavailable_value  => v_unavailable_value
    );
  END LOOP;
  commit;
END;
/

Once your maintenance time is finished, make all your workspace apps available again:

set define off
DECLARE
  v_unavailable_value VARCHAR2(4000) := '';
  v_workspace_id NUMBER;
BEGIN
  -- Retrieve the workspace ID
  SELECT workspace_id
  INTO v_workspace_id
  FROM apex_workspaces
  WHERE workspace = 'SANDBOX';

  -- Set the security group ID
  apex_util.set_security_group_id(p_security_group_id => v_workspace_id);

  FOR cur IN (
    SELECT application_id 
    FROM apex_applications
    WHERE workspace_id  = v_workspace_id
  )
  LOOP
    apex_util.set_application_status(
      p_application_id    => cur.application_id,
      p_application_status => 'AVAILABLE',
      p_unavailable_value  => v_unavailable_value
    );
  END LOOP;
  commit;
END;
/

Now it’s up to you how you will use those scripts.

Feel free to customise the HTML content and image to match your branding and message.

My recommendation?

Add those scripts before and after your Liquibase / SQLcl Project deployments, and you will have a fully automated process for logging out your users and informing them about the upgrade.

Enjoy!

5
Subscribe to my newsletter

Read articles from Rafal Grzegorczyk directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Rafal Grzegorczyk
Rafal Grzegorczyk

Oracle APEX & PL/SQL Developer with 10 years of experience in IT, including financial systems for government administration, energy, banking and logistics industry. Enthusiast of database automation. Oracle ACE Associate. Certified Liquibase database versioning tool fan. Speaker at Kscope, APEX World, SOUG, HrOUG, POUG and DOAG. Likes swimming in icy cold lakes in winter and playing basketball.