DBMS_CLOUD Package in Oracle: How to Get, Put, and Delete Files

If you work with Oracle Cloud Infrastructure and need to move files between your database and object storage, the DBMS_CLOUD package is the tool you will reach for most often. I have used it across several projects, and once you understand how it works, managing files in Oracle Cloud becomes surprisingly straightforward.

What Is the DBMS_CLOUD Package?

DBMS_CLOUD is a built-in PL/SQL package available in Oracle Autonomous Database. It gives you a clean set of procedures and functions to interact with cloud object storage services directly from your SQL or PL/SQL code.

Think of it as a bridge between your Oracle database and external storage like Oracle Object Storage, Amazon S3, Azure Blob Storage, or Google Cloud Storage. You do not need any third-party tools or external scripts to move data around.

Why I Use DBMS_CLOUD for File Operations

Before DBMS_CLOUD, loading files into an Oracle database or exporting data to storage required a lot of manual steps. Now I can handle uploads, downloads, and deletions all from within a PL/SQL block.

"The best database tools are the ones that get out of your way," and DBMS_CLOUD lives up to that idea. It keeps the syntax clean and the workflow simple.

Setting Up Credentials First Using DBMS_CLOUD

Before you can get, put, or delete any files, you need to create a credential object. This credential stores your access keys for whichever cloud storage provider you are using. Here is the general setup I follow:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_OCI_CRED',
    username        => 'your-tenancy/your-username',
    password        => 'your-auth-token'
  );
END;
/

You only need to create the credential once per environment. After that, you reference it by name in all your file operations.

How to PUT a File Into Object Storage Using DBMS_CLOUD Package

The PUT_OBJECT procedure lets me upload a file from the database directory to cloud object storage. This is something I use regularly for exporting reports and data files.

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'MY_OCI_CRED',
    object_uri      => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/report.csv',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'report.csv'
  );
END;
/

Here is a quick breakdown of the key parameters I pass to PUT_OBJECT:

The file gets uploaded to your bucket with the exact name specified in the object_uri. If a file with that name already exists, it gets overwritten.

Putting a File Without a Directory Name

There is an overloaded version of PUT_OBJECT that does not require a directory_name or file_name at all. Instead, I pass the file content directly as a BLOB using the contents parameter. This is useful when I have data already in memory and do not want to write it to a directory first.

DECLARE
  l_content BLOB;
BEGIN
  -- Build or assign your BLOB content here
  l_content := UTL_RAW.CAST_TO_RAW('column1,column2' || CHR(10) || 'value1,value2');

  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'MY_OCI_CRED',
    object_uri      => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/inline-data.csv',
    contents        => l_content
  );
END;
/

In this example, I construct the file contents as a RAW value and pass it directly. There is no directory involved at all. The data goes straight from memory into the object storage bucket at the URI I specify.

This approach is great for small generated files or situations where I am building the content dynamically inside PL/SQL and do not want the overhead of writing to a database directory first.

How to GET a File From Object Storage Using DBMS_CLOUD

When I need to pull a file down from cloud storage into a database directory, I use GET_OBJECT. This is useful for loading data files that get dropped into a bucket by an external process.

BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'MY_OCI_CRED',
    object_uri      => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/input-data.csv',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'input-data.csv'
  );
END;
/

Once the procedure completes, the file is available in the Oracle directory you specified. From there, I can use standard SQL loader tools or external tables to process the data.

Getting a File Without a Directory Name

Just like PUT_OBJECT, GET_OBJECT has an overloaded form that works without any directory. In this version, GET_OBJECT is used as a function rather than a procedure. It returns the file content directly as a BLOB, which I can then process entirely in PL/SQL.

DECLARE
  l_content BLOB;
  l_text    VARCHAR2(32767);
BEGIN
  l_content := DBMS_CLOUD.GET_OBJECT(
    credential_name => 'MY_OCI_CRED',
    object_uri      => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/config.json'
  );

  -- Convert the BLOB to a readable string
  l_text := UTL_RAW.CAST_TO_VARCHAR2(l_content);
  DBMS_OUTPUT.PUT_LINE(l_text);
END;
/

I use this form when I want to read a small config file or a JSON payload from object storage and work with it as a string inside PL/SQL. There is no need to write the file to a directory and then read it back. I get the content directly in a variable.

Here is a comparison of the two GET_OBJECT forms so it is easy to see when to use each one:

How to DELETE a File From Object Storage Using DBMS_CLOUD

Deleting a file from cloud object storage is just as simple. The DELETE_OBJECT procedure removes the specified object from the bucket permanently.

BEGIN
  DBMS_CLOUD.DELETE_OBJECT(
    credential_name => 'MY_OCI_CRED',
    object_uri      => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/old-report.csv'
  );
END;
/

I use this after a file has been successfully processed. It keeps the bucket tidy and avoids unnecessary storage costs.

Listing Files in a Bucket Using DBMS_CLOUD

Sometimes I need to see what files are already sitting in a bucket before deciding what to get or delete. The LIST_OBJECTS function returns a table of objects I can query directly.

SELECT object_name, bytes, created
FROM TABLE(
  DBMS_CLOUD.LIST_OBJECTS(
    credential_name => 'MY_OCI_CRED',
    location_uri    => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/'
  )
);

This gives me a live view of what is in the bucket, including file sizes and creation timestamps. I find this especially useful when automating file processing pipelines.

Common Use Cases for DBMS_CLOUD

Here are the scenarios where I rely on DBMS_CLOUD the most:

• Uploading data exports or backups to object storage on a schedule
• Downloading incoming data feeds from partners who drop files into a bucket
• Cleaning up processed files to keep storage organized
• Loading CSV or JSON files directly into Oracle tables using COPY_DATA
• Automating multi-step ETL pipelines entirely within PL/SQL

The fact that all of this runs natively inside the database is a huge advantage. I do not need to spin up any middleware or external scripts.

Error Handling in DBMS_CLOUD Operations

Like any database operation, I always wrap my DBMS_CLOUD calls in proper exception handling. If a file does not exist in the bucket or the credential is wrong, the procedure raises an exception that I need to catch.

BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'MY_OCI_CRED',
    object_uri      => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/my-bucket/o/data.csv',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'data.csv'
  );
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Good error handling saves a lot of troubleshooting time, especially in automated nightly jobs where no one is watching the output in real time.

Supported Cloud Storage Providers

One thing I appreciate about DBMS_CLOUD is that it is not locked into Oracle Object Storage. The package supports multiple providers, which gives me flexibility depending on where my data lives.

The URI format changes slightly depending on the provider, but the procedure names and parameters stay the same. That consistency is one of the things I like most about the package.

Conclusion

The DBMS_CLOUD package in Oracle Autonomous Database makes it easy to manage files in cloud object storage without leaving your SQL environment. Using PUT_OBJECT, GET_OBJECT, and DELETE_OBJECT, I can handle the complete lifecycle of a file from a single PL/SQL block.

What I find especially powerful is the flexibility to skip the database directory entirely when I have content in memory or want a BLOB back directly. These overloaded forms keep the code clean and reduce unnecessary I/O. Once you get your credentials set up, the rest is just picking the right form for the job.