Oracle Cloud Infrastructure Documentation

Load Data into Autonomous Database from Oracle Cloud Infrastructure Object Storage

This example shows you how to load data from Oracle Cloud Infrastructure Object Storage to Autonomous Database using SQL commands.

You have various options to perform data loading into Autonomous Database, such as:

  • Using UI options: You can use the Data Studio Load tool user interface to create credentials for the cloud store location, select files containing data, and run data load jobs.

    See Loading Data from Cloud Storage

  • Using Rest Data Services APIs: You can use the Data Studio Load tool APIs to create links to the cloud store location and run data load jobs.

    See Using Data Studio Data Load APIs in PL/SQL

  • Using SQL commands as explained in this example.

All these methods use the same PL/SQL package DBMS_CLOUD for loading data. However, Data Studio provides additional benefits over SQL commands. It not only helps to analyze the source and create table definitions but also performs validation checks.

You require Oracle Cloud Infrastructure access credentials for user account authentication and an object URL for accessing the object in your Oracle Cloud Infrastructure Object Storage bucket.

To load data from Oracle Cloud Infrastructure Object Storage:

  1. Create credentials for Oracle Cloud Infrastructure user account in the Autonomous Database.

  2. Copy data from Oracle Cloud Infrastructure Object Storage to the database.

Topics

Parent topic: Examples for Loading Data into Autonomous Database

Prepare for Loading Data from Oracle Cloud Infrastructure

Verify the prerequisites and prepare for loading data from Oracle Cloud Infrastructure Object Storage.

Prerequisites

A data file, for example, oci-data.txt exists in the Oracle Cloud Infrastructure bucket that you can import. The sample file in this example has the following contents:
1,OCI Direct Sales
2,OCI Tele Sales
3,OCI Catalog
4,OCI Internet
5,OCI Partners

On the Oracle Cloud Infrastructure side, log in to your Oracle Cloud Infrastructure account and do the following:

  1. Obtain an Auth token for the Oracle Cloud Infrastructure account.
    For more information, see Getting an Auth Token.
  2. From the Object Details page, obtain the object URL for the data file stored in the Oracle Cloud Infrastructure Object Storage bucket.
    For more information, see Getting an Object Storage Object's Details.

Steps for Loading Data from Oracle Cloud Infrastructure

Run these steps to load data from Oracle Cloud Infrastructure Object Storage to Autonomous Database.

  1. Store the Oracle Cloud Infrastructure account credentials in your Autonomous Database and specify a credential name. This enables the database to authenticate with your Oracle Cloud Infrastructure account and access the items in the Oracle Cloud Infrastructure Object Storage bucket.
    SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_CRED_NAME',
    username => 'username',
    password => 'password'
  );
END;
/
    Note

    Here, the username is your Oracle Cloud Infrastructure user name and the password is your user Auth token.

    For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

    Optionally, you can test the access to Oracle Cloud Infrastructure as shown in this example. 

    SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OCI_CRED_NAME', 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/');
  2. Create a table in your database where you want to load the data.
    CREATE TABLE myocitable (id NUMBER, name VARCHAR2(64));
  3. Import data from the Oracle Cloud Infrastructure bucket to your Autonomous Database.
    Specify the table name and the Oracle Cloud Infrastructure credential name followed by the Oracle Cloud Infrastructure object URL. 
    BEGIN
      DBMS_CLOUD.COPY_DATA(
          table_name => 'myocitable',
          credential_name => 'OCI_CRED_NAME',
          file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/oci-data.txt',
          format => json_object('delimiter' value ',')
      );
END;
/

    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.

    For detailed information about the parameters, see COPY_DATA Procedure.

You have successfully imported data from Oracle Cloud Infrastructure Object Storage to your Autonomous Database. You can run this statement and verify the data in your table.
SELECT * FROM myocitable;
ID  NAME
--  –-------------
 1  OCI Direct Sales
 2  OCI Tele Sales
 3  OCI Catalog
 4  OCI Internet
 5  OCI Partners

For more information about loading data, see Load Data from Files in the Cloud.