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.

https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fr5p8xjztfov/b/obstore_bucket/o/sh_table_datachannels.csv

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.

https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fr5p8xjztfov/b/obstore_bucket/o/sh_table_datachan_v3.dat

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.

 

Reklam