Tip#2 Application Availability using PL/SQL

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 developersDEVELOPERS_ONLY: Application only available to developers.RESTRICTED_ACCESS: Application only available to users inp_restricted_user_list.UNAVAILABLE: Application unavailable. The message is shown inp_unavailable_value.UNAVAILABLE_PLSQL: Application unavailable. The message is shown from the PL/SQL block inp_unavailable_value.UNAVAILABLE_URL: Application unavailable. Redirected to URL provided inp_unavailable_value.
🗒️ The parameter
p_restricted_user_listis used when the value ofp_application_statusisRESTRICTED_ACCESS🗒️ The parameter
p_unavailable_valueis used when the value ofp_application_statusis one of the followingUNAVAILABLE_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;
References
AI tools were used to help draft portions of this work.
About the author
I'm Mohamad, a versatile software developer with a passion for creating innovative solutions and solving complex problems. Throughout my career, I've had the opportunity to work on a variety of projects and technologies, such as JavaScript, Oracle APEX, PL/SQL, and SQL. I'm not just a developer; I'm a problem solver who enjoys tackling challenges head-on. I thrive in collaborative environments and am constantly exploring new technologies to stay at the forefront of the industry.






