How to download multiple files together from OCI Object Storage to Oracle Autonomous Database Directory such as DATA_PUMP_DIR
BaskarThis 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.
Subscribe to my newsletter
Read articles from Baskar directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by