List Files with dynamic dates in Oracle Integration Cloud

There is no way to pass Minimum Age dynamically when utilizing list files via any FTP connection. This “Minimum Age” must be provided while setting up the adapter.

I now need an integration to be developed that will manage weekly cleanup for Object Storage, ADW, and SFTP based on retention periods. This retention period should be configurable, and customers can change it during festival season. We can pass the retention period from a lookup for ADW & Object Storage API to meet our needs. But in the case of SFTP we don’t have any such direct approach. In this blog, I tried to demonstrate how we can achieve it.

If you noticed response of a List Files operations, lastModifiedTime is coming in UnixTime. Unix time is a date and time representation widely used in computing. It measures time by the number of non-leap seconds that have elapsed since 00:00:00 UTC on 1 January 1970. In our demo integration we will try to compare this date with required date.

1. First we have maintained the retention period in a lookup. And created a variable lv_retentionPeriod to fetch the retention period(eg 10, 20, 30) from lookup.
2. Now we will try to calculate the date from current date. eg. If we configure retention period as 20 and today is 30th April. Below function should return 10th April as output.

string(fn:current-dateTime()-xsd:dayTimeDuration(concat('P',$lv_retentionPeriod,'D')))

Output:  2024-04-10T01:00:00.000Z

3. Now lets try to convert current date time to Unix time stamp using below function

floor((fn:current-dateTime() - xsd:dateTime('1970-01-01T00:00:00')) div xsd:dayTimeDuration('PT1S'))

Output:  1714465326 (30th April)

4. But you might noticed lastModifiedDate in list files response have 13 characters where milliseconds denoted as 000. Hence we will concat ‘000’ along with the above function.

concat(floor((fn:current-dateTime() - xsd:dateTime('1970-01-01T00:00:00')) div xsd:dayTimeDuration('PT1S')),'000')

Output:  1714465326000 (30th April)

5. Now you have to combined step2 & step4 to get Retention period date in Unix Time. We have used below function to derive

concat(floor((xsd:dateTime(fn:current-dateTime()-xsd:dayTimeDuration(concat('P',$lv_retentionPeriod,'D'))) - xsd:dateTime('1970-01-01T00:00:00')) div xsd:dayTimeDuration('PT1S')),'000')

Output: 1712717736000 (10th April)

Now you can configure minimum age as 10sec in list files. Then all files with minimum age will be returned in list files response.

Then in the integration you need to create a loop over each file in listFiles response. And need to compare the lastModifiedDate with the file UnixdateTime we populated in Step 5. If the UnixdateTimeis larger than lastModifiedDate means, those files are older than retention period. We can take necessary actions on those files only.

The majority of the files we have on SFTP are 15 days old. So first we tried to configure 20 as retention period in the lookup. And we have used a logger to print the filename in audit trail in case lastModifiedDate is older than retention period. Now, if I attempted to execute the integration, we could see that every flow traveled to an otherwise flow in the audit trail.

Now I have changed the retention period to 10 in the lookup. As you can see, the filename was used to execute every logger.

As we’ve seen, there isn’t always a straightforward way to modify the minimum age dynamically when listing files. In practice, however, we have to use integration logics to accomplish it.

I hope this article will be useful to you if your needs are similar.