Listing the content of an object storage bucket in OCI from an autonomous database with PL/SQL code

This post explains a specific use case, but it can be used for calling whatever existing OCI REST API fron the database.

DBMS_CLOUD

DBMS_CLOUD provides all you need to handle requests to OCI REST API’s.

What you need

Create and grab a private and public keys and also grab your tenancy ocid, get help from here

Create a user in OCI, grab the user ocid

Add the public key previously created to the user and grab the fingerprint

Give permissions to user

Create a group and add the user to it

Create a policy with the following statements:

allow group <yourgroup> to manage buckets in compartment <loquesea>
allow group <yourgroup> to manage objects in compartment <loquesea>

Create a credential

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'YOURCRED',
user_ocid => <userocid>,
tenancy_ocid => <tenancyocid>,
private_key => <privatekey>,
fingerprint => <fingerprint>);
END;
/

Execute the REST call

declare
resp DBMS_CLOUD_TYPES.resp;
begin
resp := DBMS_CLOUD.send_request(
credential_name => 'YOURCRED',
uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<namespace>/b/<bucketnamwe>/o',
method => 'GET');
dbms_output.put_line('result: ' || DBMS_CLOUD.get_response_text(resp) );
end;

So far, so good:

That’s all, hope it helps! 🙂