Read ZIP files in Oracle Integration Cloud

We all know the capabilities of Oracle Integration Cloud FTP adapter of reading and writing files. There are situations where the files are in the zip format and required to read the zip files, parse each and every file under the zip file and send the data into the downstream applications.

In the article, we will explain how to read ZIP file from FTP location and push the content of each file under the zip file into the Oracle database.

Download the zip file from here. This zip file contains three CSV file in the below format

FirstName,LastName,Designation,Qualification

Ankur,Jain,Tech Lead,MCA

Dev,Ghelot,Sr. Manager,Btech

Priya,Kapoor,SVP,MBA

Below are some pre-requisites which are required to execute this use case:

Oracle Integration Cloud instance subscription. If you don’t have please subscribe for the free trial from here
FTP which should be accessible publicly
Oracle Database
OIC agent should be up and running
Download the SQL script from here. Run these script which will create a table USER_MASTER table and a package UserPKG along with one procedure AddUser

Let’s start and achieve the use case

Login into the OIC instance and move to the Integrations
Move to the Integrations and click on the Create button
Select Scheduled Orchestration pattern
Enter Integration name as ReadZipFiles

Select FTP_Conn which will be used to read the ZIP file

Provide endpoint name as DownloadToICS

We’ll select Download File Operation which will allow unzipping the file at Integration Cloud Staging location. Enter below information and click the Next button
- Select Operation: Select Download File
- Select a Transfer Mode: ASCII
- Input Directory: Enter the directory which contains the zip file at FTP location
- FileName: ZIP file name. In our case the zip file name is User.zip
- Download Directory: Enter the directory where the file will be unzipped at Oracle Staging location: For the time being we have entered /unzippedFiles
- Select Unzip the file checkbox: This option will unzip the file

Click the Done button

After this step, the integration will look like below

Now add for each loop to loop over the unzipped files. Add For Each activity just after the FTP adapter

Enter below information and click the Create button
- Name: Any meaningful name. In this case, we have entered LoopOverThefiles
- Repeating Element: Drop the repeating element ICSFile which is under DownloadToICS object
- Current Element Name: Enter CurrentFile for instance

Since ZIP file is unzipped on the Oracle Cloud Staging location. So to read the file from staging location, add the Stage activity under For each loop

Enter Endpoint name as ReadFile

Select Read Entire File Operation

Select edit icon parallel to Specify the File Name and drop the fileName element under CurrentFile object. Save and Exit the Expression builder

Select edit icon parallel to Specify the Directory to read from and drop the directory element under CurrentFile object. Save and Exit the Expression builder

Select Create a new Schema from a CSV file and click Next button

Create a sample CSV file and paste the content as below and upload using the Choose File button. Enter User and Users in the subsequent text boxes and click the Next button

FirstName,LastName,Designation,Qualification

Ankur,Jain,Tech Lead,MCA

Dev,Ghelot,Sr. Manager,Btech

Priya,Kapoor,SVP,MBA

Click the Done button

Add one For Each loop just after the Stage activity. This loop will be used to loop over the employee’s data from CSV file

Configure the For Each with below information and click the Create button
- Name: Enter the name as LoopOverTheEmployees
- Repeating Element: Drop User element under the ReadFile object
- Current Element Name: Enter as CurrentEmployee

Add DB adapter under the nested For Each loop

Enter below information and click the Next button
- What do you want to call your endpoint: Enter the name of your choice. For instance, we have entered InsertUser
- What operation do you want to perform: Select “Invoke a Stored Procedure”

Enter below information and click the Next button
- Select Schema: Since the package which we wanted to call resides under the HR schema, so we have selected the HR schema
- Select Package: Select the USERPKG
- Select Procedure: Select the ADDUSER