Introduction

One of the joys of working with APEX and ORDS is the ability to develop great-looking web apps and host REST APIs on the same platform. I have developed many Oracle REST Data Services (ORDS) REST APIs over the years, and one key factor in building a successful API is good documentation. Swagger (now Open API) has been my go-to tool for creating documentation for REST APIs for many years.

Swagger Vs. Open API

You will hear these terms used almost interchangeably. Read this post to understand the Difference Between Open API and Swagger.

Authoring Open API Documents

There are plugins for Visual Studio Code, but I typically use The SmartBear Swagger Editor to create my Open AI REST API Documentation.

The editor allows you to author documents in YAML or JSON. It also validates the YAML/JSON as you type, so you can be sure that the syntax is correct.

ORDS Generated Open API Docs

ORDS generates basic Open AI documentation out of the box.

ORDS Metadata Catalog

ORDS provides a URL that allows you to view metadata about a particular ORDS-enabled Scheme. If you are using SQL Developer Web, then navigate to the REST area, and you should see a link to the Metadata Catalog.

Note: To access the ORDS Metadata catalog URL, you must set up your ORDS-enabled schema with the option p_auto_rest_auth set to FALSE. You can always set it back to FALSE once you have the necessary documentation.

BEGIN
  ords.enable_schema
   (p_enabled             => TRUE,
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'secret',
    p_auto_rest_auth      => FALSE);  -- Must be FALSE for Metadata Catalog
  COMMIT;
END;

If you click on the Metadata Catalog link, you will see a JSON document similar to the following:

{
    "items": [
      {
        "name": "APEX Developer Blogs",
        "links": [
          {
            "rel": "describes",
            "href": "https://apps.cloudnueva.com/ords/api/apex_developer_blogs/posts"
          },
          {
            "rel": "canonical",
            "href": "https://apps.cloudnueva.com/ords/api/metadata-catalog/apex_developer_blogs/posts",
            "mediaType": "application/json"
          },
          {
            "rel": "alternate",
            "href": "https://apps.cloudnueva.com/ords/api/open-api-catalog/apex_developer_blogs/",
            "mediaType": "application/openapi+json"
          }
        ]
      }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 3,
    "links": [
      {
        "rel": "self",
        "href": "https://apps.cloudnueva.com/ords/api/metadata-catalog/"
      },
      {
        "rel": "first",
        "href": "https://apps.cloudnueva.com/ords/api/metadata-catalog/"
      }
    ]
  }

The alternate href in the JSON points to an Open API-compatible JSON document. For example, the alternate href in the JSON above https://apps.cloudnueva.com/ords/api/open-api-catalog/apex_developer_blogs/ returns the Open API compatible JSON for the APEX Developer Blogs REST API.

If you import that URL into the Open API Editor, you will see the documentation that ORDS provides based solely on the definition of your ORDS Templates and Handlers.

The Problem

When ORDS generates its documentation, it does so based on the Templates and Handlers you have defined. If you have a Collection Query type Handler, then ORDS can look at the SQL and determine the datatypes of the fields.

In my opinion, this is a great start, but it does not go far enough in creating complete API documentation. It also does not cover POST handlers, as there is no way ORDS can inspect the PL/SQL code you have behind a POST handler and determine accurate documentation for it.

When I create REST API documentation for ORDS services, I start with the ORDS-generated documentation and build on it using the SmartBear editor.

Hosting Open API Docs with ORDS

My approach involves the following steps:

1. Create a basic APEX Admin App to maintain the YAML documents that you have authored using the SmartBear editor. Copy and Paste the completed YAML from the SmartBear tool into your APEX Admin App.

2. Create an ORDS REST API to serve the YAML.

3. Create a second ORDS REST API to serve an HTML page, which renders Open API User Interface.

I will cover each of these steps in the following sections.

1 - Admin App

I won't go into much detail here. You essentially need a table (and an APEX Page to maintain it) with columns to reference and store your YAML documents.

create table cn_open_api_docs (
    id               number generated by default on null as identity
                     constraint cn_open_api_docs_id_pk primary key,
    api_code         varchar2(30 char)
                     constraint cn_open_api_docs_api_code_unq unique not null,
    api_name         varchar2(50 char) not null,
    open_api_yaml    clob not null
);

ORDS REST API to Serve YAML

We first need an API to return the Open API Documentation YAML from our table. This involves creating a simple ORDS GET Handler based on PL/SQL code.

Template

Create a new ORDS Template, including a URI Parameter (api_code) to capture the code for the document you want to serve.

Handler

Create a new handler to capture the YAML document you want to serve up.

The source code for the handler looks like this. You may have different tables, etc., but the principal is simply to emit the YAML document for the document being requested.

DECLARE
  l_api_code       cn_open_api_docs.api_code%TYPE;
  l_open_api_yaml  cn_open_api_docs.open_api_yaml%TYPE;
BEGIN
  -- Get the passed in API Code.
  l_api_code := UPPER(:api_code);

  -- Get the Open API YAML for the API Code.
  SELECT open_api_yaml INTO l_open_api_yaml
  FROM   cn_open_api_docs
  WHERE  api_code = l_api_code;

  -- Set Return Headers.
  :access_control_allow_origin := '*';
  :access_control_methods := 'GET, POST, DELETE, PUT';
  :access_control_allow_headers := 'Content-Type, api_key, Authorization';
  -- Set return Mime Type as YAML.
  owa_util.mime_header('application/x-yaml', true);

  -- Emit the YAML Document
  apex_util.prn
   (p_clob   => l_open_api_yaml,
    p_escape => FALSE);
END;

You can test the API from Postman:

ORDS REST API to Serve Open API UI

Next, we must create a REST API to serve the Open API UI via Swagger UI (also developed by SmartBear). Yes, I realize Swagger has become Open API, but you will still see many folks using these terms interchangeably.

Swagger UI is a combination of JavaScript and CSS that allows you to display your Open AI YAML (or JSON) in a user-friendly interface.

HTML

We will create an ORDS Handler to serve an HTML page that loads Swagger UI JS and CSS (from a CDN) and call the ORDS YAML REST API to fetch the YAML used to render the documentation.

The HTML below is based on the example referenced in the Swagger UI Installation Documentation. I have included comments to explain certain key elements of the HTML.

<!DOCTYPE html>
<html>
<head>
    <title>Open API Documantation</title>
    <!-- CDN Location of the Swagger UI CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/swagger-ui-dist@latest/swagger-ui.css">
</head>
<body>
    <!-- DIVE where the UI will be rendered -->
    <div id="ui-wrapper" data-spec="{{spec}}">
        Loading....
    </div>
</body>
<!-- CDN Location of the Swagger UI JS -->
<script src="https://unpkg.com/swagger-ui-dist@latest/swagger-ui-bundle.js"></script>
<script>
    var swaggerUIOptions = {
      // Placeholder for the URL of the ORDS REST Service
      //   we created to return the Open API YAML.
      url: "#OPEN_API_YAML_URL#",
      dom_id: '#ui-wrapper', // Determine what element to load ui into.
      // Other configuration options for Swagger UI
      docExpansion: 'list',
      deepLinking: true, // Enables dynamic deep linking for tags and operations
      filter: true,
      presets: [
        SwaggerUIBundle.presets.apis,
        SwaggerUIBundle.SwaggerUIStandalonePreset
      ],
      plugins: [
        SwaggerUIBundle.plugins.DownloadUrl
      ],
    }
    var ui = SwaggerUIBundle(swaggerUIOptions)
    /** Export to window for use in custom js */
    window.ui = ui
</script>
</html>

ORDS Template

Create a new ORDS Template, including a URI Parameter (api_code) to capture the code for the document you want to serve.

ORDS Handler

Create an ORDS GET Handler based on PLSQL, which will serve the HTML document described above.

The source for the ORDS Handler looks like this:

DECLARE
  lc_html_container  CONSTANT VARCHAR2(32000) := q'@
The HTML described above goes here, or you can fetch it from a table.
@';
  l_api_code         cn_open_api_docs.api_code%TYPE;
  l_html_container   VARCHAR2(32000);
  l_yaml_url         VARCHAR2(4000);
  lc_host_url        CONSTANT VARCHAR2(4000) DEFAULT APEX_UTIL.HOST_URL('SCRIPT');
BEGIN
  -- Capture the API Code URI Parameter.
  l_api_code := UPPER(:api_code);

  -- Build the URL for the Swagger YAML ORDS REST Service we built earlier.
  -- We need to include the api_code in the URL so that we get the
  --  YAML for the specific document being requested.
  l_yaml_url := lc_host_url || l_api_code;
  l_yaml_url := REPLACE(l_yaml_url, '/docs/', '/yaml/');

  -- Substitute the placeholder in the HTML document with
  --   the URL of the YAML ORDS Service.
  l_html_container := REPLACE (lc_html_container, '#OPEN_API_YAML_URL#', l_yaml_url);

  -- Set the Mime Type.
  owa_util.mime_header('text/html', true);

  -- Emit the HTML Page.
  apex_util.prn
   (p_clob   => l_html_container,
    p_escape => FALSE);
END;

You can then test the new ORDS Handler from your browser:

Conclusion

With the power of APEX and ORDS, we created an Open API / Swagger documentation hosting solution with less than 75 lines of code.

Links

• Open API Initiative

• SmartBear Swagger Editor

• Swagger UI

• The Difference Between Open API and Swagger