OIC Oracle Database polling | Oracle Integration cloud DB Polling

Here, we will see how to poll new rows from Oracle database and then write them to a file.

Highlevel steps:

1. Create a db table for polling
2. Create a Db Trigger connection
3. Creat an app driven orchestration integtation and configure db trigger 
4. Call a ftp connection adapter and write file

Steps in detail(With Screenshots):

Create a Database table and insert 2 rows.

Create Table EMPLOYEES_NEW

(

"ID" VARCHAR2(20 BYTE) ,

"NAME" VARCHAR2(200 BYTE),

"ISNEW" VARCHAR2(10 BYTE)

)

INSERT INTO EMPLOYEES_NEW(ID,NAME,ISNEW) VALUES(13,'Sanddy1','NEW')

INSERT INTO EMPLOYEES_NEW(ID,NAME,ISNEW) VALUES(14,'Dip1','NEW')

Create a database Trigger connection

Choose Oracle Database

Provide Connection Name, select role as Trigger >> Create

Provide Host, Port and Sid or service name

Select security as Username Password Token and provide user and password details. If the db is configured with Agent, then choose the respective agent group.

Create an App driven orchestration.

Adding Db Trigger and configure

Drop the Oracle DB connection as a Trigger type.

Enter a endpoint Name

Click Import Tables

Select the DB schema and enter table name and Search >> select the table and click Ok

Click on Edit button of Review the polling strategy and specify polling options.

Polling strategy: Logical delete

Logical Delete field: ISNEW

Read Value: PROCESSED(this value indicates the row has been processed)

Unread value: NEW( This value indicates the row to be processed)

Polling Frequency(Sec): 10(after which time of secconds, the rows wil be polled)

Click Next

Done

Adding a FTP adapter to write file.

Drop a FTP conntection

Privide Endpoint name

Select Operation as Write file

Select a Transfer mode as ASCII

Output directory, File Name and select Append to Existing file option.

Privide a CSV file sample.

Map the polling data to write file.

Save and Activate

Before activation:

After Activation

File content:

Note: suppose at a time there are multiple new rows in the db table, then for each row, separate transaction will be created and that we can observe in the monitoring page 

ERP - BI report using Excel template

Here, we will create a BI report based on a Excel template.

Highlevel steps:

• Create a Data model based on SQL query.
• Export the sample xml data from the created Data Model.
• Create a .xls template and upload.
• For element, use : XDO_?elementname?
• For group, use: XDO_GROUP_?elementname?
• Run the report to generate the output.

SQL query used:

Select hp.party_name supplier_name,

poz.segment1 supplier_number,

poz.vendor_id supplier_id,

hp.email_address,

hp.country,

hp.city,

hp.status

from POZ_SUPPLIERS poz, HZ_PARTIES hp where 1=1 

And poz.party_id = hp.party_id

And rownum<=5

Steps in detail:

Step1: Create a Data model:

Select SQL Query

Provide SQL Query

From Data tab, view the data, export and save as sample.

Save the Data model.

Step2: Create a .xls file

Based on the exported xml file, choose the field names and mention them as below with the element syntax:

XDO_?ElementName?

Select one element and go to Formulas >>Name Manager

Click New

Select Scope as Sheet1 >> ok

Similary do the same steps for all the element or column name.

Now select all the element and Formulas >> Name Manager 

Click New

Add the Group name as below syntax

XDO_GROUP_?groupname? 

Select scope as Sheet 1.

Now in sheet2, rename it as XDO_METADATA and copy the following in the sheet.

Create the header section by entering the following variable names in column A, one per row, starting with row 1:

• Version

• ARU-dbdrv

• Extractor Version

• Template Code

• Template Type

• Preprocess XSLT File

• Last Modified Date

• Last Modified By

• Skip a row and enter “Data Constraints” in column A of row 10.

• In the header region, for the variable “Template Type” enter the value: TYPE_EXCEL_TEMPLATE

Hide the XDO_METADATA sheet.

Save it with .xls extension

Step3: Upload the .xls template and run the report to get the output.

Note: if you have multiple groups in the data model, like G_2 under G_1, then we have to perform the following:

For example group 1 have PO headers and group 1 also have Group 2 which contains PO lines,

• For group1  elements use: XDO_?ElementName?
• For group2 elements use: XDO_?ElementNane?
• Select all the headers and elements and create group element with XDO_GROUP_?group1Name?

Note:

We can also write the element name in the name box and enter.