Overview :
The objective is to enhance Oracle APEX Application Availability through the implementation of PL/SQL code, ensuring uninterrupted and reliable operation by monitoring, managing, and responding to potential factors that may affect the availability of Oracle Application Express (APEX) applications.
Technologies and Tools Used :
The following technology has been used to achieve the same.
- Oracle Apex
- PLSQL
Use Case :
In the context of Oracle APEX application development, utilizing PL/SQL is crucial for ensuring high availability. This involves implementing efficient error handling, real-time monitoring, failover mechanisms, and database optimizations. PL/SQL plays a pivotal role in crafting a robust and reliable infrastructure to minimize downtime and guarantee uninterrupted access to web applications built on the Oracle APEX platform.
Architecture :
Steps :
The APEX_UTIL.SET_APPLICATION_STATUS and APEX_UTIL.SET_APP_BUILD_STATUS procedures are used in Oracle Application Express (APEX) to control the availability and build status of an application. Here are brief explanations and steps for using these procedures:
APEX_UTIL.SET_APPLICATION_STATUS:
Purpose: Sets the availability status of an APEX application.
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)
);
Parameters:
- p_application_id
- The ID of the application.
- p_application_status
- The new status of the application.
- p_unavailable_value
- The message or URL to be displayed when the application is unavailable.
- p_restricted_user_list
- Comma-separated list of usernames when the status is ‘RESTRICTED_ACCESS’.
Possible values for p_application_status:
- AVAILABLE
- Application is available with no restrictions.
- AVAILABLE_W_EDIT_LINK
- Application is available with the Developer Toolbar shown to developers.
- DEVELOPERS_ONLY
- Application only available to developers.
- RESTRICTED_ACCESS
- Application available only to users in p_restricted_user_list.
- UNAVAILABLE
- Application unavailable, showing the message in p_unavailable_value.
- UNAVAILABLE_PLSQL
- Application unavailable, showing the message from the PL/SQL block in p_unavailable_value.
- UNAVAILABLE_URL
- Application unavailable, redirected to the URL provided in p_unavailable_value.
Script :
begin
apex_util.set_application_status(
p_application_id => 198510,
p_application_status => ‘RESTRICTED_ACCESS’,
p_unavailable_value => ‘application not available for you’,
p_restricted_user_list => ‘ajith’ );
end;
- In this example, the application with ID 198510 is configured to be in ‘RESTRICTED_ACCESS’ status, meaning it is available only for the user with the username ‘ajith’.
- If any other user tries to access the application, they will see the message ‘application not available for you’, indicating that the application is restricted.
- If you want to grant access again to the APEX application using the apex_util.set_application_status procedure, you can change the p_application_status to a value that allows broader access, such as ‘AVAILABLE’,Here’s an example:
BEGIN
apex_util.set_application_status(
p_application_id => 198510,
p_application_status => ‘AVAILABLE’,
p_unavailable_value => NULL, — Set to NULL if not needed
p_restricted_user_list => NULL — Set to NULL if not needed
);
END;
APEX_UTIL.SET_APP_BUILD_STATUS:
Procedure:
APEX_UTIL.SET_APP_BUILD_STATUS(
p_application_id IN NUMBER, — The application ID
p_build_status IN VARCHAR2 — The new build status
);
Parameters:
- p_application_id
- The ID of the application.
- p_build_status
- The new build status of the application.
Possible values for p_build_status:
- RUN_ONLY
- Application can be run but cannot be edited by developers.
- RUN_AND_BUILD
- Application can be run and can also be edited by developers.
BEGIN
apex_util.set_app_build_status(
p_application_id => 198510,
p_build_status => ‘RUN_ONLY’
);
END;
BEGIN
apex_util.set_app_build_status(
p_application_id => 198510,
p_build_status => ‘RUN_AND_BUILD’
);
END;
Conclusion :
The APEX_UTIL.SET_APPLICATION_STATUS procedure in Oracle APEX is used to manage the availability and access control of an application. It allows you to set various statuses, such as ‘AVAILABLE’ or ‘RESTRICTED_ACCESS,’ with customizable messages and restricted user lists. The APEX_UTIL.SET_APP_BUILD_STATUS procedure controls the build status of an application, determining whether it can be edited by developers. Both procedures offer flexibility in automating application deployment and access configurations.
