REST Service Support

Service Types

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

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 (ORDS) 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 information about AutoREST, see Automatic Enabling of Schema Objects for REST Access (AutoREST) in the Oracle REST Data Services Developer's Guide.

For manually-created REST services using ORDS, you'll need to define modules, templates, and handlers in order to get an OpenAPI service description. See Manually Creating RESTful Services Using SQL and PL/SQL in the Oracle REST Data Services Developer's Guide.

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:

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 Developer's 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.

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 binary attribute data type, the add-in ignores it and does not create a column in the data table for this attribute type.

Note:

File, text, and web page type attachments are supported. See Create Layouts for Attachment Business Objects.

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

OpenApi: https://swagger.io/specification/#dataTypes
JSON Schema: https://tools.ietf.org/html/draft-wright-json-schema-00#section-4.2

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 present in a schema, the corresponding Required property defaults to false.

You can always edit the Required for update and Required for create values in the Business Object Field Editor. See Configure Business Object Fields.

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 these 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.

Natural 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.

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.

Caution:

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.

The add-in supports REST response payloads up to 1 billion characters in size.
Only REST API services that return application/json media types as response payloads are supported. The add-in supports application/octet-stream for attachments. Other media types such as XML are not supported.
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 are 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.
URLs longer than 8000 bytes may fail due to limitations in various network devices between the add-in and the server.

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 Manage Catalogs and Business Objects.