In our mixed cloud environment of AWS and Oracle Autonomous databases, sometimes it's necessary to move files/blobs between the database and AWS S3 storage. This post describes one way of moving them directly between the database and AWS S3 storage.
We'll start by setting up an AWS user and user group with the permissions needed. Then we'll create OCI credentials using PL/SQL and the DBMS_CLOUD package. Finally, we'll move some image blobs over and back between our Oracle database's PL/SQL environment and AWS S3.
Stage One - create an AWS group, a user and get access keys
Create a User Group and assign the permission policy AmazonS3FullAccess to it.
Using the "User Groups -> Create group" wizard, we'll create a new group called S3FullAccess2024. We'll also attach the Amazon-supplied policy "AmazonS3FullAccess" to this group
Create an S3 User
Login to your AWS Identity and Access Management (IAM) Console and create a new user. Let's call this user S3PLSQL. We'll follow the "Create User" wizard that we'll select from the Users option on the Identity and Access Management menu.
Create user - step 1: User details
Create user - step 2:
Set permissions by adding the user to the S3FullAccess2024 group
that we created earlier
Create user - step 3: Review and create user
Create user - finished: User created
Create and get your Access Key and Secret Key
Create Access key - step 1: Choose a use-case
Create Access key - step 2: Add an optional description
Create Access key - step 3: Retrieve the access key and secret
Stage Two - create an OCI credential using DBMS_CLOUD
grant execute on dbms_cloud to myuser;
begin dbms_cloud.create_credential
(credential_name => 's3test_cred', username => 'A*****************7U', password => 'y*********************************V');end;/
Stage Three - access and update an S3 bucket using PL/SQL and DBMS_CLOUD
Let's start by listing all the files in the bucket using DBMS_CLOUD.LIST_OBJECTS. This can be done with a simple SQL query.
We'll pass two parameters, the credentials that we created in Stage 2 and the path to the S3 bucket that we want to list.
-- list the contents of an S3 bucketselect f.* from dbms_cloud.list_objects ('s3test_cred' ,'https://s3.eu-west-1.amazonaws.com/plsql-s3/') f;
Query output - 3 jpegs
select f.*
© 2024 Niall Mc Phillips