4 Using Cloud Storage

Starting from SQLcl release 21.2, you can use the DBMS_CLOUD package for authentication to access objects in Cloud Storage.

The following actions can be performed with either OCI or DBMS_CLOUD:

• Put, get, delete, and peek objects in Cloud Storage.
• Load database objects from objects in Cloud Storage.
• Unload a database table to an object in Cloud Storage.

Table 4-1 Accessing the cloud storage

Using OCI	Using DBMS_CLOUD
Can access on-premises database and cloud database	Can access cloud database only
Set a default Cloud Storage URL, list compartments, buckets and objects, and view some of the contents of an object in Cloud Storage	Set a default Cloud Storage URL, list objects, and view some of the contents of an object in Cloud Storage
Create an OCI profile for authentication and access as a prerequisite step	Authentication is done using existing OCI or Swift credentials

Use the Cloud Storage (cs) command to set a default Cloud Storage URI, list compartments, buckets and objects, and view some of the contents of an object in Cloud Storage.

Topics

• Using DBMS_CLOUD for Authentication

• Creating the OCI profile for OCI Authentication

• Cloud Storage Command Options

• Examples

See Also:

• Loading a File

• Calling Oracle Cloud Infrastructure REST APIs Using the OCI Command

4.1 Using DBMS_CLOUD for Authentication

You can access the cloud storage using the DBMS_CLOUD Credential command. Presently, the DBMS_CLOUD Credential supports Oracle Cloud database.

With the DBMS_CLOUD Credential command (dbcc), you can:

• Create a credential using Swift, OCI credentials, or an existing OCI profile.
• Enable, disable, update, and drop a credential.
• List the credentials.
• Set the default credential.

Create a Credential

To create authentication using the Swift credential, use the following command:

dbcc create <new-credential-name> user <username> pass <password>

To create authentication using the OCI credential, use the following command:

dbcc create <new-credential-name> fingerprint <fingerprint> user_ocid <user-id> tenancy_ocid <tenancy-id> private_path <path-to-private-key>

To create authentication using OCI profile, use the following command:

dbcc create <new-credential-name> profile <oci-profile-name>

Enable, Disable, Update, and Drop a Credential

To enable a credential, use the following command:

dbcc enable <credential-name>

To disable a credential, use the following command:

dbcc disable <credential-name>

To update a credential, use the following command:

dbcc update <credential-name> <attribute> <value>

where <attribute> : [user_ocid] [tenancy | tenancy_id] [key_path | private_key_path] [print | fingerprint]

To drop a credential, use the following command:

dbcc drop <credential-name>

List the Credentials

To display a list of available credentials, use the following command:

dbcc list

Set the Default Credential

To set mycred as the default credential, use the following command:

dbcc mycred

To find help on the dbcc command, use the following command:

help dbccred

An Example to Create and Set a DBMS_CLOUD Credential

The following example details the steps to create and set different types of DBMS_CLOUD credentials.

--Set the path to the Wallet zip file on your system
SQL> set cloudconfig <path-to-Wallet-file>
Operation is successfully completed.

--Supply the database user name, password and the database service name provided in the tnsnames.ora
SQL> connect admin/<password>@adw20200526_high
Connected.

--Set Cloud Storage URL to the bucket
SQL> cs https://objectstorage.us-ashburn-1.example.com/n/test/b/example
DBMS_CLOUD Credential: Not Set
OCI Profile: Not Set
Transer Method: Not Set
URI as specified: https://objectstorage.us-ashburn-1.example.com/n/test/b/example

--create CREDENTIAL using Swift authentication
SQL> dbcc create swiftcred user <username> pass <password>
Credential SWIFTCRED created.
DBMS_CLOUD Credential set to SWIFTCRED.
Transfer method set to DBMS_CLOUD.

--create CREDENTIAL using OCI authentication
SQL> dbcc create ocicred fingerprint ec:98:83:**:**:**:b5 
     user_ocid ocid1.user.oc1......**g4xoca 
     tenancy_ocid ocid1.tenancy.oc1.....a**3n3a 
     private_path C:\\Users\\test\\.oci/freedb_api_key.pem
Credential OCICRED created.
DBMS_CLOUD Credential set to OCICRED.
Transfer method set to DBMS_CLOUD.

--create CREDENITAL using an existing OCI profile called freedb
--If profile name is not passed as a parameter, it takes the default 
   OCI profile details which is already set.
SQL> dbcc create profcred profile [freedb]      
Credential PROFCRED created.
DBMS_CLOUD Credential set to PROFCRED.
Transfer method set to DBMS_CLOUD.

4.2 Creating the OCI profile for OCI Authentication

For using the OCI authentication scheme, you must first set the OCI profile.

For more information about how to set up the required SSH keys and configure your Oracle Cloud Infrastructure Software Development Kits, see Setup and Prerequisites in the Oracle Cloud Infrastructure Documentation.

Note the following points:

• Generate a RSA key pair in PEM format (minimum 2048 bits required) without a passphrase. Use this key_file in the profile.

• Do not use relative paths inside the configuration file because relative paths resolve based on the SQLcl launch directory. Therefore, the key_file may not be found.

• Use double backslashes (\\) instead of a single backslash (\) for specifying the path of the key_file in Windows.

To display the list of profiles contained in the OCI configuration file at ~/.oci/config, use the following command:

oci profile

To set the profile as demo, use the following command:

oci profile demo

4.3 Cloud Storage Command Options

You can invoke the Cloud Storage command in SQLcl with cloudstorage or cloud_storage or cs.

The Cloud Storage Command provides Cloud Storage functions to:

• Set and show the default cloud storage URI.
• Set and show the transfer method, profile and credential.
• List compartment, bucket and objects in cloud storage.
• Get, put, delete, and peek a Cloud Storage Object.

For a list of the Cloud Storage command options in SQLcl, enter:

help cs

The command options are:

• cs [ uri ] <uri>: Sets the default Cloud Storage URI. The URI must contain the host and optionally the namespace, bucket, and object.

  Example:
  --host
  cs https://objectstorage.us-ashburn-1.oraclecloud.com
  --namespace 
  cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test
  –-bucket
  cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
  --object 
  cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/employees.csv

• cs oci [<profile>]: Sets the default transfer as OCI. Optionally, specify the profile to be used.

• cs {DBC | DBMS_CLOUD} [<credential>]: Sets the default transfer as DBMS_CLOUD. Optionally, specify the credential to use.

• cs clear: Clears the Cloud Storage URI including URI and transfer options.

• cs show: Shows the current default Cloud Storage URI, transfer method, profile and credential.

• cs listc [ <compartment> ]: Lists the compartments in the specified compartment. If compartment is not specified, lists the compartments in the namespace as identified by the profile. (OCI only)

• cs listb <compartment>: Lists the buckets in a compartment. (OCI only)

• cs listo [ <bucket> ]: Lists the objects in a bucket. Bucket must be specified in the URI or on the listo command.

• cs delete [<qualifier>]: Delete the object from cloud storage.
• cs get [<qualifier>]: Get the object from cloud storage.
• cs PUT [file-to-send-as-body] <qualifier>: Copy the file to cloud storage.

• cs peek [ <qualifier> ] [ { pos | position} <position> ] [ size <size> ]: Displays the contents of the object where:

  qualifier is the name of the object, optionally qualified by the namespace and the bucket. The qualified name combined with the default URI specified must fully identify the object URI.

  position is the start location in the object for display.

  size is the number of characters to display. If omitted, 2000 characters are displayed.

4.4 Examples

This section provides some examples:

• Load a Cloud Storage File into a Table

• Unload Table Data into Cloud Storage

• List Compartments, Buckets, and Objects

• Load a Local File into Cloud Storage using the PUT command

• Get a File from Cloud Storage using the GET Command

• Delete a File in Cloud Storage using the DELETE Command

• Grant a DBMS_CLOUD Credential to Another User

Load a Cloud Storage File into a Table

The following example shows how to load a cloud storage file into a database table:

Use Case 1: When using the DBMS_CLOUD authentication scheme, the DBMS_CLOUD credential is set and transfer method is set to dbms_cloud.

--Set the DBMS_CLOUD credential
SQL> cs dbc swiftcred

--Set Cloud Storage URI to the bucket
SQL> cs https://objectstorage.us-ashburn-1.example.com/n/test/b/example

DBMS_CLOUD Credential: SWIFTCRED                 
OCI Profile: Not Set
Transfer Method: dbms_cloud
URI as specified: https://objectstorage.us-ashburn-1.example.com/n/test/b/example

Use Case 2: When using the OCI Profile authentication scheme, the OCI profile is set and transfer method is set to oci.

--Set the OCI profile
SQL> cs oci demo
--Set Cloud Storage URI to the bucket

SQL> cs https://objectstorage.us-ashburn-1.example.com/n/test/b/example

DBMS_CLOUD Credential: Not Set            
OCI Profile: Demo
Transfer Method: oci
URI as specified: https://objectstorage.us-ashburn-1.example.com/n/test/b/example

--Create Table "locations"
SQL> create table locations(location_id NUMBER(5),location_name VARCHAR2(40));
Table LOCATIONS created

--Load data from LOCATIONS_DATA_TABLE.csv in cloud storage into "locations" table of the schema for the current user
SQL> load locations cs /o/LOCATIONS_DATA_TABLE.csv

format csv

column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names

--Number of rows processed: 50
--Number of rows in error: 0
--Last row processed in final committed batch: 50
0 - SUCCESS: Load processed without errors

SQL> select count(*) from locations;

COUNT(*)
–--------
50

--Load data from LOCATIONS_DATA_TABLE.csv in your current local folder into "locations" table 
  of the schema for the current user
SQL> load locations cs LOCATIONS_DATA_TABLE.csv

Unload Table Data into Cloud Storage

The following example shows how to unload data from the locations table into cloud storage. The data is unloaded to the cloud storage location and is named LOCATIONS_DATA_TABLE.csv.

--Set Cloud Storage URI to the bucket
SQL> cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example

–-Specify delimiter as | and terminator as <eol>
SQL> set loadformat delimiter | term <eol> csv

--Unload locations table into cloud storage
SQL> unload locations cs

DBMS_CLOUD Credential: SWIFTCRED            --Using DBMS_CLOUD authentication
OCI Profile: Not Set
Transfer Method: dbms_cloud
URI as specified: https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/
Final URI: https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/

format csv

column_names on
delimiter |
enclosure_left "
enclosure_right "
encoding UTF8
row_terminator <eof>

** UNLOAD Start ** at 2020.09.10-17.08.34
Export Separate Files to https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
DATA TABLE LOCATIONS
File Name: LOCATIONS_DATA_TABLE.csv
Number of Rows Exported: 23
** UNLOAD End ** at 2020.09.10-17.08.39

-- Show file contents
SQL> cs peek /o/LOCATIONS_DATA_TABLE.csv

"LOCATION_ID"|"LOCATION_NAME"<eof>01|"California"<eof>02|"Alaska<eof>03|"Florida"<eof>04|"Texas"<eof>05|"Virginia"<eof>
06|"Louisiana"<eof>07|"Massachusetts"<eof>08|"Arkansas"<eof>09|"Oregon"<eof>10|"Hawaii"<eof>11|"Maryland"<eof>
12|"Pennsylvania"<eof>13|"Colorado<eof>14|"Michigan"<eof>15|"Minnesota"<eof>16|"Ohio"<eof>17|"Maine"<eof>18|"Arizona"<eof>
19|"Missouri"<eof>20|"Vermont"<eof>21|"Kansas"<eof>22|"Nevada"<eof>23|"Illinois"

List Compartments, Buckets, and Objects

The following example shows how to list compartments, buckets, and objects in cloud storage:

Note:

When using DBMS_CLOUD credential, you can only list the objects in the bucket.DBMS_CLOUD transfer method does not support listing compartments and buckets.

cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test

--Lists the compartments in the namespace
SQL> cs listc

COMP_APEX
dbtst
old
ords
pdf-fn-compartment
sandbox
sqldev

--Lists the compartments in the compartment sqldev
SQL> cs listc sqldev

sqldev1

--Lists the buckets in the compartment sqldev
SQL> cs listb sqldev

example
test 

--Lists the objects in the bucket transfer
SQL> cs listo /b/example

COUNTRIES_DATA_TABLE.csv
DEPARTMENTS_DATA_TABLE.csv
REGIONS_DATA_TABLE.csv
LOCATIONS_DATA_TABLE.csv

--Displays the first 2000 characters of LOCATIONS_DATA_TABLE file
SQL> cs peek /o/LOCATIONS_DATA_TABLE.csv
 

Load a Local File into Cloud Storage using the PUT command

The following example shows how to load a local file into cloud storage using the CS PUT command and the Cloud Storage command:

--Set Cloud Storage URI to the bucket
SQL> cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example

--Put an object from local directory into cloud storage
SQL> cs put C:\\Users\\JDOE\\EMPLOYEES_DATA_TABLE.csv /o/myemployees.csv

Put C:\\Users\\JDOE\\EMPLOYEES_DATA_TABLE.csv copied
        to https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/myemployees.csv

--List the objects in the bucket
SQL> cs listo

COUNTRIES_DATA_TABLE.csv
DEPARTMENTS_DATA_TABLE.csv
REGIONS_DATA_TABLE.csv
LOCATIONS_DATA_TABLE.csv
myemployees.csv

Get a File from Cloud Storage using the GET Command

The following example shows how to get a file from cloud storage using the CS GET command and the Cloud Storage command:

--Set Cloud Storage URI to the bucket
SQL> cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/

--Set the local directory
SQL> cd C:\\Users\JDOE\\TempCS

--Get the myemployees.csv file from cloud storage
SQL> cs get /o/myemployees.csv

Get Object https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/myemployees.csv
copied to C:\Users\JDOE\TempCSemployees.csv

Delete a File in Cloud Storage using the DELETE Command

The following example shows how to delete a file in cloud storage using the CS DELETE command and the Cloud Storage command:

--Set Cloud Storage URI to the bucket
SQL> cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/

--Set the local directory
SQL> cd C:\\Users\JDOE\\TempCS

--Delete the myemployees.csv file in cloud storage
SQL> cs delete /n/test/b/example/o/myemployees.csv

Deleted Object
https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/myemployees.csv 

Grant a DBMS_CLOUD Credential to Another User

The following example shows how to create and grant a DBMS_CLOUD credential to another user:

-- Create a DBMS_CLOUD credential using oci-profile
SQL> dbcc create jdoecred profile freedb

DBMS_CLOUD Credential: JDOECRED
OCI Profile: Not Set
Transfer Method: dbms_cloud
URI as specified: https://objectstorage.us-ashburn-1.example.com/n/test/b/example

-- Grant jdoecred to user jdoe
SQL> grant all on jdoecred to jdoe;

-- Connect to the Cloud instance as jdoe
SQL> connect jdoe/********@adw20200526_high

-- Create a synonym for jdoecred
SQL> create synonym mycred for admin.jdoecred;

-- Set the transfer method using the synonym mycred
SQL> cs dbc mycred

-- User can now list the objects in the Cloud Storage 'test' bucket 
because jdoe.mycred exists (as synonym)
SQL> cs listo