REST Service Support

Service Types

Oracle Visual Builder Add-in for Excel provides support for the following service types:

Supported Data Types

The add-in supports a variety of data types exposed by business objects in web applications developed using 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 time, binary, or byte formats or 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 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:

Required Fields

When a business object field is created from a service description, the initial values of the Required for Update and Required for Create properties are set based on the following OpenApi property:

The Required for Update value is determined by the request body schema of the PUT (or PATCH) operation for the item path, along with the OpenAPI Required array property.
The Required for Create value is determined by the request body schema of the POST operation for the collection path, along with the OpenAPI Required array property.

When these PUT/PATCH or POST operations are not available, the Required properties are set using the response body schema of the collection GET operation and the OpenAPI Required array property. If the OpenAPI Required array property is not specified for a schema, the Required properties default to false.

You can always edit the Required for Update and Required for Create values in the Business Object Field Editor. See Edit Service Descriptions and Business Objects.

REST Operations

Table and Form-over-Table capabilities are enabled for PUT, PATCH, POST, and DELETE operations 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

Note:

Even if the business object supports an operation, you can still choose to disable a layout's capability by deselecting it in the Layout Designer's Advanced tab.

Language Support

For every request that the add-in makes to the REST service, Oracle Visual Builder Add-in for Excel automatically adds the accept-language header.

By default, the value sent with the accept-language header is the language/culture code that Excel is currently configured to use. You can change the language as described in Change the Add-in's Language.

Each REST service determines how and whether it will react to the accept-language header.

Oracle Business Object REST API Services

Oracle business object REST API services provide rich service descriptions that Oracle Visual Builder Add-in for Excel can analyze to provide a business object catalog with many details already filled out.

Oracle business object REST API services include many services offered by Oracle Cloud Applications as well as custom business objects in Visual Builder. When the add-in integrates an Excel workbook with Oracle business object REST API services, it supports special features such as:

See Accessing Business Objects Using REST APIs and Creating ADF RESTful Web Services with Application Modules.

Oracle REST Data Services

Oracle Visual Builder Add-in for Excel supports Oracle REST Data Services when you provide an OpenAPI description of an ORDS 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. See Create a Service Description in Oracle Visual Builder. 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 ORDS

Refer to these known issues when planning to use Oracle REST Data Services.

For some row-level errors, 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.
With an ORDS service, the PUT operation on the item path performs an "upsert" (see Update/Insert Table Row in Oracle REST Data Services Installation, Configuration, and Development Guide). So if you are about to update an existing row and someone else deletes that row, your update attempt may re-create that row. There's no warning or notice when this behavior occurs.

Other Services

Oracle Visual Builder Add-in for Excel can also be used with other service types as long as the service behaves as the add-in expects.

When using another REST service, provide an OpenApi service description as you would for other service types. See REST Service Support Limitations for more information on the add-in's expectations for any service.

Note:

The graphical search editor is not available for these services but search parameters can be used. See Use Search Parameters to Limit Downloaded Data.

Object-typed Fields and Sub-fields

Oracle Visual Builder Add-in for Excel 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 Service Support Limitations

Refer to these limitations when planning to integrate a workbook with a REST service using Oracle Visual Builder Add-in for Excel.

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.

Only REST API services that return application/json media types as response payloads are supported. Other media types (for example, XML and octet-stream responses) are not supported.
Child business objects cannot be used for the form part of a Form-over-Table layout.
Polymorphic business objects cannot be used to create a layout.
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, it cannot be used effectively with the add-in.
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.
Remote references are not supported in OpenApi service descriptions.

If a REST service owner makes significant changes to the service after the workbook is configured to integrate with the service, the integration may not function as expected. In such cases, you can either re-import the service description and create a new layout, or refresh the business object catalog. If the change is minor, you can update the business object details to match the change in the service. See Edit Service Descriptions and Business Objects.