That’s why, we gathered key tips that improve your code quality so the apps you create are efficient and optimised. Here are 14 best practices that are often overlooked by APEX beginners, but bring tangible benefits. The tips come from experts such as Steven Feuerstein, Jeffrey Kemp, and Michelle Skamene, as well as from my experience with APEX platform and PL/SQL.

1. Limit the amount of code - write only queries that populate reports and tables

Write SQL code in Application Builder only if queries populate reports and tables. Even then, simplify these queries by using views and table functions for complex scenarios.

Tools such as ChGPT make code generation so easy that we tend to overuse it. We write query after query, copying the code from one process to another. In effect the query repetitions skyrocket and pop up in multiple places, forcing us to edit every single one if we need to make a change. That's why it's a good idea to create functions in the database. This approach lets you modify the code only once since the same block is stored in a single space. Additionally, if you want to reuse the code, simply call the created function.

2. Don't repeat the code

Avoid code repetition as much as possible. Keep the amount of code inside APEX to a minimum by moving it into PL/SQL packages. Move the code into stored program units instead of writing extensive anonymous processes and conditions. This allows us to keep things tidy and optimise the application at the same time. Jeffrey Kemp describes the advantage of keeping the code in the packages. He points out that the large amount of PL/SQL logic kept in his application led to code being dynamically compiled every time a page was loaded or processed. Moving this code into the database resulted in a single compilation. Thanks to that it was easier to spot duplicate code and to adjust it so that the same procedure was called from multiple pages.

3. Use the IDE to efficiently navigate your code

Using the IDE to format all SQL and PL/SQL statements, makes it significantly easier for you to read and recognise code. Although it’s not required by the APEX platform, use IDE when working with large amounts of code - it’s easier and will save you from many errors.

4. Transfer values via parameters

Pass element values via parameters - this let’s you see what you’re passing to other functions. For example, what Apex elements you are entering. If you call a function without parameters then you don't know what variables it accepts and which ones pass on, making it difficult to understand the code and the whole app.

5. Avoid using the V function

Avoid using the V function to obtain the variable’s value based on its name. Why? Because you can't see what arguments go into the function. If during the life of the app, the elements used change their names, the APEX environment won’t detect this and won’t notify you about the error. Use parameters instead of V functions. Here is an example: Let's create a function whose task is to return the value "Y" when the employee is someone's leader and the value "N" when he is not. We retrieve the value of the element on which we base our check using the function v.

CREATE OR REPLACE FUNTION is_lead_team
	RETURN CHAR
IS
	v_flag CHAR(1);
BEGIN
	SELECT
		CASE
			WHEN EXISTS (
				SELECT 1
				FROM PEQ_EMPLOYEE
				WHERE direct_manager_employee_id  = v(‘P7_USER_ID’)
			)
			THEN ‘Y’
			ELSE ‘N’
		END AS team_lead
	INTO
		v_flag
	FROM
		DUAL;
	RETURN v_flag;
END;

The function is prompt in this way:

--PL/SQL
RETURN is_team_lead();

In this case, we cannot see what arguments go into this function. If the elements change their names during the life of the app, the APEX environment won’t detect this and won’t notify us about an error. Instead of calling V functions, let’s pass all element values to functions and procedures via parameters.

CREATE OR REPLACE FUNTION is_lead_team (
	v_user_id IN VARCHAR2
)
    RETURN CHAR
IS
	v_flag CHAR(1);
BEGIN
	SELECT
		CASE
			WHEN EXISTS (
				SELECT 1
				FROM PEQ_EMPLOYEE
				WHERE direct_manager_employee_id  = v_user_id
			)
			THEN ‘Y’
			ELSE ‘N’
		END AS team_lead
	INTO
		v_flag
	FROM
		DUAL;
	RETURN v_flag;
END;

RETURN is_team_lead(:P20_USER_ID);

With the parameterised function, APEX can check any process or condition that calls this function whether the element name is misspelled (and therefore undefined) or not.

By reading the call of this function inside the app, anyone can immediately see that the function is based on user ID values, making code maintenance much easier.

6. Use bind variables to shorten the query

Use the bind variable :APP_USER, as it does not run PL/SQL. This practice shortens the query compilation time, because its execution will be done entirely in SQL."V" function forces the contexts to switch to PL/SQL for each record that matches the query condition.. When you are writing a query, use bind variable, like :APP_USER, instead of using V function, like v(‘APP_USER’). This practice will significantly reduce the compilation time. This is happening because “V” function forces the context to switch from SQL to PL/SQL for each record that matched query condition. With use of bind variable, we keep whole compilation in SQL.

SELECT task_name
FROM tasks
WHERE assigned_to=v('APP_USER');

Versus

SELECT task_name
FROM tasks
WHERE assigned_to=:APP_USER;

7. For constant values, use substitution strings

For fixed values, such as the app name, it is recommended to use substitution strings or static variables defined at the app level. A substitution string is a special denoted form of syntax, for example &APP_NAME, which represents a predefined value. The use of the substitution string allows constants to be dynamically referenced in different places in the app.

The appropriate use of substitution strings will make your app more flexible, allowing you to easily adjust the values of constants at a project-wide level. In this way, you avoid entering the same data multiple times in different parts of the app, making the code more consistent and easier to maintain. Make a conscious effort to use substitution strings, so that the apps you create are more efficient and customisable.

8. BUT - if possible - avoid string substitution in queries

To optimise query performance in Oracle APEX, avoid substitution strings and instead use bind variables. For example :VARIABLE instead of $VARIABLE. Switching to variable bind significantly saves resources as it allows Oracle to efficiently reuse queries. Don't do this

'f?p=&APP_ID.:PAGE:&SESSION.::&DEBUG.:'

Do this instead:

'f?p=' || :APP_ID || ':PAGE:' || :APP_SESSION || '::' || :DEBUG || ':'

Bind variable allows the same query to be reused minimising the memory load on the shared pool. In effect, you’ll avoid flooding memory with unique instructions that cannot be effectively reused.

9. Use the #TIMING# variable to identify slow report regions

Use the #TIMING# variable in the footer of a report region if you want to display the time it runs. By using this variable you’ll identify report regions that run particularly slow on the page. Thanks to the runtime information, you’ll optimise app performance, as you’ll be able to focus on areas that require deeper analysis. With this simple tip, you’ll quickly identify potential problems and effectively optimise your reports in Oracle APEX.

Declared variable #TIMING# is displayed under the table as seen below:

10. Use declarative logic to improve performance

Don’ write code where it is not necessary to avoid over-coding. Use declarative elements in the APEX app builder, especially when defining conditions for displaying elements on a page. Focus on areas that require deeper analysis to optimise app performance. Avoid over-coding by not writing code unless it’s necessary.

To determine when specific elements should be visible, use the predefined condition and validation types available in APEX. With the use of built-in elements you’ll reduce the amount of code and improve the performance of the app, as this logic is an integral part of the framework.

11. Use Build Options instead of Server Side Conditions so that conditions are not lost.

Instead of setting the conditions for each component in Server Side Condition to "Never", use "Build Options" with the status "Exclude" available in Shared Components. "Build Options" apply to the different components of the page and are used to evaluate the effects of each change.

“Status” options under 'Build Options': Include: Enable this feature or component. Related application components are enabled and included in the application. Exclude: Do not include this function or component. Related application components are excluded from the app.

Created “Build Option” can then be applied in Configuration, available under region-related options. This way of managing build options avoids losing previously defined conditions in Server Side Condition. As you make changes, run the page with each new build and monitor the effects. You’ll effectively optimise the app without losing the previous conditions.

12. Use Activity Monitor to optimise your application

If our application seems to be running slowly and we do not know the cause, it is worth checking Activity Monitor, which can provide valuable information. There are several useful reports available in the Activity Monitor like 'By Weighted Page Performance'.

Choose Activity Monitor from the list:

Next choose By Weighted Page Performance in Page View Analysis :

In this report, each activity in the workspace and application is recorded with information about the user, date, timestamp, application, page_id and most importantly, elapsed time. Focuse on pages that show a high number of 'events' (i.e. frequent accesses) and a high average viewing time. Activity Monitor allows convenient analysis from an interactive report. However, if we need more detailed information, we can access the full data via the APEX_WORKSPACE_ACTIVITY_LOG view.

Check the Activity Monitor regularly to identify areas for optimisation in our app.

13. Improve performance with Region Caching

Enable the Server Cache option available in the region settings to improve the app performance. This is especially useful for dashboards frequently visited by many users that don’t require a refresh every time the page is displayed.

Caching is disabled by default. When enabled we can configure a caching timeout. Setting the appropriate time allows you to optimise the performance of the dashboard. The higher the value of the caching timeout, the better.

To inform users of the last data refresh when caching is enabled, use the APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE function.

Region Caching is an effective tool to reduce unnecessary data refreshes each time a page is displayed, resulting in a faster and more efficient use of Oracle APEX applications.

14. Check your code with APEX Advisor

APEX Advisor is a program that analyses your code for errors and bad practices. It runs pre-written automated tests to check: ● programming errors ● security issues ● warnings ● performance ● usability, etc.

Depending on the size of the app and the number of checks selected the time to generate the results may vary. Once the results are ready, the program displays each check marked as a problem.

Every result contains detailed information about the control, including:

• location in the application,
• attribute checked,
• category and type of check,
• a message explaining why the error was flagged,
• a value - this could be an invalid query or link.

The "View" button will take you to the source of the error in the page builder, making it easier and quicker to repair any problems detected.

The above tips from experts will improve your coding technique and at the same time make your APEX apps more efficient.