Use Substitution Strings to Standardize Code and avoid boilerplate

Introduction

I decided to write this article because it's not uncommon to see code with excessive boilerplate, which can become a headache every time a parameter needs to be changed. This article aims to introduce you to Substitution Strings and demonstrate how you can use this powerful tool to avoid code repetition.

What are substitution strings?

They are basically application-level variables whose values are predefined in the application's definition. You can use these variables within HTML and PL/SQL as if they were page or application items.

Substitution strings vs. Application items

Application items work similarly, but they require value assignment to receive the desired value, whereas application substitution strings are predefined.

When should you use one over the other?

Application items can be seen as substitution strings on steroids because they can be defined more flexibly and their values can be changed at runtime. Substitution strings, however, are fixed and cannot have their predefined values changed. Therefore, use:

Substitution strings to store constant values that can be predefined before the session is established.
Application items to store non-constant values and/or values that can only be defined upon session creation, with the drawback of needing to be filled every time a session is started.

Practical use in REST calls

When you don't need to access various APIs and/or don't have authentication information saved in a table, using Substitution Strings can be very interesting!

Scenario: Accessing the Google Books API dynamically using provided credentials.

Defining values

In the App Builder, go to:

Shared Components > Edit Application Definition

In the Substitutions tab, you will find a small form where you fill in the string name and its value:

All the information for API authentication was defined here, allowing us to retrieve it throughout the application.

In my utilities Package, I define a new Type to handle API credentials.

CREATE OR REPLACE PACKAGE "PKG_UTILS" AS
  TYPE api_key_credentials_type IS RECORD (
    subdomain VARCHAR2(1000),
    api_key VARCHAR2(100),
    api_name VARCHAR2(20) DEFAULT 'key',
    host VARCHAR2(1000)
  );

  FUNCTION fnc_get_google_books_credentials(APP_ID IN VARCHAR2 DEFAULT v('APP_ID')) RETURN api_key_credentials_type;
END "PKG_UTILS";
/

Function explanation

Let's see what the function fnc_get_google_books_credentials is doing:

CREATE OR REPLACE PACKAGE BODY "PKG_UTILS" AS
  FUNCTION fnc_get_google_books_credentials(APP_ID IN VARCHAR2 DEFAULT v('APP_ID')) RETURN api_key_credentials_type IS
    l_credentials api_key_credentials_type;
  BEGIN
    -- For calls from outside and inside the application, use:
    SELECT 
      (SELECT SUBSTITUTION_VALUE FROM APEX_APPLICATION_SUBSTITUTIONS WHERE APPLICATION_ID = APP_ID AND SUBSTITUTION_STRING = 'GOOGLE_BOOKS_API_SUBDOMAIN'),
      (SELECT SUBSTITUTION_VALUE FROM APEX_APPLICATION_SUBSTITUTIONS WHERE APPLICATION_ID = APP_ID AND SUBSTITUTION_STRING = 'GOOGLE_BOOKS_API_KEY')
    INTO 
      l_credentials.subdomain, l_credentials.api_key
    FROM dual;

    -- You can also use V(substitution_string) if your call is from within the application:
    /*
    l_credentials.subdomain := V('GOOGLE_BOOKS_API_SUBDOMAIN');
    l_credentials.api_key := V('GOOGLE_BOOKS_API_KEY');
    */     
    RETURN l_credentials;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_credentials.subdomain := NULL;
      l_credentials.api_key := NULL;
      l_credentials.api_name := NULL;
      l_credentials.host := NULL;
      RETURN l_credentials;
  END fnc_get_google_books_credentials;
END "PKG_UTILS";
/

Since the function returns the type we defined, we have access to all credentials in a single variable, making the code cleaner and more readable.

Making a REST request

Now, let's make a REST request to return books by a specific author, applying the logic described above:

I created a new Package focused on the Google Books API so each function could return what was expected.
In this Package, I defined two more Types since this function will return a pipelined table (the types can also be created as a database object, not necessarily within the package).
Finally, I define the function get_books_by_author that will return all occurrences from the API as a pipelined table.

CREATE OR REPLACE PACKAGE PKG_GOOGLE_BOOKS_API AS
  TYPE book_type IS RECORD (
    title          VARCHAR2(4000),
    authors        VARCHAR2(4000),
    publisher      VARCHAR2(4000),
    published_date VARCHAR2(4000)
  );

  TYPE book_table IS TABLE OF book_type;

  FUNCTION get_books_by_author(p_author IN VARCHAR2) RETURN book_table PIPELINED;
END PKG_GOOGLE_BOOKS_API;
/

Package body

CREATE OR REPLACE PACKAGE BODY PKG_GOOGLE_BOOKS_API AS
  FUNCTION get_books_by_author(p_author IN VARCHAR2) 
  RETURN book_table PIPELINED IS
    l_url             VARCHAR2(4000);
    l_books_response  CLOB;
    -- Fetching the credentials type from the utilities package
    l_api_credentials PKG_UTILS.api_key_credentials_type; 

    CURSOR c_books IS
      SELECT title, authors, publisher, published_date
      FROM JSON_TABLE(
        l_books_response,
        '$.items[*]'
        COLUMNS (
          title          VARCHAR2(4000) PATH '$.volumeInfo.title',
          authors        VARCHAR2(4000) PATH '$.volumeInfo.authors[0]',
          publisher      VARCHAR2(4000) PATH '$.volumeInfo.publisher',
          published_date VARCHAR2(4000) PATH '$.volumeInfo.publishedDate'
        )
      );
  BEGIN
    -- Fetch the API credentials as we saw before
    l_api_credentials := PKG_UTILS.fnc_get_google_books_credentials(100);

    -- Defining the search URL
    l_url := l_api_credentials.subdomain || 'inauthor:' || UTL_URL.escape(p_author);
    -- Setting the request headers
    apex_web_service.g_request_headers.delete();
    apex_web_service.g_request_headers(1).name := l_api_credentials.api_name;
    apex_web_service.g_request_headers(1).value := l_api_credentials.api_key; 

    l_books_response := APEX_WEB_SERVICE.make_rest_request(
      p_url => l_url,
      p_http_method => 'GET',
      p_wallet_path => 'YOUR_WALLET_PATH',
      p_wallet_pwd => 'YOUR_WALLET_PASSWORD'
    );

    IF apex_web_service.g_status_code = 200 THEN 
      FOR r_book IN c_books LOOP
        PIPE ROW (r_book);
      END LOOP;
      RETURN;
    ELSIF apex_web_service.g_status_code IN (400,404) THEN
      RAISE_APPLICATION_ERROR(-200002, 'Error fetching data '||apex_web_service.g_status_code); 
    END IF;
  EXCEPTION 
    WHEN OTHERS THEN
      PKG_TRACE_LOGS.PRC_GET_PROCESS_LOG(P_PROCESS_NAME => 'PKG_GOOGLE_BOOKS_API.get_books_by_author',
                                         P_ERROR => SQLERRM,
                                         P_FORMAT_ERROR_BACKTRACE => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                                         P_PARAMETERS => 'Key: '||NVL(l_api_credentials.api_key, 'null')||' subdomain: '||NVL(l_api_credentials.subdomain, 'null')||' host: '||l_api_credentials.host||' api_name:'||l_api_credentials.api_name);
  END get_books_by_author;
END PKG_GOOGLE_BOOKS_API;
/

Using the function fnc_get_google_books_credentials we dynamically retrieve the API credentials, avoiding boilerplate and non-standardized code.
We use the credentials throughout the API call setup.
We Convert the JSON response into a pipelined table for proper return.

Final result:

Conclusion

We have seen that substitution strings are a powerful tool in Oracle APEX that allows us to store predefined values and use them throughout the application context. Their main advantages are eliminating boilerplate, standardizing code and REST calls, and centralizing frequently used information in one place.

https://giphy.com/gifs/thats-all-folks-upg0i1m4DLe5q

APEX Tip: Standardize and Simplify REST Calls Using Substitution Strings