Jumpstarting ORDS REST API Development: A Comprehensive Guide for Beginners - Part 3

In the previous blog, we learned how to secure a REST API using OAuth2 and tested it using Postman. Now, in this blog post, we will take the next step and explore how to invoke a secured REST API using PL/SQL.

By the end of this blog post, you will have a clear understanding of how to integrate your PL/SQL applications with OAuth2-secured REST APIs, enabling seamless communication and secure data exchange.

So, let's dive into the world of PL/SQL and learn how to interact with OAuth2-protected REST API.

One of the greatest advantages of Oracle APEX is its collection of powerful APIs that simplify your development tasks, and one such API is APEX_WEB_SERVICE. This API allows us to easily invoke REST APIs and integrate them into our APEX applications, making our work much more convenient.

Before invoking the REST API, it is necessary to create an ACL entry for the external network service. For more detailed information on how to create an ACL entry, refer this blog. 

Following the same approach as we did while testing the REST API in Postman, let's proceed with the steps for generating an access token, including it in the request header, and finally invoking the REST API. 

Generate the Access Token:

By utilizing the OAUTH_AUTHENTICATE procedure of the APEX_WEB_SERVICE API, we can request an OAuth access token. This token, along with its expiration date, is stored in the global variable g_oauth_tokens. The g_oauth_tokens variable is of a record type with two subfields: token and expires.

BEGIN
    -- Get Access Token
    apex_web_service.oauth_authenticate(
        p_token_url     => 'https://g797c891abe3879-db1.adb.us-phoenix-1.oraclecloudapps.com/ords/apidemo/oauth/token',
        p_client_id     => 'wF97ihe7IrSUOsZ8E5_5Eg..',
        p_client_secret => 'nIvNXfY7z8zPsdz3Clioiw..');
        
    DBMS_OUTPUT.PUT_LINE('token:'|| apex_web_service.g_oauth_token.token);
    DBMS_OUTPUT.PUT_LINE('expires:'|| To_CHAR(apex_web_service.g_oauth_token.expires,'DD-MM-YYYY HH24:MI:SS'));
END;

Next, we need to add access token in the request header. 

To set the HTTP request header in Oracle APEX, we can utilize the G_REQUEST_HEADERS global variable from the APEX_WEB_SERVICE API.  By setting the request headers using G_REQUEST_HEADERS, we can include various headers such as "Content-Type",  "Authorization", or custom headers required by the API you are interacting with.

The G_REQUEST_HEADERS is an array of type APEX_WEB_SERVICE.T_HEADER and can be accessed and modified within your PL/SQL code to set specific headers for the HTTP request.

-- Set the request header
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Header-Name';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'Header-Value';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).NAME := 'Authorization'; 
APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).VALUE := 'Bearer [ACCESS_TOKEN]'; 

Here, we need to set the generated access token to the HTTP Authorization header using the G_REQUEST_HEADERS. 

-- Set the request header
APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE(); -- clear request header
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Authorization';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'Bearer '|| apex_web_service.g_oauth_token.token ;

Before setting the request headers for a new API call, it is important to clear the existing headers to ensure a clean slate. For more detail information, refer this blog from Martin. 

Alternative, we can also use SET_REQUEST_HEADERS procedure of APEX_WEB_SERVICE_API to set the HTTP request headers.

BEGIN
    -- Set the request header
    APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
        p_name_01        => 'Authorization',
        p_value_01       => 'Bearer '|| apex_web_service.g_oauth_token.token,
        p_reset          => TRUE  -- clear the request header array
        );
END;

In the final step, we can use the MAKE_REST_REQUEST function of the APEX_WEB_SERVICE API to invoke the REST API. This function allows us to send an HTTP request and retrieve the response from the REST API. The response is returned as a CLOB data type.

DECLARE
    v_response CLOB;
BEGIN
    -- invoke REST API to get single employee
    v_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
                    p_url         => 'https://g797c891abe3879-db1.adb.us-phoenix-1.oraclecloudapps.com/ords/apidemo/hr/v2/employees/999',
                    p_http_method => 'GET');
END;

Now let's put everything together.

DECLARE
    v_response CLOB; 

BEGIN
    -- Get Access Token
    apex_web_service.oauth_authenticate(
        p_token_url     => 'https://g797c891abe3879-db1.adb.us-phoenix-1.oraclecloudapps.com/ords/apidemo/oauth/token',
        p_client_id     => 'wF97ihe7IrSUOsZ8E5_5Eg..',
        p_client_secret => 'nIvNXfY7z8zPsdz3Clioiw..');
        
    -- Set request header
    APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE(); -- clear request header
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Authorization';
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'Bearer '|| apex_web_service.g_oauth_token.token ;
    
    -- Invoke REST API to get single employee
    v_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
                    p_url         => 'https://g797c891abe3879-db1.adb.us-phoenix-1.oraclecloudapps.com/ords/apidemo/hr/v2/employees/999',
                    p_http_method => 'GET');
                     
    -- Handle API RESPONSE
    -- Parse JSON response and handle it accorind to your requirement
    DBMS_OUTPUT.PUT_LINE(v_response);
END;

Make a POST request to the API to create an employee:

This code demonstrates how to authenticate, set headers, define the request body, and make a POST request.

DECLARE
  v_request_body CLOB;
  v_response     CLOB;
BEGIN
     -- 
     -- Get Access Token
     --
    apex_web_service.oauth_authenticate(
        p_token_url     => 'https://g797c891abe3879-db1.adb.us-phoenix-1.oraclecloudapps.com/ords/apidemo/oauth/token',
        p_client_id     => 'wF97ihe7IrSUOsZ8E5_5Eg..',
        p_client_secret => 'nIvNXfY7z8zPsdz3Clioiw..');     
    --
    -- Set Request Header
    --
    apex_web_service.g_request_headers.delete();
    apex_web_service.g_request_headers(1).name := 'Authorization';
    apex_web_service.g_request_headers(1).value := 'Bearer ' || apex_web_service.g_oauth_token.token;
    --
    -- Request body
    --
    v_request_body := '{
                        "employee_id": 999,
                        "first_name": "Rutvik",
                        "last_name": "Prajapati",
                        "email": "RPRAJAPATI",
                        "phone_number": "515.123.4444",
                        "hire_date": "2023/05/01",
                        "job_id": "AD_ASST",
                        "salary": 4400,
                        "commission_pct": null,
                        "manager_id": 101,
                        "department_id": 10
                       }';    
    --
    -- invoke webservice
    --
    v_response := APEX_WEB_SERVICE.make_rest_request(
                    p_url         => 'https://g797c891abe3879-db1.adb.us-phoenix-1.oraclecloudapps.com/ords/apidemo/hr/v2/employees/',
                    p_http_method => 'POST',
                    p_body        => v_request_body);

    DBMS_OUTPUT.PUT_LINE(v_response);
END;

In this blog, we learned how to generate an access token, include it in the request header, and effectively make REST API calls using PL/SQL code. With the concepts and techniques covered in this blog, you are well-equipped to incorporate REST APIs into your APEX applications and unlock the potential of seamless integration with external services. Embrace the power of PL/SQL and REST APIs to take your application development to new heights.