Executing OCI object storage operations (rename, copy, move, …) from an Autonomous Database with PL/SQL code


Following the work started here, we continue depicting how easy is to execute operations in OCI Object Storage buckets.

Credential

Credential is needed for the api signing:

-- for oci rest api's we need a credential for signing api
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'DEVCSCRED',
user_ocid => 'ocid1.user.oc1..aaaaa...yw77q',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaa...6jj5ruqqq',
private_key => 'MIIEogIBAA...hGuCkA=',
fingerprint => 'e2:...f:90');
END;
/

Rename, move (copy), delete

declare
resp DBMS_CLOUD_TYPES.resp;
osnamespace varchar2(32) := 've...xt';
region varchar2(32) := 'eu-frankfurt-1';
osendpoint varchar2(64) := 'https://objectstorage.eu-frankfurt-1.oraclecloud.com';
originbucket varchar2(32) := 'sb...ng';
destbucket varchar2(32) := 'sb..ed';

begin
-- renames a file in bucket
resp := DBMS_CLOUD.send_request(credential_name => 'DEVCSCRED',uri =>
osendpoint || '/n/ver...xt/b/sb...ng/actions/renameObject',
method => 'POST', body => UTL_RAW.cast_to_raw(JSON_OBJECT(
'sourceName' value 'm-VS001-00000000.txt',
'newName' value 'p-m-VS001-00000000.txt'
)));
dbms_output.put_line('-------');
dbms_output.put_line('result: ' || DBMS_CLOUD.get_response_text(resp));

-- copyes a file from a bucket to another
resp := DBMS_CLOUD.send_request(credential_name => 'DEVCSCRED',uri =>
osendpoint || '/n/ver...xt/b/sb...ng/actions/copyObject',
method => 'POST', body => UTL_RAW.cast_to_raw(JSON_OBJECT(
'sourceObjectName' value 'p-m-VS001-00000000.txt',
'destinationRegion' value region,
'destinationNamespace' value osnamespace,
'destinationBucket' value destbucket,
'destinationObjectName' value 'c-p-m-VS001-00000000.txt'
)));
dbms_output.put_line('-------');
dbms_output.put_line('result: ' || DBMS_CLOUD.get_response_text(resp));

-- deletes a file
resp := DBMS_CLOUD.send_request(credential_name => 'DEVCSCRED',uri =>
osendpoint || '/n/ver...xt/b/sb...ng/o/p-m-VS001-00000000.txt',
method => 'DELETE');
dbms_output.put_line('-------');
dbms_output.put_line('result: ' || DBMS_CLOUD.get_response_text(resp));

end;

Enjoy 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.