Scroll to top

Oracle APEX Tips and Tricks Series – Part 1


Kavya HT - June 13, 2024

Reading Time: 6 minutes

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:

  1. Sending email notifications from APEX with an attachment
  2. Making a POST API call using a Rest data connection
  3. 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.

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].

Kavya HT

Kavya is an Associate Consultant at Conneqtion Group. She has completed her graduation in BCA in Computer Science. She is proficient in Oracle APEX and PL/SQL.

Author avatar

Kavya HT

Kavya is an Associate Consultant at Conneqtion Group. She has completed her graduation in BCA in Computer Science. She is proficient in Oracle APEX and PL/SQL.

Related posts

Post a Comment

Your email address will not be published. Required fields are marked *