Introduction
APEX Form Regions and Interactive Grids are a great way to enable Create, Update and Delete (CRUD) functionality on a table without writing any code. Enabling CRUD functionality for a table is as easy as following the Create Page Wizard in APEX Builder.
The Challenge
A lot of the time, however, we need additional logic to run before or after the CRUD operation. Examples include:
- Send an email after the record is created or updated (e.g., Assignee changed)
- Update a second table when a record is created or updated
- Pushing the new record to a queue for integration
- Updating calculations in summary tables
Throughout this post, I will use an example where we need to send an email to the Assignee of a To Do item after the To Do is created or when the Assignee is changed. My examples show an Interactive Grid (IG) Region, but it looks and works the same with Form Regions.
After completing the No-Code Wizard to create an editable IG, a Save Process is created, which looks like the screenshot below. This is the starting point for the options that follow.
Option 1 - Second PL/SQL Process
One way of sending an email after a CRUD operation is to add a second PL/SQL process that is called after the IG or Form Region Process is run.
This option has the advantage of not disturbing the No-Code handling of the Region/IG CRUD processing but may not be the best solution.
For example, in our case, as well as APEX, we also want to be able to create, update and delete tasks via an ORDS REST service. If we go with the option one approach, we will have to write a PL/SQL CRUD handler for the table for ORDS to use anyway. We then need to create a send email PL/SQL API and call it from the new APEX PL/SQL process and the ORDS service.
Another more serious downside is that the second process will only run when we submit the page, and it will run just once, no matter how many rows were created, updated, and deleted. Furthermore, you will have to programmatically figure out which records were impacted, which could be impossible. So, this option is a no-go for IGs.
This option would be OK for a Form Region because we are dealing with just one record at a time, and we can tell which button the user clicked to find out if they Created, Updated, or Deleted the record.
Option 2 - Common PL/SQL Table Handler
A more robust approach is to create a table handler PL/SQL procedure (inside a package, of course). This procedure can then do the INSERT, UPDATE, or DELETE and call the send email PL/SQL procedure afterward. Also, APEX calls the handler once for each created, Updated, or deleted record. Best of all, you can call the same handler from your ORDS REST service and APEX, making it much easier to maintain.
So, how do we do this? We start by setting the 'Target Type' to 'PL/SQL code' for the wizard-generated region (see screenshot below).
As the Help Text for the 'PL/SQL code' target type indicates (see above screenshot), you can now add any code you like. Any form region or IG column fields are referenced using bind variables, e.g., :EMPNO for an IG or :P10_EMPNO for a form region. The :APEX$ROW_STATUS variable tells us whether the user Created, Updated, or Deleted the row.
❗Unfortunately, we are still not quite there, as the code is embedded in our APEX page. We still can't share this code between APEX and ORDS.
As much code as possible should be in PL/SQL packages; this improves performance and re-usability.
Option 2+ Common PL/SQL Table Handler in PL/SQL Package
We can enhance option two by encapsulating all of the logic into a PL/SQL procedure, as seen in the screenshot below.
Well, that looks nice and clean, but what does the code in the package look like?
Procedure task_crud_apex
The below procedure, task_crud_apex, is called from the APEX Process 'ToDo - Save Interactive Grid Data'. For an IG, it is called once for each record created, updated, or deleted (depending on how you set the process property Execution Scope).
The reason for this procedure is to gather the values passed to the APEX Process, populate a PL/SQL record, and then call the appropriate procedure to (C)reate, (U)pdate, or (D)elete the record. You could see this as an overhead, but it is invaluable in keeping PL/SQL complexity out of our APEX page.
The bind variables are captured using the V function, which is a wrapper for APEX_UTIL.GET_SESSION_STATE. This is the only way we can reference the bind variable values from within the PL/SQL package. We could add parameters to the task_crud_apex procedure and pass in the bind variables. e.g., p_task_number => :TASK_NUMBER, etc.). This would run fractionally faster, but this would add to the bulk of the code in the APEX process.
The create_task, update_task, and delete_task procedures are called from task_crud_apex. These procedures can also be shared directly with other code that may need to perform CRUD operations on this table. As you can see in the code comments, this is also where we can make a call out to perform other logic, in this case, send an email.
Conclusion
We have successfully minimized PL/SQL logic in the APEX application and moved it to a PL/SQL package where it can be more easily re-used.
We also discovered that APEX makes it easy to build useful functionality using simple Wizards. APEX also makes it easy to build on this baseline functionality and perform more advanced processing as your knowledge and requirements grow.
🔗 Read More
Please also take a look at my other posts on Oracle APEX:
