How to download/transfer files from OCI Object Storage to Oracle Autonomous Database Directory such as DATA_PUMP_DIR
This article guides you with the commands for downloading multiple files together from OCI Object Storage to Oracle Autonomous Database Directory Object such as DATA_PUMP_DIR.
Remember that we don't have OS access for our Autonomous Serverless Database. So, to download the files from OCI Object Storage to Autonomous Database DATA_PUMP_DIR directory, use the package called "DBMS_CLOUD".
Let's first see the list the files stored in your Autonomous Database DATA_PUMP_DIR directory.
set lines 250 pages 5000 col object_name format a60 col created format a40 col last_modified format a40 SELECT object_name, bytes, created, last_modified FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'); -- Output: no rows selected -- Currently, there are no files in my DATA_PUMP_DIR location.Now let's see how to download a single file from your OCI Bucket to Autonomous Database DATA_PUMP_DIR location. So, to do that, we should use a procedure called "DBMS_CLOUD.GET_OBJECT". And, before that, let me first show you the list of files which I am having in my Bucket Storage. And then you can use the following PL/SQL code to download a particular file called "potential_churners.csv".
In the below screenshot, copy the URL as that will be passed in the PL/SQL code. If this is not working for some reason, then you can create a PAR url and then use that URL in the code.
BEGIN DBMS_CLOUD.GET_OBJECT( credential_name => 'MY_CLOUD_CREDENTIAL', -- Here, You should pass the already created Credential Name. object_uri => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/mytenancy/b/BaskarBabu_Bkt/o/potential_churners.csv', directory_name => 'DATA_PUMP_DIR'); END; /Let's again verify the list of files from DATA_PUMP_DIR directory. You may observe, from the output, that the file "potential_churners.csv" is now downloaded from your OCI Bucket Storage to Autonomous Database DATA_PUMP_DIR location.
set lines 250 pages 5000 col object_name format a60 col created format a40 col last_modified format a40 SELECT object_name, bytes, created, last_modified FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'); -- Output: OBJECT_NAME BYTES CREATED LAST_MODIFIED ------------------------------------------------------------ ---------- ---------------------------------------- ---------------------------------------- potential_churners.csv 2268419 07-JUL-24 07.36.27.158696 AM +00:00 07-JUL-24 07.36.28.535773 AM +00:00Let's now see how to download multiple files together from your OCI Bucket Storage to Oracle Autonomous Database DATA_PUMP_DIR location. So, to do that, we should use the procedure called "DBMS_CLOUD.BULK_DOWNLOAD". And, in this procedure, I am using a parameter called "regex_filter" using which you can download multiple files together. And, in this example, I am downloading all the PDF files together.
BEGIN DBMS_CLOUD.BULK_DOWNLOAD ( credential_name => 'MY_CLOUD_CREDENTIAL', location_uri => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/mytenancy/b/BaskarBabu_Bkt/o/', directory_name => 'DATA_PUMP_DIR', regex_filter => '.*\.pdf'); END; /Let's again verify the list of files from DATA_PUMP_DIR directory. You may observe, from the output, that all the PDF files from your OCI Bucket Storage is now downloaded to your Autonomous Database DATA_PUMP_DIR location.
set lines 250 pages 5000 col object_name format a60 col created format a40 col last_modified format a40 SELECT object_name, bytes, created, last_modified FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'); -- Output: OBJECT_NAME BYTES CREATED LAST_MODIFIED ------------------------------------------------------------ ---------- ---------------------------------------- ---------------------------------------- Oracle_Autonomous_Database.pdf 25701941 07-JUL-24 07.45.43.977573 AM +00:00 07-JUL-24 07.45.47.259860 AM +00:00 ElectronicBanking_ADB2426.pdf 448343 07-JUL-24 07.45.37.931236 AM +00:00 07-JUL-24 07.45.38.703588 AM +00:00 Oracle_AI_Vector_Search_User_Guide.pdf 2956343 07-JUL-24 07.45.41.601408 AM +00:00 07-JUL-24 07.45.42.991014 AM +00:00 potential_churners.csv 2268419 07-JUL-24 07.36.27.158696 AM +00:00 07-JUL-24 07.36.28.535773 AM +00:00 Sample-Sales-Data.pdf 122279 07-JUL-24 07.45.41.035590 AM +00:00 07-JUL-24 07.45.41.424286 AM +00:00 SavingsInvestment_ADB2852.pdf 1118930 07-JUL-24 07.45.42.363816 AM +00:00 07-JUL-24 07.45.43.545959 AM +00:00 Oracle_Database_23ai_Concepts.pdf 10302049 07-JUL-24 07.45.37.921749 AM +00:00 07-JUL-24 07.45.40.084021 AM +00:00 Installing_Oracle_Analytics_Server.pdf 638750 07-JUL-24 07.45.39.620862 AM +00:00 07-JUL-24 07.45.40.604061 AM +00:00 8 rows selected.Additionally, if you want to see the Bulk Downloading Operation details, you can use the dictionary view called "user_load_operations". The following SQL can be used to identify the bulk-downloaded operation details.
set lines 250 pages 5000 col owner_name format a18 col type format a15 col start_time format a40 col update_time format a40 col status_table format a20 SELECT owner_name, type, status, start_time, update_time, status_table, rows_loaded FROM user_load_operations WHERE type = 'DOWNLOAD'; -- Output: OWNER_NAME TYPE STATUS START_TIME UPDATE_TIME STATUS_TABLE ROWS_LOADED ------------------ --------------- --------- ---------------------------------------- ---------------------------------------- -------------------- ----------- "BASBABU" DOWNLOAD COMPLETED 07-JUL-24 07.45.33.732332 AM +00:00 07-JUL-24 07.45.49.165562 AM +00:00 DOWNLOAD$2_STATUS 7Thank you for reading this article. Hope, it is useful to progress your work.