DBMS_CLOUD REST APIs

Prerequisites

As a developer, you can use DBMS_CLOUD REST APIs with Autonomous Databases deployed on Oracle Public Cloud or Exadata Cloud@Customer.

Depending on the deployment choice, the following prerequisites must be met to use the DBMS_CLOUD REST APIs with Amazon S3, Azure Blob Storage, and Google Cloud Storage service providers.

Oracle Public Cloud
Exadata Cloud@Customer

An outbound connectivity must have been configured using a NAT gateway, by your fleet administrator as described below:

Create a NAT gateway in the Virtual Cloud Network (VCN) where your Autonomous Database resources reside by following the instructions in Create a NAT Gateway in Oracle Cloud Infrastructure Documentation.
After creating the NAT gateway, add a route rule and an egress security rule to each subnet (in the VCN) where Autonomous Database resources reside so that these resources can use the gateway to obtain a public key from your Azure AD instance:
1. Go to the Subnet Details page for the subnet.
2. In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.
3. In the table of existing Route Rules, check whether there is already a rule with the following characteristics:
  - Destination: 0.0.0.0/0
  - Target Type: NAT Gateway
  - Target: The name of the NAT gateway you just created in the VCN
  If such a rule does not exist, click Add Route Rules and add a route rule with these characteristics.
4. Return to the Subnet Details page for the subnet.
5. In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.
6. In the side menu, under Resources, click Egress Rules.
7. In the table of existing Egress Rules, check whether there is already a rule with the following characteristics:
  - Destination Type: CIDR
  - Destination: 0.0.0.0/0
  - IP Protocol: TCP
  - Source Port Range: 443
  - Destination Port Range: All
  If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

The HTTP Proxy settings in your environment must allow the database to access the cloud service provider.

These settings are defined by your fleet administrator while creating the Exadata Cloud@Customer infrastructure as described in Using the Console to Provision Exadata Database Service on Cloud@Customer .

Note:

The network configuration including the HTTP Proxy can only be edited until the Exadata Infrastructure is in Requires Activation state. Once it is activated, you cannot edit those settings.

Setting up an HTTP Proxy for an already provisioned Exadata Infrastructure needs a Service Request (SR) in My Oracle Support. See Create a Service Request in My Oracle Support for details.

DBMS_CLOUD REST APIs

This section covers the DBMS_CLOUD REST APIs provided with Autonomous Database.

REST API	Description
GET_RESPONSE_HEADERS Function	This function returns the HTTP response headers as JSON data in a JSON object in Autonomous Database.
GET_RESPONSE_TEXT Function	This function returns the HTTP response in TEXT format (`VARCHAR2` or `CLOB`) in Autonomous Database. Usually, most Cloud REST APIs return JSON response in text format. This function is useful if you expect the HTTP response is in text format.
GET_API_RESULT_CACHE_SIZE Function	This function returns the configured result cache size.
SEND_REQUEST Function and Procedure	This function begins an HTTP request, gets the response, and ends the response in Autonomous Database. This function provides a workflow for sending a Cloud REST API request with arguments and a return response code and payload.
SET_API_RESULT_CACHE_SIZE Procedure	This procedure sets the maximum cache size for current session.

DBMS_CLOUD REST API Overview

When you use PL/SQL in your application and you need to call Cloud REST APIs you can use DBMS_CLOUD.SEND_REQUEST to send the REST API requests.

The DBMS_CLOUD REST API functions allow you to make HTTP requests using DBMS_CLOUD.SEND_REQUEST and obtain and save results. These functions provide a generic API that lets you call any REST API with the following supported cloud services:

Oracle Cloud Infrastructure
See API Reference and Endpoints for information on Oracle Cloud Infrastructure REST APIs.
Azure Cloud ^Foot 1
See Azure REST API Reference for information on Azure REST APIs.

DBMS_CLOUD REST API Constants

Describes the DBMS_CLOUD constants for making HTTP requests using DBMS_CLOUD.SEND_REQUEST.

DBMS_CLOUD supports GET, PUT, POST, HEAD and DELETE HTTP methods. The REST API method to be used for an HTTP request is typically documented in the Cloud REST API documentation.

Name	Type	Value
`METHOD_DELETE`	`VARCHAR2(6)`	`'DELETE'`
`METHOD_GET`	`VARCHAR2(3)`	`'GET'`
`METHOD_HEAD`	`VARCHAR2(4)`	`'HEAD'`
`METHOD_POST`	`VARCHAR2(4)`	`'POST'`
`METHOD_PUT`	`VARCHAR2(3)`	`'PUT'`

DBMS_CLOUD REST API Results Cache

You can save DBMS_CLOUD REST API results when you set the cache parameter to true with DBMS_CLOUD.SEND_REQUEST. The SESSION_CLOUD_API_RESULTS view describes the columns you can use when REST API results are saved.

By default DBMS_CLOUD REST API calls do not save results for your session. In this case you use the DBMS_CLOUD.SEND_REQUEST function to return results.

When you use DBMS_CLOUD.SEND_REQUEST and set the cache parameter to TRUE, results are saved and you can view past results in the SESSION_CLOUD_API_RESULTS view. Saving and querying historical results of DBMS_CLOUD REST API requests can help you when you need to work with your previous results in your applications.

For example, to query recent DBMS_CLOUD REST API results, use the view SESSION_CLOUD_API_RESULTS:

SELECT timestamp FROM SESSION_CLOUD_API_RESULTS;

When you save DBMS_CLOUD REST API results with DBMS_CLOUD.SEND_REQUEST the saved data is only available within the same session (connection). After the session exits, the saved data is no longer available.

Use DBMS_CLOUD.GET_API_RESULT_CACHE_SIZE and DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE to view and set the DBMS_CLOUD REST API cache size, and to disable caching.

DBMS_CLOUD REST API Results cache_scope Parameter

When you save DBMS_CLOUD REST API results with DBMS_CLOUD.SEND_REQUEST, access to the results in SESSION_CLOUD_API_RESULTS is provided based on the value of cache_scope.

By default cache_scope is 'PRIVATE' and only the current user of the session can access the results. If you set the cache_scope to 'PUBLIC', then all session users can access the results. The default value for cache_scope specifies that each user can only see DBMS_CLOUD.SEND_REQUEST REST API results generated by the procedures they invoke with invoker's rights. When you invoke DBMS_CLOUD.SEND_REQUEST in a session, there are three possibilities that determines if the current user can see results in the cache, based on the cache_scope value:

You directly execute DBMS_CLOUD.SEND_REQUEST as a top-level statement and the call to DBMS_CLOUD.SEND_REQUEST and the REST API results are saved with the same username. In this case you have access to all results with the default value, 'PRIVATE', set for cache_scope.
You write a wrapper invoker's rights procedure and as the current user your call with DBMS_CLOUD.SEND_REQUEST calls the procedure and the REST API results are saved with the same username. In this case, and you have access to all results with the default value, 'PRIVATE', set for cache_scope.
You write a wrapper definer's rights procedure and the procedure is owned by another user. When you call DBMS_CLOUD.SEND_REQUEST inside the procedure, the results are saved with the username of the procedure owner.

For this case, a different definer's rights user is invoking DBMS_CLOUD.SEND_REQUEST, and the REST API results are saved with that definers procedure's owner. For this case, by default when cache_scope is PRIVATE', the invoker's session cannot see the results.

If the definer's procedure owner wants to make the results available to any invoking session user, then they must set cache_scope to 'PUBLIC' in the DBMS_CLOUD.SEND_REQUEST.

DBMS_CLOUD REST API SESSION_CLOUD_API_RESULTS View

You can save DBMS_CLOUD REST API results when you set the cache parameter to true with DBMS_CLOUD.SEND_REQUEST. The SESSION_CLOUD_API_RESULTS view describes the columns you can use when REST API results are saved.

The view SESSION_CLOUD_API_RESULTS is the view created if you cache results with DBMS_CLOUD.SEND_REQUEST. You can query historical results which belong to your user session. When the session ends, the data in the SESSION_CLOUD_API_RESULTS is purged.

Column	Description
`URI`	The `DBMS_CLOUD` REST API request URL
`TIMESTAMP`	The `DBMS_CLOUD` REST API response timestamp
`CLOUD_TYPE`	The `DBMS_CLOUD` REST API cloud type, such as Oracle Cloud Infrastructure and AZURE_BLOB
`REQUEST_METHOD`	The `DBMS_CLOUD` REST API request method, such as `GET`, `PUT`, `HEAD`
`REQUEST_HEADERS`	The `DBMS_CLOUD` REST API request headers
`REQUEST_BODY_TEXT`	The `DBMS_CLOUD` REST API request body in `CLOB`
`RESPONSE_STATUS_CODE`	The `DBMS_CLOUD` REST API response status code, such as `200(OK)`, `404(Not Found)`
`RESPONSE_HEADERS`	The `DBMS_CLOUD` REST API response headers
`RESPONSE_BODY_TEXT`	The `DBMS_CLOUD` REST API response body in `CLOB`
`SCOPE`	The `cache_scope` set by `DBMS_CLOUD.SEND_REQUEST`. Valid values are `PUBLIC` or `PRIVATE`.

GET_RESPONSE_HEADERS Function

This function returns the HTTP response headers as JSON data in a JSON object.

Syntax

DBMS_CLOUD.GET_RESPONSE_HEADERS(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN JSON_OBJECT_T;

Parameters

Parameter	Description
`resp`	HTTP Response type returned from `DBMS_CLOUD.SEND_REQUEST`.

Exceptions

Exception	Error	Description
`invalid_response`	`ORA-20025`	Invalid response type object passed to `DBMS_CLOUD.GET_RESPONSE_HEADERS`.

GET_RESPONSE_TEXT Function

This function returns the HTTP response in TEXT format (VARCHAR2 or CLOB). Usually, most Cloud REST APIs return JSON response in text format. This function is useful if you expect the the HTTP response is in text format.

Syntax

DBMS_CLOUD.GET_RESPONSE_TEXT(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN CLOB;

Parameters

Parameter	Description
`resp`	HTTP Response type returned from `DBMS_CLOUD.SEND_REQUEST`.

Exceptions

Exception	Error	Description
`invalid_response`	`ORA-20025`	Invalid response type object passed to `DBMS_CLOUD.GET_RESPONSE_TEXT`.

GET_API_RESULT_CACHE_SIZE Function

This function returns the configured result cache size. The cache size value only applies for the current session.

Syntax

DBMS_CLOUD.GET_API_RESULT_CACHE_SIZE()
   RETURN NUMBER;

SEND_REQUEST Function and Procedure

This function and procedure begins an HTTP request, gets the response, and ends the response. This function provides a workflow for sending a cloud REST API request with arguments and the function returns a response code and payload. If you use the procedure, you can view results and response details from the saved results with the SESSION_CLOUD_API_RESULTS view.

Syntax

DBMS_CLOUD.SEND_REQUEST(
       credential_name    IN VARCHAR2,
       uri                IN VARCHAR2,
       method             IN VARCHAR2,
       headers            IN CLOB DEFAULT NULL,
       async_request_url  IN VARCHAR2 DEFAULT NULL,
       wait_for_states    IN DBMS_CLOUD_TYPES.wait_for_states_t DEFAULT NULL,
       timeout            IN NUMBER DEFAULT 0,
       cache              IN PL/SQL BOOLEAN DEFAULT FALSE,
       cache_scope        IN VARCHAR2 DEFAULT 'PRIVATE',
       body               IN BLOB DEFAULT NULL)
   RETURN DBMS_CLOUD_TYPES.resp;

DBMS_CLOUD.SEND_REQUEST(
       credential_name    IN VARCHAR2,
       uri                IN VARCHAR2,
       method             IN VARCHAR2,
       headers            IN CLOB DEFAULT NULL,
       async_request_url  IN VARCHAR2 DEFAULT NULL,
       wait_for_states    IN DBMS_CLOUD_TYPES.wait_for_states_t DEFAULT NULL,
       timeout            IN NUMBER DEFAULT 0,
       cache              IN PL/SQL BOOLEAN DEFAULT FALSE,
       cache_scope        IN VARCHAR2 DEFAULT 'PRIVATE',
       body               IN BLOB DEFAULT NULL);

Parameters

Parameter	Description
`credential_name`	The name of the credential for authenticating with the corresponding cloud native API.
`uri`	HTTP URI to make the request.
`method`	HTTP Request Method: `GET`, `PUT`, `POST`, `HEAD`, `DELETE`. Use the `DBMS_CLOUD` package constant to specify the method. See DBMS_CLOUD REST API Constants for more information.
`headers`	HTTP Request headers for the corresponding cloud native API in JSON format. The authentication headers are set automatically, only pass custom headers.
`async_request_url`	An asynchronous request URL. To obtain the URL select your request API from the list of APIs (see https://docs.cloud.oracle.com/en-us/iaas/api/). Then, navigate to find the API for your request in the left pane. For example, Database Services API → Autonomous Database → StopAutonomousDatabase. This page shows the API home (and shows the base endpoint). Then, append the base endpoint with the relative path obtained for your work request WorkRequest link.
`wait_for_states`	Wait for states is a status of type: `DBMS_CLOUD_TYPES.wait_for_states_t`. The following are valid values for expected states: '`ACTIVE`', '`CANCELED`', '`COMPLETED`', '`DELETED`', '`FAILED`', '`SUCCEEDED`'. Multiple states are allowed for `wait_for_states`. The default value for `wait_for_states` is to wait for any of the expected states: '`ACTIVE`', '`CANCELED`', '`COMPLETED`', '`DELETED`', '`FAILED`', '`SUCCEEDED`'.
`timeout`	Specifies the timeout, in seconds, for asynchronous requests with the parameters `async_request_url` and `wait_for_states`. Default value is `0`. This indicates to wait for completion of the request without any timeout.
`cache`	If `TRUE` specifies the request should be cached in REST result API cache. The default value is `FALSE`, which means REST API requests are not cached.
`cache_scope`	Specifies whether everyone can have access to this request result cache. Valid values: `"PRIVATE"` and `"PUBLIC"`. The default value is `"PRIVATE"`.
`body`	HTTP Request Body for `PUT` and `POST` requests.

Exceptions

Exception	Error	Description
`invalid_req_method`	`ORA-20023`	Request method passed to `DBMS_CLOUD.SEND_REQUEST` is invalid.
`invalid_req_header`	`ORA-20024`	Request headers passed to `DBMS_CLOUD.SEND_REQUEST` are not in valid JSON format.

Usage Notes

If you are using Oracle Cloud Infrastructure, you must use a Signing Key based credential value for the credential_name. See CREATE_CREDENTIAL Procedure for more information.
The optional parameters async_request_url, wait_for_states, and timeout allow you to handle long running requests. Using this asynchronous form of send_request, the function waits for the completion status specified in wait_for_states before returning. With these parameters in the send request, you pass the expected return states in the wait_for_states parameter, and you use the async_request_url parameter to specify an associated work request, the request does not return immediately. Instead, the request probes the async_request_url until the return state is one of the expected states or the timeout is exceeded (timeout is optional). If no timeout is specified, the request waits until a state found in wait_for_states occurs.

SET_API_RESULT_CACHE_SIZE Procedure

This procedure sets the maximum cache size for current session. The cache size value only applies for the current session.

Syntax

DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE(
       cache_size          IN NUMBER);

Parameters

Parameter Description

Parameter	Description
`cache_size`	Set the maximum cache size to the specified value `cache_size`. If the new maximum cache size is smaller than the current cache size, older records are dropped until the number of rows is equal to the specified maximum cache size. The maximum value is `10000`. If the cache size is set to `0`, caching is disabled in the session. The default cache size is `10`.

cache_size

Set the maximum cache size to the specified value cache_size. If the new maximum cache size is smaller than the current cache size, older records are dropped until the number of rows is equal to the specified maximum cache size. The maximum value is 10000.

If the cache size is set to 0, caching is disabled in the session.

The default cache size is 10.

Exceptions

Exception	Error	Description
`invalid API result cache size`	`ORA-20032`	The minimum value is 0 and the maximum value is 10000. This exception is shown when the input value is less than 0 or is larger than 10000.

Example

EXEC DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE(101);