DBMS_CLOUD REST APIs

This section covers the DBMS_CLOUD REST APIs provided with Autonomous Data Warehouse.

Note:

To run DBMS_CLOUD subprograms with a user other than ADMIN you need to grant EXECUTE privileges to that user. For example, run the following command as ADMIN to grant privileges to adwc_user:
GRANT EXECUTE ON DBMS_CLOUD TO adwc_user;

DBMS_CLOUD REST API Overview

When you use PL/SQL in your application and you need to call Cloud REST APIs you can use the DBMS_CLOUD function 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. These functions provide a generic API that lets you call any REST API with the following supported cloud services:

  • Oracle Cloud Infrastructure
  • Amazon Web Services (AWS)
  • Azure Cloud Foot 1
  • Oracle Cloud Infrastructure Classic

See the following for more information:

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'

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_RAW Function

This function returns the HTTP response in RAW format. This is useful if the HTTP response is expected to be binary format.

Syntax

DBMS_CLOUD.GET_RESPONSE_RAW(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN BLOB;

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

GET_RESPONSE_STATUS_CODE Function

This function returns the HTTP response status code as an integer. The status code helps to identify if the request is successful.

Syntax

DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN PLS_INTEGER;

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

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.

SEND_REQUEST Function

This function 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 a return response code and payload.

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,
       body               IN BLOB DEFAULT NULL)
   RETURN DBMS_CLOUD_TYPES.resp;

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.

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 (OCI Signing Key Credentials) 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.

DBMS_CLOUD REST API Examples

Shows examples using DBMS_CLOUD.SEND_REQUEST to create and delete an Oracle Cloud Infrastructure Object Storage bucket, and an example to list all compartments in the tenancy.

Note:

These examples show Oracle Cloud Infrastructure request APIs and require that you use a Signing Key based credential for the credential_name. Oracle Cloud Infrastructure Signing Key based credentials include the private_key and fingerprint arguments.

For example:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => ‘OCI_KEY_CRED’,
       user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
       tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
       private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
       fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
END;
/
See CREATE_CREDENTIAL Procedure (OCI Signing Key Credentials) for information on DBMS_CLOUD.CREATE_CREDENTIAL.

Create Bucket Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP POST method to create an object store bucket named bucketname.

See CreateBucket for details on the Oracle Cloud Infrastructure Object Storage Service API for this example.

SET SERVEROUTPUT ON
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
BEGIN
  -- Send request
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://objectstorage.region.oraclecloud.com/n/namespace-string/b/',
            method => DBMS_CLOUD.METHOD_POST,
            body => UTL_RAW.cast_to_raw(
                        JSON_OBJECT('name' value 'bucketname',
                                    'compartmentId' value 'compartment_OCID'))
          );
 
  -- Response Body in TEXT format
  dbms_output.put_line('Body: ' || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_text(resp) || CHR(10));
  
  -- Response Headers in JSON format
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
 
  -- Response Status Code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_status_code(resp));
 
END;
/

Notes:

  • In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

  • Where: region is an endpoint region. See Object Storage API reference in API Reference and Endpoints for more information. For example, where region is: us-phoenix-1.

Delete Bucket Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP DELETE method to delete an object store bucket named bucketname.

See DeleteBucket for details on the Oracle Cloud Infrastructure Object Storage Service API for this example.

SET SERVEROUTPUT ON
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
BEGIN
  -- Send request
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname',
            method => DBMS_CLOUD.METHOD_DELETE
          );
 
  -- Response Body in TEXT format
  dbms_output.put_line('Body: ' || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_text(resp) || CHR(10));
  
  -- Response Headers in JSON format
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
 
  -- Response Status Code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_status_code(resp));
 
END;
/

Notes:

  • In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

  • Where: region is an endpoint region. See Object Storage API reference in API Reference and Endpoints for more information. For example, where region is: us-phoenix-1.

List Compartments Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP GET method to list all compartments in the tenancy (root compartment). This example shows how to pass request headers in the DBMS_CLOUD.SEND_REQUEST.

See ListCompartments for details on the Oracle Cloud Infrastructure Identity and Access Management Service API for this example.

--
-- List compartments
--
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
  root_compartment_ocid VARCHAR2(512) := '&1';
BEGIN
  -- Send request
  dbms_output.put_line('Send Request');
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://identity.region.oraclecloud.com/20160918/compartments?compartmentId=' || root_compartment_ocid,
            method => DBMS_CLOUD.METHOD_GET,
            headers => JSON_OBJECT('opc-request-id' value 'list-compartments')
          );
  dbms_output.put_line('Body: ' || '------------' || CHR(10) || DBMS_CLOUD.get_response_text(resp) || CHR(10));
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_status_code(resp));
  dbms_output.put_line(CHR(10));
END;
/

Where: region is an endpoint region. See Identity and Access Management (IAM) API reference in API Reference and Endpoints for more information. For example, where region is: uk-london-1.

Asynchronous Request Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP POST method to perform the Autonomous Database stop operation and wait for status. This example shows how to use DBMS_CLOUD.SEND_REQUEST with the async_request_url, wait_for_states, and timeout parameters.

--
-- Sent Work Request Autonomous Database Stop Request with Wait for Status
DECLARE
    l_resp DBMS_CLOUD_TYPES.resp;
    l_resp_json JSON_OBJECT_T;
    l_key_shape JSON_OBJECT_T;
    l_body JSON_OBJECT_T;
    status_array DBMS_CLOUD_TYPES.wait_for_states_t;
BEGIN
  status_array := DBMS_CLOUD_TYPES.wait_for_states_t('SUCCEEDED');
  l_body := JSON_OBJECT_T('{}');
  l_body.put('autonomousDatabaseId', 'ocid');
-- Send request
  dbms_output.put_line(l_body.to_clob);
  dbms_output.put_line('Send Request');
  l_resp := DBMS_CLOUD.send_request(
                       credential_name    => 'NATIVE_CRED_OCI',
                       uri                => 'https://database.region.oraclecloud.com/20160918/autonomousDatabases/ocid/actions/stop',
                       method             => DBMS_CLOUD.METHOD_POST,
                       body               => UTL_RAW.cast_to_raw(l_body.to_clob),
                       async_request_url  => 'https://iaas.region.oraclecloud.com/20160918/workRequests',
                       wait_for_states    => status_array,
                       timeout            => 600
                  );
   dbms_output.put_line('resp body: '||DBMS_CLOUD.get_response_text(l_resp));
   dbms_output.put_line('resp headers: '||DBMS_CLOUD.get_response_headers(l_resp).to_clob);
END;
/

Where: region is an endpoint region. See Identity and Access Management (IAM) API reference in API Reference and Endpoints for more information. For example, where region is: uk-london-1.

The ocid is the Oracle Cloud Infrastructure resource identifier. See Resource Identifiers for more information.



Footnote Legend

Footnote 1: Support for Azure Cloud REST API calls is limited to the domain "blob.windows.net".