REST Service Support

Service Descriptions

To create a layout in the workbook, the REST service(s) that you use must provide a service description that supports the OpenAPI format. The service description can be a URL or a local file. For Oracle RAMP REST services, the URL typically includes a describe, as in https://host/RESTApi/describe. For an Oracle REST Data Service (ORDS), the URL may be similar to https://host/ords/great_app/open-api-catalog/employees/. A service description that you register from a local file on your computer will come from a file where the first element is openapi and the file extension is .JSON.

The service description that you provide can contain the definition for one or more business objects. A service description that contains multiple business objects is sometimes referred to as a catalog. You cannot provide a data URL (a URL that returns data) as the starting point to creating a layout in the workbook.

You can provide the service description document when you create a Table layout or a Form-over-Table layout by clicking the Designer button in the Oracle Visual Builder tab. This approach is described in the sections that describe the creation of Table and Form-over-Table layouts. Alternatively, you can provide the service description document by clicking the Manage Services button in the Oracle Visual Builder tab. This latter approach is described in the section that describes how to edit service descriptions.

Definitions

The content that follows in this chapter and elsewhere in this guide makes use of the following terms:

A business object refers to an entity such as employees. A business object includes a collection path, an item path, and other properties.
A collection path is a service path (endpoint) that can be used to fetch multiple rows of data from the business object and/or to perform operations on the collection.
An item path is a service path (endpoint) that can be used to fetch, or operate on, a single row from the business object.

Data Types Supported by the Oracle Visual Builder Add-in for Excel

The Oracle Visual Builder Add-in for Excel supports a variety of data types exposed by business objects in web applications developed using Oracle Visual Builder and data types exposed by REST services.

The add-in supports the following OpenAPI data types (derived from the JSON Schema Specification):

boolean
integer
object
number
string

In addition, the add-in recognizes the optional modifier property "format", when it is applied to values of type string. The two formats recognized are "date-time" and "date". There is no support for array-valued fields.

The add-in ignores fields with unsupported data types when you create a Table layout or Form-over-Table layout in the Excel workbook. If, for example, a REST service that you use to retrieve data includes the attachment attribute data type, the add-in ignores it and does not create a column in the data table for this attribute type.

For more information, see the specifications for OpenAPI and JSON Schema:

The add-in supports Oracle RAMP REST Service and requires REST API framework version 6.

Excel specifications and limits can be found in the Excel specifications and limits page of Microsoft’s documentation.

Object-typed Fields and Sub-fields

The add-in supports fields of type Object. These fields may expose sub-fields, also known as nested fields.

If, for example, you have an Employee business object with the following fields:

First Name (type: String)
Last Name (String)
Address: (Object)
- Street (String)
- City (String)
- State (String)
- Zip (String)
- GPS Coordinates (Object)
  - Latitude (Number)
  - Longitude (Number)
Hire Date (Date)

In this example, the type of the Address field is Object and it contains sub-fields. Object fields should not be confused with arrays. In this example, an Employee has only one Address. The add-in does not support fields that are typed as arrays.

The add-in handles Object fields and their sub-fields in the following manner:

First, in the Business Object editor Fields tab, only the top-level fields are listed. In this example, the top-level fields are: First Name, Last Name, Address, and Hire Date. To edit the properties for sub-fields of Address, edit Address and find the Sub-fields list on the Field Editor window. The direct sub-fields for Address are Street, City, State, Zip, and GPS Coordinates. Since GPS Coordinates is of type Object, its field editor will show its sub-fields (Latitude, Longitude).
Next, when creating a Table layout from a business object's fields, the add-in promotes the sub-fields and creates columns for each (leaf) sub-field. This maintains a regular, rectangular structure for the table in the worksheet. So, the above example generates a table with these columns:
- First Name
- Last Name
- Address / Street
- Address / City
- Address / State
- Address / Zip
- Address / GPS Coords / Latitude
- Address / GPS Coords / Longitude
- Hire Date

REST Operations

Table features are enabled as follows:

Existing row updates are enabled if the item path has either a PUT or PATCH operation
- For PATCH operations, the add-in includes all data values from editable cells for the changed row(s) on upload, regardless of whether the data value was edited since download.
Create new rows is enabled if the collection path has a POST operation
Delete existing rows is enabled if the item path has a DELETE operation

Oracle REST Data Services

The add-in supports Oracle REST Data Services (ORDS). As with other service types, you must provide an OpenAPI description of the service. ORDS with AutoREST can provide an OpenAPI service description.

For example, use http(s)://myhost.example.com:8888/ords/hr_demo/open-api-catalog/employees/ where:

myhost.example.com:8888 is the host and domain portion
hr_demo is the schema/application
employees is the database table

For other ORDS endpoints, you need to find or create an OpenAPI service description. For information about AutoREST, see Automatic Enabling of Schema Objects for REST Access (AutoREST) in Oracle REST Data Services Installation, Configuration, and Development Guide.

Only basic authentication is supported when working with ORDS services.

After importing an ORDS service description, you can use the Business Object Field Editor to provide additional information about each field to improve the overall user experience. For example:

Edit the field titles
Designate certain fields as required
Define lists of values. See Use Lists of Values in an Excel Workbook.

Known Issues with Oracle REST Data Services

For some row-level errors, such as First Name is too long, the ORDS server does not provide a specific reason for the error.
In some cases, the ORDS server returns Create Failed for rows, when in fact the Create operation was successful. Re-downloading rows into the table will show the created rows.

REST Service Support Limitations

Many different request and response schema types are possible and we cannot list all that are compatible with the add-in. If a particular structure is not listed explicitly as supported, it may not work.

Asymmetrical field lists
Since download, editing, and upload all occurs in the same Excel rectangular grid, the add-in counts on having a single set of field IDs (JSON member names) for both download and upload. If the REST service uses different field IDs for the same information when completing different operations, the REST service cannot be used effectively with the add-in.
Multi-part primary keys are supported only for Oracle RAMP REST services
Fields with forward slash ('/') in the member name:
- OpenApi documents contain schema properties that represented in JSON as something like "memberName" : { . . . properties describing the field ... }
- When creating the business object field from the JSON member, the add-in uses the member name as the field ID.
- Field IDs that include the / character are incompatible with the add-in, so such members will not be represented as fields in the business object.
When providing a URL for an OpenAPI service description, ?metadataMode=minimal is not supported.