Migration methods to Autonomous Database (ADB) - Part IV (DBMS_CLOUD.COPY_DATA)

In this part of Migration methods to Autonomous Database (ADB) article series, we will see how to load data into ADB database using the DBMS_CLOUD package.

Assuming you have an ADB database up & running and you wanna load data from the file stored in any of the supported object storage. The image below from Oracle presentation depicts explains what type of object storage is supported to store the files, and settings required part of DBMS_CLOUD package to load the data.



Below image depicts the important procedures and table details to monitor the loading jobs:


You may review my earlier articles to understand how to load files into an Oracle object storage. Once you have the files put in a bucket, you then need to store the OSS credentials in the ADB database using the example below:



And finally use the example below to load the data into the table:

BEGIN

DBMS_CLOUD.COPY_DATA(

table_name =>'CHANNELS',

credential_name =>DEF_CRED_NAME,

file_uri_list =>'https://swiftobjectstorage.<region>.oraclecloud.com/v1/<tenancy>/<bucketname>/<filename>',

format => json_object('delimiter' value ',')

);

END;

/

If you are loading bulk data, you can query table below to know data loading operation progress:

SELECT * 
FROM  user_load_operations
WHERE type = 'COPY';

SELECT table_name, owner_name, type, status, logfile_table,

  badfile_table

FROM user_load_operations WHERE type = 'COPY';

Tables below provide information about log file and bad file of data load operations:

SELECT * FROM copy$1_log;
SELECT * FROM copy$1_bad;

So, we have learned how to load data into ADB using the DBMS_CLOUD package.