Let’s look at how to transfer data from our bucket in Object Storage to the tables under the sample schema in our Autonomous database. To do this, we first need to create a bucket.
Object Storage -> Create Bucket
For example, we load the data files of SH schema into our bucket in Object Storage.
Database sample schemas:
https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/toc.htm
https://github.com/oracle/db-sample-schemas/releases -> db-sample-schemas-19.2.zip
Creating an Object Store Auth Token:
OCI user Auth Token is required to read and load data from Object Storage.
Identity -> Users
OCI user name: orhaneripek@hotmail.com
My sample Auth Token: 78PJL#wEw0uM7hzlrPX8
BEGIN DBMS_CLOUD.drop_credential(credential_name => 'OBJ_STORE_CRED'); END; / BEGIN DBMS_CLOUD.create_credential ( credential_name => 'OBJ_STORE_CRED', username => 'orhaneripek@hotmail.com', password => '78PJL#wEw0uM7hzlrPX8' ) ; END; /
Loading Data Using the Data Import Wizard in SQL Developer:
We can load the data in Object Storage into a copy table using Sql Developer’s Data Import Wizard.
Load data from the Object Store using DBMS_CLOUD:
We can load the data in Object Storage into a copy table using DBMS_CLOUD.copy_data.
CREATE TABLE CHANNELS_CLOUD2 (
channel_id NUMBER NOT NULL,
channel_desc VARCHAR2(20) NOT NULL,
channel_class VARCHAR2(20) NOT NULL,
channel_class_id NUMBER NOT NULL,
channel_total VARCHAR2(13) NOT NULL,
channel_total_id NUMBER NOT NULL);
Table CHANNELS_CLOUD2 created
BEGIN
DBMS_CLOUD.copy_data(
table_name => 'CHANNELS_CLOUD2',
credential_name => 'OBJ_STORE_CRED',
file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fr5p8xjztfov/b/obstore_bucket/o/sh_table_datachan_v3.dat',
format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
);
END;
/
PL/SQL procedure successfully completed.
Orhan Eripek's Oracle Blog 