APEX is an intuitive tool and it is easy to dive straight in and begin developing after some basic tutorials. There are a few tips & tricks I have discovered along the way that I really wish I had found out about sooner, so I thought I would share them with you.
Most of your code will likely be written using a text editor (VSCode is widely considered as the most efficient and extendable) but there are times when you will have to code directly in the builder.
To activate auto-complete simply begin typing and then press <ctrl> + <space> and your options will appear.
This can be useful when you have more complex table names for example, and can prevent typos in your code.
When I joined Explorer I was urged to try out this extension and I’m glad I did – It is a veritable toolkit for the APEX developer, created by Matt Mulvaney – a hugely experienced and respected Oracle ACE and a senior development consultant here at Explorer.
Some of the features I use the most:
If you want to make a quick change in the in the code editor to a process/query/computation that has the ‘validate’ button
Usually, you have to click to validate, wait for the ‘Validation Successful’ message then click OK.
With tab lock enabled, you have the option of ‘Validate and close’
This seems like a small feature but it really does improve the UX of the editor modals.
A few clicks bring up a list of all the variables available to you on the page. Again, this is great for speed and prevents typos.
A handy feature that lets you sense check the current return value of an item without submitting the page.
A fairly standard requirement, but with no out-of-the-box solution how do you add checkbox functionality to your IR? I recommend you check out this blog from Jeff Kemp which gives a step by step guide I find myself revisiting frequently.
This useful JavaScript function allows you to generate a ‘confirm’ modal without creating an actual APEX modal dialog page. On confirm, the page is submitted and you can set the request value and even set page items – See an example of how to implement in my next tip…
When creating an IR I often need a row button to perform an action/link. Don’t worry about having to create and style a button from scratch – luckily, Universal Theme has you covered with the Button Builder.
So Let’s make a delete button to delete an employee on our IR.
Step 1) Style your button using the Button Builder
Step 2) Create a column for your button
Step 3) Populate the following attributes:
Step 4) Create a new hidden item to store the EMPNO to delete :PX_DELETE_EMP
Ensure the Settings on the item are:
Step 5) In ‘Link’ Attribute select
| Target | URL |
| URL | JavaScript:apex.confirm(“Are you sure you want to delete: #ENAME#?”,{request:”DELETE”,set:{“PX_DELETE_EMP”:#EMPNO#}});10 Things I Wish I Knew When I Started Using APEX |
When clicked a confirmation modal (JS) is displayed and if ‘OK’ is pressed the page will be submitted with the ‘DELETE’ request and the :PX_DELETE_EMP item is populated with the selected EMPNO.
Remember to replace ‘PX’ with your page number in the example.
Link Text/Attributes: Copy from Button Builder
Step 6) Create a Process to delete the employee using the PX_DELETE_EMP
And Voila!
A really simple but effective one, a ‘Clear’ link on any form item allowing the user to clear any pre-populated data in the item.
Simply enable Quick Picks from the Attributes of the item and populate as below.
If you have been using APEX for any length of time you will definitely know this one, but if you are just starting out it can be a bit confusing when your report doesn’t refresh after an update is performed on the data via a modal dialog.
You could submit the page on close of the dialog, but there is a more efficient way:
Step 1) On the report page, create a ‘Dialog Closed’ Dynamic Action and populate the following attributes:
| Identification | |
| Name | Close Dialog |
| When | |
| Event | Dialog Closed |
| Selection Type | Region |
| Region | <name of report region> |
Step 2) Create a True action with the following attributes:
| Identification | |
| Action | Refresh |
| Affected Elements | |
| Selection Type | Region |
| Region | <name of report region> |
And…that’s it! It is important that the Selection Type in Step 1 is the region/button etc that the modal was triggered from. So in our example we assume that an edit modal has been triggered from a link /button contained in an IR.
When working on a project, I was asked to create a form to enter invoice details, but the invoice lines had to be dynamic as there could be multiple. It is possible to achieve this using the apex_item package and a PL/SQL Dynamic Content Region but there had to be an easier way I thought. I was recommended the ‘APEX-MultiRow-Item’ Plugin (created by another Oracle ACE on our consultancy team Rodrigo Mesquita). This allowed me to easily add the dynamic item I required.
Step 1) Download and install the plugin from here
Step 2) Create a page item with the following attributes:
Step 3) That’s it!
On page submit the multiple values will be stored in the item, separated by ‘:’.
You can then use apex_string.split (docs) to return a table of type apex_t_varchar2, which you can then loop through.
Example of a proc to insert values in a table
When I first started using PL/SQL and APEX I didn’t know where to start when debugging my code. There are built-in debugging tools in SQL Developer and PL/SQL Developer that I won’t go into here, however for a simple ‘console.log’ type debug you can use the apex_debug package.
apex_debug.message(‘lv_variable_to_check: ‘||lv_variable_to_check); to the line in your code you want to check the value of the variable.
For example if I wanted to check the value of the :P3_DELETE_EMP in my delete process from earlier I would add this to the process:
For some handy tips on the debugger, check out another Explorer blog – Enhancing the Debugger
You can create Enterprise level applications using out of the box APEX and PL/SQL functionality, but there are times where you will want to implement some JavaScript in your application. There is an extensive list of JavaScript functions available for use in the Oracle JavaScript API Reference. You can also easily declare your own to use at page or application level. To do this, simply select the page level rendering and in the ‘JavaScript’ Attribute add your functions to the ‘Function and Global Variable Declaration’
Any functions declared here can be utilised anywhere JavaScript is allowed on that page.
If you are using more than the odd function, it is advised that you store your JavaScript in a separate file, which can be referenced from multiple pages.
To do this simply upload the .js file via
You can add the reference
If you want the entire application to have access to the JavaScript in the file you need to add the reference to Shared Components -> User Interface Attributes
Then in the JavaScript tab enter the file reference here:
I hope that if you are just starting your APEX journey, this has given you some insight and maybe a few techniques to try in your next application.
Author: Lee Burgess
Job Title: Oracle Development Consultant
Bio: Lee is a Development Consultant at DSP-Explorer. After a change of career, he began working with Oracle Technologies and specialising in SQL and PL/SQL Programming. His previous role involved developing an Enterprise application using APEX, and since then has never looked back. Here at DSP-Explorer, he is part of a highly skilled development team providing APEX development solutions and training to both UK and international businesses.