ADB_list_buckets_with_RESOURCE_PRINCIPAL.sql

DECLARE
   l_comp_ocid varchar2(200);
   l_tenant_ocid varchar2(200);
   all_buckets DBMS_CLOUD_OCI_IDENTITY_COMPARTMENT_TBL;

   function lsComp(p_tenant_ocid varchar2,
                   p_region      varchar2,
                   p_cred        varchar2 default 'OCI$RESOURCE_PRINCIPAL',
                   p_page varchar2 default null) return DBMS_CLOUD_OCI_IDENTITY_COMPARTMENT_TBL
   as
    l_nextPage varchar2(2048);
    l_ret DBMS_CLOUD_OCI_IDENTITY_COMPARTMENT_TBL;
    l_tmp DBMS_CLOUD_OCI_IDENTITY_COMPARTMENT_TBL;
    l_comps dbms_cloud_oci_id_identity_list_compartments_response_t;

   begin
       --
       -- list 1st page of bucket names
       --
        l_comps := DBMS_CLOUD_OCI_ID_IDENTITY.list_compartments ( p_tenant_ocid         
                , region => p_region
                , credential_name => p_cred
                , page => p_page
                , limit=>100);        
        --
        -- populated when there's another page
        --
        l_nextPage := l_comps.headers.get_string('opc-next-page');
        l_ret := l_comps.response_body;
        --
        -- if there's page 2 get it
        --
        if ( l_nextPage is not null ) then 
          l_tmp := lsComp(p_tenant_ocid,p_region,p_cred,l_nextPage);
          l_ret := l_ret MULTISET UNION l_tmp;
        end if;
        return l_ret;
  end;
begin 
    --
    -- Get tenancy ocid and adb's compartment  
    --
    select json_value(cloud_identity,'$.TENANT_OCID') ,
          json_value(cloud_identity,'$.COMPARTMENT_OCID')
    INTO  l_tenant_ocid , l_comp_ocid
    from v$pdbs;

   --
   -- get all the buckets 
   --
   all_buckets := lsComp(l_tenant_ocid,'us-phoenix-1');


    for i in 1..all_buckets.count
    loop
        dbms_output.put_line('bucket: ' || all_buckets(i).name);
    end loop;

end;
/