Access AWS S3 Bucket from Oracle Database

 If you want to access any object stored in an AWS S3 bucket from Oracle database, then dbms_cloud package is your friend, provided you are using Oracle Autonomous database. 

An AWS S3 (Simple Storage Service) bucket is a scalable storage resource in the cloud provided by Amazon Web Services. It allows you to store and retrieve any amount of data from anywhere on the web. S3 buckets are highly durable, secure, and can be configured to support various use cases including website hosting, data backup, and content distribution.

The DBMS_CLOUD package in Oracle Database is a PL/SQL package that provides procedures to interact with Oracle Cloud Infrastructure services, specifically for loading data into Oracle Database from external data sources such as object storage in Oracle Cloud. It enables seamless integration between Oracle Database and cloud-based storage for data loading operations, making it easier to ingest large volumes of data into the database from cloud-based sources. The package includes procedures like COPY_DATA for copying data from cloud storage to database tables, and CREATE_CREDENTIAL for managing credentials needed to access cloud storage securely. This package simplifies data loading tasks and enhances integration capabilities between Oracle Database and Oracle Cloud services.

In order to access the object from S3 bucket, make sure you have created a database user in Oracle with proper credentials, and also you have AWS IAM credentials to access S3 bucket.

Step 1: First, you need to create a credential in Oracle Database that allows access to your AWS S3 bucket. This credential will store the AWS access key ID and secret access key securely.

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

    credential_name => 'MY_AWS_CREDENTIAL',

    username => 'AWS_ACCESS_KEY_ID',

    password => 'AWS_SECRET_ACCESS_KEY'

  );

END;

/

Step 2: Now, you can use the DBMS_CLOUD package to select data from a file in your AWS S3 bucket and load it into a table in Oracle Database.

DECLARE

  v_data CLOB;

BEGIN

  v_data := DBMS_CLOUD.GET_OBJECT(

    credential_name => 'MY_AWS_CREDENTIAL',

    object_uri => 'https://s3.<region>.amazonaws.com/<bucket_name>/<file_key>'

  );

  -- Process the retrieved data (example: print the data)

  DBMS_OUTPUT.PUT_LINE(v_data);

END;

/

Enjoy.