In a previous post, we showed how to change the availability status of an application from the APEX workspace. In this post, we will demonstrate how to change the availability status using PL/SQL.

Set Application availability using PL/SQL

It is possible to perform all of the tasks described above by using the procedure SET_APPLICATION_STATUS provided in the package APEX_UTIL . This is useful if you want to automate the availability status of the application or if you are using CI/CD to deploy your applications.

APEX_UTIL.SET_APPLICATION_STATUS(
   p_application_id IN NUMBER,        -- The application ID
   p_application_status IN VARCHAR2,  -- The new status  
   p_unavailable_value IN VARCHAR2,   -- The message or URL  
   p_restricted_user_list IN VARCHAR2 -- The restricted users list (comma-separated)
);

You can use the following status values for the parameter p_application_status

AVAILABLE: The application is available with no restrictions.
AVAILABLE_W_EDIT_LINK: The application is available with no restrictions. The Developer Toolbar is shown to developers
DEVELOPERS_ONLY: Application only available to developers.
RESTRICTED_ACCESS: Application only available to users in p_restricted_user_list.
UNAVAILABLE: Application unavailable. The message is shown in p_unavailable_value.
UNAVAILABLE_PLSQL: Application unavailable. The message is shown from the PL/SQL block in p_unavailable_value.
UNAVAILABLE_URL: Application unavailable. Redirected to URL provided in p_unavailable_value.

🗒️ The parameter p_restricted_user_list is used when the value of p_application_status is RESTRICTED_ACCESS

🗒️ The parameter p_unavailable_value is used when the value of p_application_status is one of the following UNAVAILABLE_URL, UNAVAILABLE, UNAVAILABLE_PLSQL

For instance, if you want to make the application available only for specified users. Go to the SQL Workshop, then navigate to SQL Commands and run the following script.

begin
apex_util.set_application_status(
    p_application_id => <your app id>,
    p_application_status => 'RESTRICTED_ACCESS',
    p_unavailable_value => '<Message to be displayed>',
    p_restricted_user_list => upper('<comma-separated usernames>') ); 
end;

Application Build Status

You can also automate the Build Status by using SET_APP_BUILD_STATUS procedure.

APEX_UTIL.SET_APP_BUILD_STATUS( p_application_id IN NUMBER,
                                p_build_status in VARCHAR2 );

You can use the following build status for the parameter p_build_status

RUN_ONLY: The application can be run but cannot be edited by developers.
RUN_AND_BUILD: The application can be run and can also be edited by developers.

For instance, to change the Build Status of our application run the following code in the SQL Command

begin
    apex_util.set_app_build_status(
        p_application_id => 78187,
        p_build_status   => 'RUN_ONLY' );
end;

Tip#2 Application Availability using PL/SQL

Table of contents

Set Application availability using PL/SQL

Application Build Status

References