It’s been more than a month that we published some valuable information about Oracle APEX to share with the growing APEX community.
After having soaked in the scorching Indian Summer, staying indoors as much as possible, and with the onset of the monsoon season in India that provides a major relief to all, we start our monthly series of Oracle APEX Tips and Tricks of the month, that would be useful to the entire global APEX community.
Our intention is to highlight and focus on features that are widely used and a must-have in an APEX developer’s closet.
In our first series, Oracle APEX Tips & Tricks – 01, we will discuss the following useful APEX tips:
- Sending email notifications from APEX with an attachment
- Making a POST API call using a Rest data connection
- Exposing APIs from your schema via ORDS
1. Sending email notifications from APEX with attachments
Step 1: Create an email template
In the page designer -> Shared components, under User Interface, navigate to Email Templates.
Inside Email Templates click on Create Email Template button.
You will now see page to enter the template details.
Enter the Template Name and Email Subject of the email to be sent. The placeholder variables will be substituted dynamically from the page using this template.
You also need to provide the HTML code for the Body of your email template. A professionally designed and visually appealing html if plugged in the Body of the template gives a good user experience to the end user when they receive a notification.
As you can see here, we have used img tag but instead of specifying a URL as a source, we specify a content-id cid:conneqtion-logo-dark-1.png.
Click on Apply Changes button.
Now you have successfully completed creating an email template.
Next step, is to create a page specific process which sends an email to the recipient.
Step 2: Create a Page Process to send an email
Here we have created a page process of Type: Send E-Mail.
Under the Email Template option under Settings property select the email template that was created as a Shared Component.
Set Placeholder Values : point each placeholder to a respective Page Item or a fixed text value.
In this example, we set the values of following two placeholders dynamically:
EMP_EMAIL_TO and EMP_EMAIL_BODY to page items P4_EMP_NAME_TO and P4_EMP_BODY respectively.
To embed or display the images in your email body you need to add the below SQL query under Attachment SQL option.
We need to first upload the image in Shared Components – Static Application Files with the same file name as referenced in the email template <img src= “cid:conneqtion-logo-dark-1.png”>
In Attachment SQL: you need to write a select query on apex_application_files pointing to the exact attachment.
Similarly, blob attachments can be referenced from your custom tables in your schema.
Also Read – Oracle APEX Tips and Tricks Series Part 2
2. Making a POST API call using Rest Data Sources in Oracle APEX
Step 1: To make a POST API Call using REST Data Sources, navigate to Shared Components -> Rest Data Sources.
Step 2: To create a new Rest Data Source, click on Create.
By default, from Scratch option will be selected. After selecting either one of the options, click on NEXT button to proceed with the next step.
Step 3: Select the REST Data Source Type, Name and URL Endpoint of the REST Data Source.
Select REST Data Source Type to Oracle REST Data Services.
Step 4: Go to Advanced option.
Under the Advanced option, set Content-Type as a HTTP Header and toggle is Static to true.
Since this is a POST request, set the HTTP Method to POST and provide the Request Body for the POST request and click on Create Rest Data Source Manually.
The newly created REST Data Source appears as shown below.
The above Rest Data Source created can be used as a Page Process.
Type: Invoke API Under Settings: Type: Rest Source
Rest Source: Employees_Data Operation: POST
3. Exposing an API from your schema via Oracle Rest Data Services in APEX
In this example, we will expose a POST API to Update Employee Salary and Return updated Salary.
As we have shown the current example in apex.oracle.com, hence there was no need to enable ORDS on the schema as it was already enabled, but this will have to be enabled on your APEX instance.
Pre-requisite: ORDS should be enabled.
Enable ORDS on your schema (one-time activity)
begin
ORDS.enable_schema(
p_enabled => TRUE,
p_schema => ‘MYUSER1’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘hr’,
p_auto_rest_auth => FALSE
);
commit;
end;
Base URL for web service to refer: https://host:port/ords/hr/
Step 1: Navigate to RESTful Services under SQL Workshop in your workspace.
Step 2: In the RESTful Services page, navigate to Modules option and click on Create Module button.
Step 3: Create a New Module
Provide the Module Name as update employees and Base Path as create-emp/ and Create Module.
Step 4: Click on Create Template to create a new resource template.
Step 5: Provide the URI Template for Resource Template and click on Create Template.
Step 6: Create a Resource Handler by clicking on Create Handler.
Step 7: Select the Method to PUT and provide the PL SQL code for Source that will update and hike the employee salary as per the EMP_HIKE_PERCENTILE parameter that will be passed to the POST API.
DECLARE
v_emp_sal NUMBER;
v_emp_hike NUMBER;
BEGIN
SELECT emp_salary, emp_hike_percentile
INTO v_emp_sal, v_emp_hike
FROM EMPLOYEE_DATA
WHERE EMP_ID = :EMP_ID;
v_emp_sal := v_emp_sal + (:EMP_HIKE_PERCENTILE / 100) * v_emp_sal;
v_emp_hike := v_emp_hike + :EMP_HIKE_PERCENTILE;
UPDATE EMPLOYEE_DATA
SET EMP_SALARY = v_emp_sal,
EMP_HIKE_PERCENTILE = v_emp_hike
WHERE EMP_ID = :EMP_ID;
COMMIT;
:EMP_SALARY:=ROUND(v_emp_sal,2);
END;
Step 8: Scroll down the page and click on Add Row button to create a new Parameter.
Step 9: Enter Name as EMP_ID, Bind Variable as :EMP_ID, Access Method as IN , Source Type as HTTP Header and Data Type as INTEGER.
Repeat Step-8 to add the following additional Parameters.
Step 10: After creating the parameters click on Create Handler.
Now the above API can be invoked from any APEX Page Process after making a Rest Data Source connection to this API or from any PL/SQL code block within your APEX application or any other applications that needs access to your API.
If you have any questions or concern, please get in touch with us on [email protected].
