Introduction
Until APEX 22.2, the primary method for calling code in a PL/SQL package from an APEX page process was to use the Execute Code page process type. In this post, I will describe the Invoke API process type, which allows you to call PL/SQL procedures and functions declaratively.
Example Use Case
Throughout this post, I will use an example of a simple APEX form that allows you to create employee records. After creating an employee, I need to call a PL/SQL procedure to onboard the employee (provision a laptop, send a welcome email, etc.).
The onboarding process is initiated by calling the procedure onboard_employee in the package cn_blog_demo_pk.
PROCEDURE onboard_employee
(employee_id IN NUMBER,
laptop_type IN VARCHAR2) IS
BEGIN
apex_debug.info('employee_id [%s], laptop_type[%s]', employee_id, laptop_type);
END onboard_employee;
Before the Invoke API Process
Before the Invoke API process type came along, we would have created an Execute Code process type, something like this:
Setting Up an Invoke API Process
Perhaps the best way to illustrate the new approach is via a short video. The video below shows me creating a new Invoke API page process to call the onboard_employee procedure declaratively.
As you can see, the process of selecting the PL/SQL procedure and passing parameters from the APEX page to the procedure was completely declarative.
💡Although you can select a Type of PL/SQL Procedure or Function in the Process Settings, please don't. I advise you to keep all PL/SQL procedures and functions in a package. This post lists some of the reasons why.
Parameters
There are several options for populating the parameters of the underlying PL/SQL API; these are:
Static Value - Set the parameter to a Static Value.
Item - Set the parameter to the value held in the session state for an APEX Item.
SQL Query (return single value) - Set the parameter to the first value returned from a SQL Query.
Expression - Set the parameter to the result of an Expression.
Function Body - Set the parameter to the value returned from a Function Body.
Preference - Set the parameter to the value of an APEX Preference.
API Default - Use the value defined as default in the API parameter declaration.
Null - Set the parameter to NULL.
💡APEX will automatically set the API parameters to page items from the current page if their names match.
Booleans
The Invoke API process type handles boolean types quite nicely. If I were to add a boolean parameter to my underlying procedure as follows:
PROCEDURE onboard_employee
(employee_id IN NUMBER,
laptop_type IN VARCHAR2,
international IN BOOLEAN);
When I re-sync the parameters in APEX, I can map a true false value from the page item (which is a Switch-type page item in this case) to indicate what value represents true and what value represents false. In this case, if the item P10_INTERNATIONAL_EMP has a value of Y, the Invoke API Process will pass TRUE to the underlying procedure.
Out Parameters
If you have OUT (or IN OUT) parameters in the underlying PL/SQL API, these values will be passed to the mapped page item when the API completes. For example, if I add the x_message as an OUT parameter as follows:
PROCEDURE onboard_employee
(employee_id IN NUMBER,
laptop_type IN VARCHAR2,
international IN BOOLEAN,
x_message OUT VARCHAR2);
After re-syncing the parameters, I can map the value of the message OUT parameter to a page item.
💡If you don't need to do anything with an out parameter, you can just flip the Ignore Output switch, and APEX will generate a dummy variable to receive the out value, and you won't have to assign it to anything.
Changing the Underlying PL/SQL API
As you would expect, an Invoke API page process will break if you change the underlying PL/SQL procedure or function.
💡Hopefully, you are using the APEX Error Handling Function, so your users do not see these kinds of ugly and potentially security risky error messages.
If the name of the underlying package, procedure, or function changes, you must re-select it in the region settings and re-set up the parameters.
If you change the underlying PL/SQL API parameters, you can re-sync the parameters in the Invoke API process by right-clicking on the Parameters section and clicking Synchronize Parameters.
Why Bother
You may wonder why you should bother with Invoke API processes. As you can see from the examples above, we did not have to write much code to call a PL/SQL API before the Invoke API process came along.
While Invoke API process types do not introduce groundbreaking functionality, they make things easier for citizen developers and encourage best practices for experienced developers.
Citizen Developers can now declaratively select a procedure and populate its parameters. Having said that, someone (presumably an experienced developer) has to create the PL/SQL package and logic.
Experienced Developers are forced to put all their code in a PL/SQL package. I don't know about you, but when I need to change an Execute Code type process quickly, I am tempted to add the code to the PL/SQL block in the page process instead of opening the package and adding the code there. Invoke API processes will end that practice, and I am glad.
Conclusion
In this post, I introduced you to the Invoke API page process. I do not think the Invoke API process is a game changer, but it supports best practices by forcing you to keep your code in PL/SQL packages.
APEX 23.1 Addendum
I wrote this post before APEX 23.1 was announced. The post is still relevant but misses out on a new feature where you can now Invoke REST Sources. It works the same way as invoking PL/SQL APIs, i.e., you select a REST Source from a list of values, and the REST Source Parameters are displayed so you can easily pass in parameters.
🔗 Read More
- 🩳 APEX Shorts
- 📝 APEX Posts
