How to list all the Objects from OCI Bucket Storage via SQL Query in Autonomous Database

The following queries can be used to list all the objects from your OCI Bucket Storage.

-- First Create a Credential using the following code.
-- In the below code, the 'username' parameter expects your "Tenancy" Username which you can find in your OCI 'My Profile' page.
-- In the below code, the 'password' parameter expects your OCI "Auth Tokens" password.
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_CLOUD_CREDENTIAL',
    username => 'identitycloudservice/basbabu@xyz.com',
    password => '+B4)U(d-GgFXiFkh53GS' );
END;
/

-- Use the below SQL to list the contents from your Bucket
-- In the below code, the 'credential_name' parameter expects your 'Credential Name' which you have created in the above code.
-- In the below code, the 'location_uri' parameter expects your BUCKET PAR URL or the URL in the below format
-- https://objectstorage.<region_identifier>.oraclecloud.com/n/<tenancy_name>/b/<bucket_name>/o/
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_objects
(
  credential_name => 'MY_CLOUD_CREDENTIAL',
  location_uri    => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/apacpaas/b/BaskarBabu_Bkt/o/'
);

OBJECT_NAME                                                       BYTES CREATED                                  LAST_MODIFIED
------------------------------------------------------------ ---------- ---------------------------------------- ----------------------------------------
Installing_Oracle_Analytics_Server.pdf                           638750                                          17-MAY-24 08.07.45.996000 AM +00:00
Oracle_AI_Vector_Search_User_Guide.pdf                          2956343                                          17-MAY-24 08.07.46.339000 AM +00:00
Oracle_Autonomous_Database.pdf                                 25701941                                          17-MAY-24 08.07.49.848000 AM +00:00
Oracle_Database_23ai_Concepts.pdf                              10302049                                          17-MAY-24 08.07.47.676000 AM +00:00
Sample-Sales-Data.pdf                                            122279                                          17-MAY-24 08.07.45.840000 AM +00:00

5 rows selected.

SQL>