Exporting Data to Object Storage

Prerequisites

To implement the example below, you will need:

Access to Oracle Cloud Console.
Access to the Object Storage Service.
Ability to list buckets and their objects.
Ability to create a PAR.
Access to the APEX UI.
Access to POSTMAN. The discussion below assumes familiarity POSTMAN.
The ORDS OAUTH credentials created in Obtaining ORDS Service Credentials.

There are two approaches to exporting data to object storage. The approaches only differ in the type for file URI used. The first approach uses a pre-authenticated request (PAR). The second uses a URI that requires authentication.

Exporting Data Using a PAR

Create a bucket level or prefix PAR using the process described in Section 4.3.4. An object level PAR will not work. The example code below demonstrates how a PAR can be used to export data from RDS.

Begin
   dbms_cloud.export_data(
     file_uri_list=> '<your-PAR-goes-here>',
     query => 'select 1 from dual',
     format => json_object('type' value 'csv')
   );
   htp.prn('{"status":"success"}');
end;

Before proceeding:

Create a bucket level PAR for an export test.
Execute the above code in APEX > SQL Commands
1. Verify an export was created by listing the objects in your bucket.
Implement an export POST service using the example code above and your PAR.
Test the service using POSTMAN.
1. Verify an export was created by listing the objects in your bucket.

Exporting Data with a Credential

In Obtaining Object Storage Credentials, you created credentials that can be used to import data from and export data to object storage. You will need those credentials to run the example described here. The first step to exporting data using an unauthenticated file URI is configure a credential for use in ADW. An example follows.

begin
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name =>'OCI_KEY_CRED',
    user_ocid => '<your-user-ocid>',
    tenancy_ocid=> '<your-tenancy-ocid>',
    private_key=> '<your-private-key>',
    fingerprint=> '<your-fingerprint>'
  );
end;

The export code for an unauthenticated file URI adds this credential to the calling parameters.

begin
   dbms_cloud.export_data(
    credential_name => '<your-credential>',
    file_uri_list=> '<your-URI-goes-here>',
    query => 'select 1 from dual',
    format => json_object('type' value 'csv')
   );
   htp.prn('{"status":"success"}');
end;

Before proceeding:

Configure your credential using the information you obtained in Obtaining Object Storage Credentials.
Execute the above code in APEX > SQL Commands.
1. Verify an export was created by listing the objects in your bucket.
Implement an export POST service using the example code above and your file URI. The format of the URI is described in Constructing an Object Storage Object URL.
Test the service using POSTMAN.
1. Verify an export was created by listing the objects in your bucket.

13 Exporting Data to Object Storage

Prerequisites

Exporting Data Using a PAR

Exporting Data with a Credential