Overview
As part of routine maintenance, Oracle Integration Cloud (OIC) embedded SFTP servers can accumulate a large number of archived files over time. These files consume storage and make file management difficult.
To address this, we developed a common utility integration that automatically removes archive files older than 30 days from the OIC Embedded SFTP server.
Business Requirement
Periodically clean up old archive files from the OIC Embedded SFTP server.
Retain only recent files for operational and audit purposes.
Automatically delete files older than 30 days.
Reduce manual maintenance effort.
Integration Flow
Scheduler Integration
The utility integration is triggered through an OIC Scheduler at a predefined interval (daily or weekly).
Scheduler
↓
Main Integration
↓
List Files from Embedded SFTP Root Directory
↓
Filter Files Older Than 30 Days
↓
For-Each File
↓
Delete File
↓
Log Processing Details
Implementation Details
Step 1: Scheduler Trigger
A scheduled integration initiates the cleanup process automatically based on the configured frequency.
Payload to send :
{
"ProcessRequest": {
"SystemName": "1234",
"DateTime": "INTXXX",
"ParentProcessId": "INTXXX",
"ArchiveRootFolder": "INTXXX",
"Attribute1": "PlaceHolder",
"Attribute2": "PlaceHolder"
}
}
Step 2: List Files
The integration connects to the OIC Embedded SFTP server and retrieves the list of files available under the root archive directory.
Step 3: Filter Files
For each file returned from the SFTP server:
Read the file's last modified date.
Compare it with the current date.
Calculate the age of the file.
Select files older than 30 days.
<xsl:if test="ns22:lastModifiedTime < number(concat(floor(((xsd:dateTime(fn:current-dateTime()) - xsd:dayTimeDuration(concat('P',dvm:lookupValue('UTILITIES_CONFIG','Key',concat(substring-after(ns22:directory,$StringToGetInterfaceId_Var),'_RETENTION_PERIOD'),'Value',$DefaultRetentionPeriod_Var),'D'))) - xsd:dateTime('1970-01-01T00:00:00')) div xsd:dayTimeDuration('PT1S')),'000'))">
Step 4: Delete Files
A For-Each loop processes the filtered file list.
For every eligible file:
Verify the file exists.
Invoke the Delete File operation.
Record the deletion status for monitoring and auditing.
Step 5: Logging
The integration logs:
Total files scanned.
Total files identified for cleanup.
Successfully deleted files.
Failed deletion attempts, if any.
Benefits
Automated archive maintenance.
Reduced storage consumption.
Improved SFTP file management.
Eliminates manual cleanup activities.
Reusable utility service across multiple OIC projects.
#######Logic discussion#######
Assume:
Current Date = 05-Jun-2026
Retention Period = 30 days (from DVM)
File LastModifiedTime = 01-May-2026
Step 1: Get Retention Period
XML dvm:lookupValue(...)
Returns: 30
Step 2: Create Duration
XML concat('P',30,'D')
Result: P30D
(XML duration format = 30 Days)
Step 3: Subtract Retention Days from Current Date
XML current-dateTime() - xsd:dayTimeDuration('P30D')
Result: 05-Jun-2026 - 30 days = 06-May-2026
Step 4: Convert Cutoff Date to Epoch Seconds
XML (06-May-2026 - 01-Jan-1970) div PT1S
Result (example): 1778025600 seconds since 1970
Step 5: Convert to Epoch Milliseconds
XML concat(1778025600,'000')
Result: 1778025600000
Step 6: Compare File Timestamp
XML ns22:lastModifiedTime < 1778025600000
Suppose file timestamp is:
01-May-2026 = 1777593600000
Then: 1777593600000 < 1778025600000
Result: TRUE
Final Decision
✅ File is older than 30 days → Include in delete list.
In simple terms: If File Last Modified Date < (Today - 30 Days) Then Delete File
Conclusion
This common utility service provides a simple and effective solution for maintaining the OIC Embedded SFTP server by automatically deleting archive files older than 30 days. By leveraging OIC Scheduler, SFTP Adapter, and For-Each processing, organizations can keep their integration environment clean, efficient, and easier to manage.
No comments:
Post a Comment