10 Things I Wish I Knew When I Started Using APEX

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.

Auto complete on builder

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.

Tab lock Chrome extension

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:

Validate and close

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’ so if the validation is successful then the editor will close automatically, otherwise the errors will display as normal.

This seems like a small feature but it really does improve the UX of the editor modals.

Accessing shortcuts to all items on the page

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.

Accessing an exhaustive list of substitution strings

Checking the return value of an item (before submit)

A handy feature that lets you sense check the current return value of an item without submitting the page.

Links to useful resources

Checkboxes in IRs & Select/Deselect all

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.

Adding a ‘Confirm’ action dialog using apex.confirm

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…

Adding a row button in an IR

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 and Oracle APEX adding a row button in an IR and populate the link builder: 

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!

Quick picks – Clear Form Item

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.

Close dialog refresh

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:

Step 2) Create a True action with the following attributes:

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.

Dynamic Multi-Row Item Plugin

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

Basic Debugging

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.

1. Simply add:

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:

1. Then turn on Debug mode in your app
2. In the app – Perform the action that triggers the process.
3. Select and select the triggered process to view the log
4. You will see the debug printed out after the process in the log

For some handy tips on the debugger, check out another Explorer blog – Enhancing the Debugger

Using JavaScript in your application

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 to the file URLs in the page attributes of any pages you want to utilise the JavaScript in.

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.