Waiting for specific data to proceed with action using open tools in ODI 11g

Today I will be demonstrating the basic usage of a very cool feature which ODI 11g offers through ODI Open Tools palette in a package designer.

This cool tool includes:

• OdiWaitForData

Imagine you are asked to automate an email and notify some 3rd party that specific data has been populated in a specific table. Furthermore multiple tables, furthermore multiple filters.

This "automatic querying" can be easily achieved by using OdiWaitForData. As the name pretty much describes it, it will poll the table for specific data in specific amount of time and specific intervals, all which can be given through parameters!

For the purpose of demonstration my use case will include:

• Check a table named PHONE_CONTACT
• Check if the column STATE has been populated with a value like '%KOS%'
• Poll this table every 30 seconds
• If the condition is matched, send an email.
• If the condition is not matched, run indefinitely until the condition is matched.

Go to the desired project, under the package section create a new package. Proceed to diagram tab inside the package select 2 of elements which I will be using for today's use case.

WaitForData

The oracle table which I will poll is named PHONE_CONTACT, and I will check for a record consisting the string '%KOS%' in the column STATE of this table.

PHONE_CONTACT Oracle table used for polling

Let's dig in into OdiWaitForData parameters, click on the open tool element and start filling the parameters:

OdiWaitForData parameters

- Context - provide the context (I hid it due to privacy reasons)

- Global Row Count - The amount of rows to meet the criteria of consisting STATE like '%KOS%'

- Polling Interval (milliseconds) - check the table every 30 seconds

- SQL Filter - STATE LIKE '%KOS%'

- Table Name - PHONE_CONTACT

- Timeout - if 0, the polling will continue indefinitely until the criteria is matched

**If multiple table's need to be checked at once, or say for example the row count has to be compared with a specific variable, check this documentation for a detailed usage of ODI's open tools:

https://docs.oracle.com/middleware/11119/odi/develop/appendix_a.htm#CEGEDJEB 

Now let's fill in OdiSendMail's parameters:

Now let's execute the package and see how it's working:

Inserting this row, the package should not stop and continue to poll the table because the state does not meet the criteria.

30 Seconds have passed and the package is still running, so it should be working good:

Now let's insert a row which matches the criteria to proceed with email:

Inserting this record and waiting for 30 seconds, an email should arrive, notifying the destination that the table has met the conditions needed!