Use case: Create the integration which records the files entry in ATP Database, record errors in ATP Database, send an error email notification using lookups and moves files to archive folder using Oracle Integration Cloud Service – OIC.

 Let us start !

• Create lookups for common values

Navigation:

Integration --> Lookups --> Create 

New page will appear to create the required lookup. First of all we will create common lookups to define sender's email and receiver's email. 

Now we will create lookups other lookups which we will use in this integration

• Create Integration using Schedule Orchestration

Navigation:

 Integration --> Create 

Click Select Button and New page will appear. Fill the required details and select the package as container and press button Create. 

• Download Wallet.zip file

Navigation:

 

Oracle Database --> Autonomous Database.         

Click on ATPDB hyper link --> DB Connection --> Download wallet

• Create SQL Developer Connection

Open SQL Developer --> Click on Plus icon 

Fill the required details in connection page, browse wallet.zip, press button to test and connect the ATP database.

• Create schema in ATP Database if it does not exist.

Run below script to create an schema 

create user test identified by "Shahani@0312";

• Grant privileges to the schema

grant create session to test;

grant create table to test;

grant create sequence to test;

alter user test quota 100M on USERS;

• Create required objects

  CREATE TABLE "XXC_ERR_TBL" 

   ( "FILE_ID" NUMBER, 

"FLOW_ID" NUMBER,

"SOURCE_FILE_NAME" VARCHAR2(200 BYTE), 

"ERR_CODE" VARCHAR2(500 BYTE), 

"ERR_MSG" VARCHAR2(1000 BYTE), 

"CREATION_DATE" DATE

   ) SEGMENT CREATION DEFERRED 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

  CREATE TABLE "XXC_FILE_ENTRY_TBL" 

   ( "LOAD_FLOW_ID" NUMBER, 

"FILE_ID" NUMBER, 

"FILE_NAME" VARCHAR2(1000 BYTE),  

"CREATEION_DATE" DATE, 

PRIMARY KEY ("FILE_ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  TABLESPACE "USERS"  ENABLE

   ) SEGMENT CREATION DEFERRED 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

  

  create sequence XXC_FILE_ENTRY_seq;

  create sequence XXC_ERR_seq;

• Add Assign action for global variables

Navigation:

Action --> Data --> Assign and Drag and Drop it on the integration. 

Enter the Global Variable name and press button Create

After pressing Create button new popup will open and assign variables to the integration.

• Create Database connection

Navigation:

Integrations --> Connection --> Click button Create --> pop up will display, search for Oracle Database adapter, click on adapter and Press Button select.

Once you will press select button new pop up will display, upload wallet.zip file, enter wallet password, database service user such as ADMIN and database service password. Press button Test and if test is successful then press Button Save

• Create FTP connection

Navigation:

Integrations --> Connection --> Click button Create, popup will display, search for FTP adapter. Select FTP adapter and press button Select.

 

Once you will press select button new pop up will display, enter the required details. Press button Test and if test is successful then press Button Save.

Let us to back to our integration and complete our integration since all the required connections have been created.

• Add FTP connection for listing the files and perform mapping

In order to get list the file we need to drag and drop the FTP connection

Navigation:

Invokes --> FTP -->  FTP Connection

New popup will display and follow the steps in below listed snapshots.

Now we will map the source and target as

Navigation:

Click on --> Map to listFiles

New popup will appear

Expand --> listFiles Request (FTP)

Expand -->ListRequest --> FTPListHeader

Right click on fileNamePatter--> select menu -->Create Target Node 

 Write the Expression for fileNamePattern using lookupValue

dvm:lookupValue ("tenant/resources/dvms/FixedVal", "IntegrationCode", $varIntegrationCode, "fileNamePattern", "" ) 

Same way we will write expression for directory

dvm:lookupValue ("tenant/resources/dvms/FixedVal", "IntegrationCode", $varIntegrationCode, "inputDir", "" )            

 Press button Validate and close

• Add For-Each loop

Navigation:

 Action --> Collection --> For-Each -->Drag and Drop it on the integration.

New popup page will display fill the required info as required.

Click button Create and it will display as below

• Add ATPDB connection to get sequence value

In order to get sequence value we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

 Drag and drop the ATPDB connection, new popup will display and fill the details

Remove mapper as it is not required.

 

• Add ATPDB connection to record the file entry and perform mapping.

In order to perform insert operation we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

 

Drag and drop the ATPDB connection, new popup will display and fill the details

 Now it is time to map the source and target using mapper

Click on Mapper --> Edit  and map the fields as 

  

Source	Target
getfileldfromseq Response à nextVal	fileId
Toggle Functions àSearch à getflowid	loadflowId
CurrentFilesàFileàfilename	fileName
Toggle Functions àSearch à current-date	Creation

• Add connection to read files and perform mapping

In order to read the file/s we need to drag and drop the FTP connection

Navigation:

Invokes --> FTP -->  FTP Connection

New popup will display and follow the steps in below listed snapshots.

Now we will map the source and target as

Navigation:

Click on --> Map to readFiles

• Add connection to write files and perform mapping
  

In order to write the file/s we need to drag and drop the FTP connection

Navigation:

Invokes --> FTP -->  FTP Connection

 

New popup will display and follow the steps in below listed snapshots.

Now we will map the source and target as

Navigation:

Click on --> Map to writeFiles

• Add connection to archive/ move files and perform mapping

In order to write the file/s we need to drag and drop the FTP connection

Navigation:

Invokes --> FTP -->  FTP Connection

 New popup will display and follow the steps in below listed snapshots.

 Now we will map the source and target as

Navigation:

Click on --> Map to moveFiles

• Add Scope

Navigation:

Action --> Data --> Assign and Drag and Drop it on the integration.

Enter the Scope name and press button Create

•  Add Fault Handler

 Click on the Fault Handler Button and again click on Fault Handler. New Pop Will display

• Add ATPDB to get sequence value

In order to get sequence value we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

 Drag and drop the ATPDB connection, new popup will display and fill the details

Remove mapper as it is not required.

• Add ATPDB to record error in database and perform mapping

In order to perform insert operation we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

Drag and drop the ATPDB connection, new popup will display and fill the details

Now it is time to map the source and target using mapper

Click on Mapper --> Edit  and map the fields as 

 

Source	Target
Geterrseqval Response à nextVal	fileId
Toggle Functions àSearch à getflowid	flowId
	SourceFileName
errorScopeFaultObjectàFaultàerrorCode	errCode
errorScopeFaultObjectàFaultàreason	errMsg
Toggle Functions àSearch à current-date	Creation

• Use Notification Action to send email notification

Navigation:

 Action --> General --> Notification -->Drag and Drop it on the integration.

New popup page will display fill the required info as required.

 New popup page will display fill the required info as required.

• Add Tracking

Navigation:

Action --> Tracking 

• Save and activate your integration
• Monitor activity stream

Happy Learning!