Download file from Object Storage and Insert into ATPDB

5 min readNov 23, 2022

There are many common requirements to download a file from Object Storage and Insert records into ATP DB. Below steps will help you to create a bucket in Oracle Cloud & create a respective connection and implement the requirement.

First open your Oracle cloud account, then click on Storage, Object Storagethen Bucket. Now click on new Bucket. For now keep all default configuration As Is.

Once your bucket created, note down your Namespace and Bucket Name. Also by default Visibility will be Private, which you can make it Public using Edit Visibility.

Now generate an RSA Key pairs and its fingerprint, as mentioned in OCI documentation. I have used cloud shell to generate my RSA keys, but you can use any third party software.

Run below command to generate the Private key:

openssl genrsa -out oci_api_key_private.pem -aes128 2048 (with passphase)
openssl genrsa -out oci_api_key_private.pem 2048 (without passphase)

Now run below command to generate the Public key:

openssl rsa -pubout -in oci_api_new_private.pem -out oci_api_new_public.pem

Now you need to generate your API Key to connect the Object Storage from OIC connection. For that Click on Identity then User and select your User. Now click API Keys.

Now click Add API Key, where you can choose 3 options

Generate API Key Pair
Choose Public Key File
Paste Public Key

We will paste Public Key(oci_api_new_public.pem) which we already created in previous steps.

Once API Key Added Note down User OCID, Fingerprints, and Tenancy OCID & Region as below :

***Make sure Private Key is RSA format. If your private key is in different format, convert the Private Key from PKCS8 to RSA (PKCS1) Format for the OCI Signature Version 1 Security Policy. I have used below online tool to convert my Private Key to RSA format.

https://decoder.link/rsa_converter

Now create a REST Connection as below :

Connection URL will be based on region. As I am using Hyderabad region, so used below connection

https://objectstorage.ap-hyderabad-1.oraclecloud.com

Select OCI signature Version 1 as Security Policy and provide required details like Tenancy OCID, User OCID, Private Key(RSA), Finger Print and Pass Phase(if required)

Now create another ATPDB connection with Wallet Credentials. For that go to Autonomous Database and download wallet. Use the same wallet while configurating OIC connection.

Now create a Schedule or App Driven Orchestration Integration to invoke Object Storage to retrieve CSV files and store respective records into ATP DB.

First use OCI_ObjectStorageConn to invoke Object storage to list of files. In below screenshot you might observe we need namespace & bucketName to retrieve data from Object Storage.

Your might use a Query Parameters prefix to fetch only required data. For example we might need to retrieve UserSet1.csv, UserSet2.csv. Then You need to pass prefix as “User”. For more details you might take reference from Oracle Docs.

Also you need to configure response parameters as below :

Now create a for loop for each object listed from Object Storage and invoke again to download the file into Stage location. Now you need to pass the Object name in Relative URL to retrieve the file.

And response will be in Binary format as entire file need to be downloaded in stage location.

Now Read the file from Object Storage File reference and create new schema.

I have already create a sample Emp table with fields userId, fName, lName, emailId & age. Now will configure ATP DB connection to inser these records into it. We will use insert operation to insert data into EMP table: