8.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> SET CLOUDSTORAGE TRANSFER DBMS_CLOUD
SQL> SET CLOUDSTORAGE CREDENTIAL swiftcred

--Set Cloud Storage URI to the bucket
SQL> SET CLOUDSTORAGE URI 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
--The current OCI profile is automatically reused by Cloud Storage operations.
--Set Cloud Storage URI to the bucket
SQL> SHOW CLOUDSTORAGE PROFILE

--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> SET CLOUDSTORAGE URI 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 list -compartment

COMP_APEX
dbtst
old
ords
pdf-fn-compartment
sandbox
sqldev

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

sqldev1

--Lists the buckets in the compartment sqldev
SQL> cs list -bucket sqldev

example
test 

--Lists the objects in the bucket transfer
SQL> cs list -object 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
 

Note:

When using the DBMS_CLOUD transfer method, only object listing is supported. Listing compartments and buckets is supported only with OCI transfer.

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> SET CLOUDSTORAGE URI https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example

--Put an object from local directory into cloud storage
SQL> cs put -q myemployees.csv C:\Users\JDOE\EMPLOYEES_DATA_TABLE.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 list -object

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> SET CLOUDSTORAGE URI 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 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> SET CLOUDSTORAGE URI 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 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> SET CLOUDSTORAGE TRANSFER DBMS_CLOUD
SQL> SET CLOUDSTORAGE CREDENTIAL mycred

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

Override the Configured URI

The following example lists objects in the default cloud storage bucket and then lists objects in another bucket by overriding the configured URI.

SQL> set cloudstorage uri https://objectstorage.us-ashburn-1.oraclecloud.com/n/example_namespace/b/default_bucket/o/
SQL> set cloudstorage transfer oci
SQL> cs list -object
SQL> cs list -object -uri https://objectstorage.us-ashburn-1.oraclecloud.com/n/example_namespace/b/other_bucket/o/