10 Things I Wish I Knew When I Started Using APEX

Lee Burgess Oct 6, 2020 12:32:00 PM

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.

10 Things I Wish I Knew When I Started Using APEX Auto complete on builder

This can be useful when you have more complex table names for example, and can prevent typos in your code.

Tab lock Chrome extension

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 Oracle APEX Validate and close

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

Oracle APEX Code Editor

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.

10 Things I Wish I Knew When I Started Using APEX

 

Accessing an exhaustive list of substitution strings

Oracle APEX 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.

Oracle APEX Return Value of an Item

Links to useful resources

Oracle APEX 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

Oracle APEX adding a confirm action

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

Oracle APEX Mega Menu Demo 1

Step 2) Create a column for your button

Oracle APEX adding a row button in an IR (2)

Step 3) Populate the following attributes:

Oracle APEX adding a row button in an IR (3)

Oracle APEX security

Step 4) Create a new hidden item to store the EMPNO to delete :PX_DELETE_EMP

Ensure the Settings on the item are:

Oracle APEX settings

Step 5) In ‘Link’ Attribute select Oracle APEX no link definedand Oracle APEX adding a row button in an IR and populate the link builder: 

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

Oracle APEX link text attributes

Step 6) Create a Process to delete the employee using the PX_DELETE_EMP

Oracle APEX delete employee

Oracle APEX server side condition

And Voila!

Oracle APEX end product

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.

Oracle APEX quick picks

Oracle APEX quick picks

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:

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.

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.

Oracle APEX dynamic-row item plugin

Step 1) Download and install the plugin from here

Step 2) Create a page item with the following attributes:

Oracle APEX plugin identification

Step 3) That’s it!

On page submit the multiple values will be stored in the item, separated by ‘:’.

10 Things I Wish I Knew When I Started Using APEX

You can then use apex_string.split (docs) to return a table of type apex_t_varchar2, which you can then loop through.

Oracle APEX string split

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:

Oracle APEX basic debugging

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

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’

Oracle APEX using JavaScript in your application

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 Oracle APEX shared components

You can add the reference Oracle APEX app images to the file URLs in the page attributes of any pages you want to utilise the JavaScript in.

Oracle APEX JavaScript file URLs

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:

Oracle APEX file URLs app images

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.