Beginning with Oracle Database 26ai, Amazon RDS for Oracle natively supports the DBMS_CLOUD package. DBMS_CLOUD lets you work with data in Amazon S3 directly from SQL and PL/SQL: upload and download objects, list and delete objects, load data into tables, and query S3 data through external tables and hybrid partitioned tables.
If you have used DBMS_CLOUD on self managed Oracle 19c on Amazon EC2 before, the big change on RDS for Oracle 26ai is that there is nothing to install. RDS pre-installs DBMS_CLOUD (and DBMS_CLOUD_AI) and manages the TLS trust store for you. There is no catcon.pl install, no Amazon Root CA download, and no Oracle wallet or sqlnet.ora editing. You configure a credential and network ACEs, and you are ready.
In this post we walk through each DBMS_CLOUD S3 operation on RDS for Oracle 26ai using SQL*Plus (SQLcl works identically), so you can follow along.
Solution overview
- Confirm
DBMS_CLOUDis available (it is, natively). - Configure a credential and network access controls (ACEs).
- Create a sample schema.
- Upload data with
DBMS_CLOUD.PUT_OBJECT. - Download data with
DBMS_CLOUD.GET_OBJECT. - Load data with
DBMS_CLOUD.COPY_DATA. - Query S3 with
DBMS_CLOUD.CREATE_EXTERNAL_TABLE. - Combine S3 and database data with
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE. - Delete objects with
DBMS_CLOUD.DELETE_OBJECT.
Prerequisites
- An Amazon RDS for Oracle DB instance running Oracle Database 26ai (engine
oracle-ee-cdb, an26.0.0.0engine version). Oracle 26ai is Enterprise Edition and CDB-only. - An S3 bucket in the same Region.
- An IAM user with an access key, allowed to access the bucket.
- A SQL client (SQL*Plus or SQLcl) with network connectivity to the DB instance endpoint on port 1521.
The following placeholders are used throughout:
| Placeholder | Meaning |
|---|---|
<db-endpoint> | RDS instance endpoint host |
<pdb-service> | PDB service name (default for a new 26ai instance is the DB name) |
<admin-user> / <admin-password> | RDS master user and password |
<bucket> | S3 bucket name |
<region> | AWS Region, e.g. us-east-1 |
<access-key-id> / <secret-access-key> | IAM access key for the credential |
<db-user> / <db-password> | least-privilege application user |
<db-role> | application role |
IAM policy attached to the IAM user:
{
"Version": "2012-10-17",
"Statement": [{
"Sid": "RDSOracleS3Policy",
"Effect": "Allow",
"Action": ["s3:PutObject","s3:GetObject","s3:ListBucket","s3:DeleteObject"],
"Resource": ["arn:aws:s3:::<bucket>","arn:aws:s3:::<bucket>/*"]
}]
}
This solution creates and uses AWS resources (RDS, S3, data transfer) that incur cost. See AWS Pricing, and clean up when you are done.
Step 1: Confirm DBMS_CLOUD is available
Connect to the PDB as the master user and check the package status:
sqlplus <admin-user>/<admin-password>@<db-endpoint>:1521/<pdb-service>
SELECT banner FROM v$version;
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN ('DBMS_CLOUD','DBMS_CLOUD_AI')
ORDER BY object_name, object_type;
Expected output:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
OBJECT_NAME OBJECT_TYPE STATUS
--------------- --------------- -------
DBMS_CLOUD PACKAGE VALID
DBMS_CLOUD PACKAGE BODY VALID
DBMS_CLOUD SYNONYM VALID
DBMS_CLOUD_AI PACKAGE VALID
DBMS_CLOUD_AI PACKAGE BODY VALID
DBMS_CLOUD_AI SYNONYM VALID
The packages are pre-installed and owned by the RDS-managed C##CLOUD$SERVICE user. No installation, no wallet setup.
Step 2: Create a least-privilege user, role, and ACEs
As the master user:
CREATE USER <db-user> IDENTIFIED BY "<db-password>" DEFAULT TABLESPACE users;
ALTER USER <db-user> QUOTA UNLIMITED ON users;
CREATE ROLE <db-role>;
GRANT CREATE SESSION, CREATE TABLE TO <db-role>;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO <db-role>;
GRANT EXECUTE ON DBMS_CLOUD TO <db-role>;
GRANT <db-role> TO <db-user>;
-- Allow outbound HTTPS to the S3 regional endpoint.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 's3.<region>.amazonaws.com',
lower_port => 443, upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http','http_proxy'),
principal_name => UPPER('<db-role>'),
principal_type => xs_acl.ptype_db));
END;
/
Notes:
DATA_PUMP_DIRalready exists on RDS for Oracle; you just grantREAD, WRITE.- On RDS the master user can run
DBMS_NETWORK_ACL_ADMINdirectly in the PDB. - For
DBMS_CLOUD(S3), an ACE granted to the role is honored.
Step 3: Create the credential and verify connectivity
Connect as the application user:
sqlplus <db-user>/<db-password>@<db-endpoint>:1521/<pdb-service>
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'CRED_S3',
username => '<access-key-id>',
password => '<secret-access-key>');
END;
/
-- Verify the credential + native TLS by listing the (empty) bucket
SELECT object_name, bytes
FROM DBMS_CLOUD.LIST_OBJECTS(
credential_name => 'CRED_S3',
location_uri => 'https://s3.<region>.amazonaws.com/<bucket>/');
-- no rows selected (no certificate errors -> RDS manages the trust store)
Step 4: Create a sample schema
CREATE TABLE emp (id int primary key, name varchar2(10), salary int, last_join date);
INSERT INTO emp VALUES(1,'user 1',1000, DATE '2000-01-01');
INSERT INTO emp VALUES(2,'user 2',2000, DATE '2001-01-01');
COMMIT;
DECLARE
CURSOR c IS SELECT id,name,salary, TO_CHAR(last_join,'YYYY-MM-DD') last_join FROM emp;
f UTL_FILE.file_type;
BEGIN
f := UTL_FILE.FOPEN('DATA_PUMP_DIR','emp.csv','w');
FOR r IN c LOOP
UTL_FILE.PUT_LINE(f, r.id||','||r.name||','||r.salary||','||r.last_join);
END LOOP;
UTL_FILE.FCLOSE(f);
END;
/
Step 5: Upload data with PUT_OBJECT
BEGIN
DBMS_CLOUD.PUT_OBJECT(
credential_name => 'CRED_S3',
object_uri => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/emp.csv',
directory_name => 'DATA_PUMP_DIR',
file_name => 'emp.csv');
END;
/
SELECT object_name, bytes, last_modified
FROM DBMS_CLOUD.LIST_OBJECTS(
credential_name => 'CRED_S3',
location_uri => 'https://s3.<region>.amazonaws.com/<bucket>/')
WHERE object_name = 'orcl/emp.csv';
OBJECT_NAME BYTES LAST_MODIFIED
--------------- ------- ---------------------------------
orcl/emp.csv 50 <timestamp> +00:00
You can also write a BLOB straight to S3 without a local file:
DECLARE my_lob BLOB;
BEGIN
my_lob := UTL_RAW.CAST_TO_RAW('some data you want to write to S3 file directly');
DBMS_CLOUD.PUT_OBJECT(
credential_name => 'CRED_S3',
object_uri => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/mylob.dat',
contents => my_lob);
END;
/
Step 6: Download data with GET_OBJECT
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => 'CRED_S3',
object_uri => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/emp.csv',
directory_name => 'DATA_PUMP_DIR',
file_name => 'emp2.csv');
END;
/
SELECT TO_CLOB(
DBMS_CLOUD.GET_OBJECT(
credential_name => 'CRED_S3',
object_uri => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/mylob.dat')) AS mylob
FROM dual;
-- MYLOB: some data you want to write to S3 file directly
Step 7: Load data with COPY_DATA
CREATE TABLE emp_copy (id int primary key, name varchar2(10), salary int, last_join date);
BEGIN
DBMS_CLOUD.COPY_DATA(
table_name => 'emp_copy',
credential_name => 'CRED_S3',
file_uri_list => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/emp.csv',
format => JSON_OBJECT('type' VALUE 'csv','dateformat' VALUE 'YYYY-MM-DD'));
END;
/
SELECT * FROM emp_copy ORDER BY id;
-- 1 user 1 1000 01-JAN-00
-- 2 user 2 2000 01-JAN-01
Step 8: Query S3 with CREATE_EXTERNAL_TABLE
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name => 'emp_ext',
credential_name => 'CRED_S3',
file_uri_list => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/emp.csv',
column_list => 'id int, name varchar2(10), salary int, last_join date',
format => JSON_OBJECT('type' VALUE 'csv','dateformat' VALUE 'YYYY-MM-DD'));
END;
/
SELECT * FROM emp_ext ORDER BY id; -- rows read live from S3
Step 9: Combine S3 and database data with CREATE_HYBRID_PART_TABLE
BEGIN
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
table_name => 'emp_hpt',
credential_name => 'CRED_S3',
format => JSON_OBJECT('type' VALUE 'csv','dateformat' VALUE 'YYYY-MM-DD'),
column_list => 'id int, name varchar2(10), salary int, last_join date',
partitioning_clause =>
'partition by range(last_join) '||
'(partition p1 values less than (to_date(''2002-01-01'',''YYYY-MM-DD'')) '||
' external location (''https://s3.<region>.amazonaws.com/<bucket>/orcl/emp.csv''), '||
' partition p2 values less than (to_date(''2003-01-01'',''YYYY-MM-DD'')))');
END;
/
SELECT * FROM emp_hpt ORDER BY id; -- rows from S3
INSERT INTO emp_hpt VALUES(3,'user 3',3000, DATE '2002-01-01'); COMMIT;
SELECT * FROM emp_hpt ORDER BY id; -- S3 rows + database row
-- 1 user 1 1000 01-JAN-00
-- 2 user 2 2000 01-JAN-01
-- 3 user 3 3000 01-JAN-02
External and hybrid partitioned tables use the Oracle Partitioning option (included with Enterprise Edition).
Step 10: Delete objects with DELETE_OBJECT
BEGIN
DBMS_CLOUD.DELETE_OBJECT(credential_name => 'CRED_S3',
object_uri => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/emp.csv');
DBMS_CLOUD.DELETE_OBJECT(credential_name => 'CRED_S3',
object_uri => 'https://s3.<region>.amazonaws.com/<bucket>/orcl/mylob.dat');
END;
/
Clean up
Delete the DB instance, the S3 bucket and its contents, and the IAM user/keys to avoid ongoing charges.
Conclusion
On Amazon RDS for Oracle 26ai, DBMS_CLOUD is a native, managed capability. Every S3 operation from the original RDS Custom for Oracle post — put_object, get_object, list_objects, copy_data, create_external_table, create_hybrid_part_table, and delete_object — works as-is, with far less setup because RDS installs the package and manages TLS trust for you.
No comments:
Post a Comment