DEV Community: Naveen

Integrating OIC with Databricks using AWS S3 (External Tables Approach)

Naveen — Mon, 18 May 2026 14:05:23 +0000

Overview

In modern data integration scenarios, seamless connectivity between cloud platforms is critical. One common requirement is integrating Oracle Integration Cloud (OIC) with Databricks for performing table-based operations.

However, a key limitation arises:

❗ Databricks does not provide native RDS-style connectivity for direct table operations from OIC.

To overcome this limitation, we implement a scalable and efficient architecture:
Proposed Solution Architecture
OIC → AWS S3 → Databricks (External Tables)

This approach leverages AWS S3 as an intermediate storage layer, allowing OIC to push data, which Databricks can then consume via external tables.
Architecture Flow

1. OIC (Oracle Integration Cloud)

Acts as the data integration layer
Extracts and prepares data from upstream systems
Pushes data files (CSV/JSON/Parquet) to AWS S3

2. AWS S3 (Storage Layer)

Serves as a staging area for data
Stores files uploaded by OIC
Organizes data into structured folders (e.g., by date/source)

3. Databricks (Processing Layer)

Uses External Tables to read data directly from S3
Processes data using Spark SQL / PySpark
Performs transformations and analytics

Why External Tables?
Since Databricks cannot directly expose tables like traditional RDS systems:

✅ External tables allow querying data stored in S3
✅ No need to move data into Databricks-managed storage
✅ Enables scalable and cost-effective data access
Example:
CREATE TABLE customer_data USING CSV LOCATION 's3://your-bucket/path/' OPTIONS (header = "true");

Required Connection Details for OIC
To enable the integration (OIC → S3), the following details are required:
📦 AWS S3 Access Details

Bucket Name
Region
Folder Path (if applicable)
Access Key ID
Secret Access Key

🔗 Connectivity Configuration in OIC

Configure REST Adapter or FTP Adapter / Stage File Action
Use pre-signed URLs or API-based upload (if secured)
Ensure proper authentication mechanism (AWS Signature v4)

⚙️ OIC Design Reference
Step 1: Data Extraction

Fetch data from source application (DB / SaaS)

Step 2: Data Transformation

Convert to required format (CSV/JSON)

Step 3: File Creation

Use Stage File action in OIC

Step 4: Upload to S3

Use REST API or direct adapter
Push file into S3 bucket

✅ Advantages of This Approach

🔹 Decouples OIC and Databricks
🔹 Highly scalable using S3 storage
🔹 Cost-efficient (pay-as-you-go)
🔹 Supports batch and near real-time processing
🔹 Simplifies integration architecture

⚠️ Key Considerations

Ensure data format compatibility (CSV, Parquet preferred)
Maintain naming conventions in S3
Handle data partitioning for performance
Secure credentials using OCI Vault / AWS IAM policies
Monitor file upload success and retries in OIC

🎯 Conclusion
Due to the absence of native RDS-style table connectivity in Databricks, using an intermediary layer like AWS S3 is the most effective integration pattern.
The combination of:

OIC for orchestration
S3 for storage
Databricks external tables for processing

provides a robust, scalable, and cloud-native solution.

How to Accept Binary Format Files Directly in Oracle Integration Cloud (OIC) and Return JSON Response from SFTP

Naveen — Thu, 14 May 2026 05:12:36 +0000

Overview

In Oracle Integration Cloud (OIC), there are scenarios where we need to receive files directly from an external application through a REST API and then place those files into an SFTP location.

Usually, files like CSV, ZIP, PDF, or other binary content are handled as file-based data. In this blog, we will see how to configure an OIC integration to accept a binary file as input through a REST trigger, write that file to SFTP in binary mode, and finally return a successful JSON response with file details.

Use Case
The requirement is to:

Expose a REST endpoint in OIC.
Accept a file directly as a binary payload.
Write the received binary file to an SFTP directory.
Return a JSON response containing file details such as:

File name
Directory
Status
Uploaded date

This approach is useful when the source system sends the file directly in the REST request body instead of sending file content as Base64 or multipart form data.

High-Level Flow
The integration flow is as follows:

Prerequisites
Before creating the integration, make sure the following are available:

Oracle Integration Cloud instance access
REST connection with Trigger role
SFTP connection configured successfully
Target SFTP directory details
Required file naming convention
Sample JSON response structure

Step 1: Create REST Connection with Trigger Role
First, create a REST connection in OIC.
While creating the REST connection, select the role as: Trigger

This REST connection will be used to expose the integration as an API endpoint.
Once the REST connection is created and tested successfully, it can be used as the starting point of the integration.

Step 2: Create and Test SFTP Connection
Next, create an SFTP connection in OIC.
Provide the required SFTP details such as:

SFTP host
Port
Username
Password or private key
Known host key details, if applicable

After entering the required details, test the connection.
The connection should be established successfully before using it in the integration.

Step 3: Create App-Driven Orchestration Integration
Create a new integration in OIC using:App Driven Orchestration
Add the REST connection as the trigger.
Step 4: Configure REST Trigger Operation

Configure the REST trigger with a POST operation.
In the request configuration, select the request payload format as:Binary
In the REST trigger configuration, mention the content type as:Other Media Type
Then provide the media type as:application/csv
If you are handling other file types, you can change the media type accordingly.

application/pdf
application/zip
application/octet-stream
text/csv

Step 5: Configure REST Response as JSON

In the response part of the REST trigger, select the response payload format as:JSON Sample
Use the below sample JSON response:
{
"File Properties": {
"FileName": "sfghsfh",
"Directory": "djsdgj",
"status": "gjsgjds",
"UploadedDate": "25-dec-2026"
}
}
This sample will generate the response structure in the mapper.
You can later map actual values from the SFTP write response or assign custom values.

Step 6: Add SFTP Adapter in the Integration Flow
After successfully creating the REST trigger, add the SFTP adapter in the integration flow.
Select the SFTP connection and configure the operation as:Write

Step 7: Configure Target Directory and File Name

Provide the target SFTP directory where the file should be written.
You can provide the file name directly in the SFTP configuration or dynamically map it later in the mapper. Map REST Binary Payload to SFTP Write

Now open the mapper between the REST trigger and SFTP write activity.
Map the incoming binary payload from the REST trigger request to the SFTP file reference/content field.
The binary stream received from the REST POST request should be passed directly to the SFTP write operation.

Map SFTP Response to REST Response

After the SFTP write operation is completed, map the response from the SFTP activity to the REST response mapper.
Map the output fields as required.

Conclusion
Using this approach, we can directly accept binary files in Oracle Integration Cloud through a REST trigger and write them to an SFTP location without converting the file content into Base64 or any other format.
This is a simple and effective solution when an external application sends files directly through a REST API. By configuring the REST request payload as Binary and the SFTP transfer mode as Binary, OIC can handle the file smoothly and return a proper JSON response after successful upload.
This helps in building clean file-upload integrations where the source system gets an immediate confirmation response with file details after the file is written to SFTP.

Configuring a Custom “From” Email Address in Oracle Integration Cloud (OIC)

Naveen — Mon, 11 May 2026 07:40:26 +0000

By default, Oracle Integration Cloud (OIC) sends notification emails using an Oracle‑managed email address such as:
no-reply@mail.integration.eu-frankfurt-1.ocp.oraclecloud.com

While this works, it is not ideal for enterprise usage due to branding, trust, and spam‑filtering concerns. Most organizations prefer emails to be sent from their own domain email address (for example, noreply@yourcompany.com).

This blog explains how to configure a custom “From” email address in OIC so that emails are delivered successfully and behave like authenticated, non‑spam emails.

Step 1: Add a Custom Sender Email Address in OIC

Log in to Oracle Integration Cloud.
Navigate to:

Settings → Notifications

Go to the Senders tab.
Click Add Email Address.
Enter your required email address, for example: noreply@yourcompany.com

Step 2: Verify the Email Address

After adding the email address, Oracle sends a verification code to that mailbox.
Access the mailbox and retrieve the verification code.
Enter the code in OIC to complete verification. Once verified, the email address will appear as an approved sender in OIC.

Step 3: Raise an Oracle Support Ticket (Mandatory)

Even after verification, additional DNS configuration is required to prevent emails from being marked as spam.
What to do:

Raise an Oracle Support (SR) ticket with the request: “Please provide the CNAME record details required to configure a custom sender email address in OIC.” Oracle Support will provide: ✅ CNAME Key ✅ CNAME Value

Step 4: Update DNS Records (With DNS Admin)
Share the CNAME details received from Oracle Support with your DNS administrator and request them to:

Add the CNAME key and value to your domain’s DNS records This step is critical to:
Validate Oracle as an authorized sender
Improve email trust
Avoid spam filtering or message rejection

Step 5: (Optional but Recommended) Configure SPF Records

In addition to the CNAME record, it is strongly recommended to configure SPF (Sender Policy Framework).

SPF configuration helps:
✅ Improve email deliverability
✅ Prevent spoofing
✅ Reduce spam classification

Ask your DNS administrator to:

Update the SPF record in DNS Lookups Step 6: Use the Custom “From” Address in Integrations Once all configurations are complete:

Always use the verified custom email address as the FROM address in your OIC integration notifications.

This applies to:

OIC Integration Insight notifications

Oracle Integration Cloud (Gen3): File Polling Using FTP Trigger

Naveen — Sat, 02 May 2026 08:46:54 +0000

Automate file‑based integrations without custom schedulers (OIC Gen3 24.10+)

File‑based integrations are still extremely common—daily CSV extracts, XML drops from legacy systems, or batch partner feeds over FTP/SFTP.

Before Oracle Integration Cloud (OIC) Gen3 24.10,
triggering an integration when a file arrived often required:

Scheduled integrations
External scripts
Custom polling logic

Gen3 introduced a native File‑Polling trigger and it simplifies everything.

In this post, I’ll walk through how to use the new FTP File Polling trigger, when to use it, and what to watch out for.

When Should You Use File Polling?
Use this feature if:

Files are small to medium in size
You want to trigger immediately on file arrival
You don’t need manual file download logic
You prefer a low‑code, native OIC pattern

Typical use cases:

Daily CSV or XML reports
Lightweight batch integrations
Partner file drops
Staging‑based data ingestion

What’s New in OIC Gen3 24.10+
With the FTP File‑Polling trigger, you can:

Automatically trigger an integration when a file arrives
Match files using filename patterns
Load file content directly as payload
Control archive, delete, or reject behavior
Avoid additional adapters or scripts

Step‑by‑Step: Configuring File Polling in OIC

1. Verify OIC Version
Ensure your instance is:

Oracle Integration Cloud Gen3 – 24.10 or later

File polling is not available in earlier Gen3 builds.

2. Configure FTP / SFTP Connection
Create or reuse an FTP adapter connection with Trigger & Invoke role:

Host, port, credentials
Source directory permissions
Optional archive/reject directories

Test the connection before proceeding.

3.Use the File‑Polling Trigger
While creating the integration:

Select the FTP Adapter as the trigger
Choose File Polling as the trigger type

You’ll configure:

Polling frequency (e.g., every 5 minutes)
Source directory
Filename pattern (e.g., *.csv)
Schema type (CSV / XML)
Optional sample file upload for schema generation This eliminates the need for a separate file‑server read step.

4. Configure File Handling Behavior
You can define what happens after the file is read:

📦 Archive the file
🗑️ Delete after successful read
🚫 Reject invalid files
⚠️ Ignore delete errors to prevent retries

This is extremely useful for idempotency and cleanup.

5. Design the Integration Flow
After the trigger:

Parse file content using the generated schema
Route data to downstream systems
Apply validations and transformations
Handle errors using reject logic

The file content is already available as the payload—no manual streaming required.

6. Test with a Proof of Concept (POC)
Before production:

Drop a test file matching the pattern
Confirm the integration triggers immediately
Validate: *File movement (archive/delete) *Payload parsing *Error handling

7. Deploy & Monitor
Once verified:

Activate the integration
Monitor tracking for file runs
Adjust polling frequency or file rules if required