Friday Jun 12, 2009

Using an ODI Procedure to Loop Through a Command

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Procedure, Command and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

In this article we will focus on using the Command on Target and Command on Source tab of a Command in an ODI procedure.

It is not uncommon to have to execute a specific command for each value returned by a select statement. For example you might be willing to send an email as part of your integration process to a specific list of users maintained in a database table.
You might also receive several zip files that have to be extracted before being processed by ODI. An ODI procedure can help you loop through a list of files and start the extraction process.

If we want to implement the email example we will need to:
- Reverse-Engineer the database table in ODI.
- Create an ODI procedure.
- Add a Command.
- Use the Command on Source tab to execute a select statement on a table to retrieve all the email addresses.
- Use the Command on Target tab to execute an OdiSendMail process for each email address returned by the select statement.

Step 1. Create the ODI Procedure

Expand your ODI project then expand a folder and right-click on the Procedures node. Select Insert Procedure in the menu.

Procedure_InsertProcedure.jpg

You can pick any name for the procedure, we will use Send Email to Mailing List Users in this example.
You don't need to modify the other parameters.

Procedure_Definition.jpg

Now that the procedure is created, we will add a command.

Step 2. Add a Command

Go to the Details tab and click on the grid button to create a new Command in the procedure.

Procedure_NewCommandButton.jpg

A new window will appear you can specify any name for this Command. We will use Email Step in this example.

Step 3. Define a Command on Source

In the Command on Source we want to execute a select statement on a database table to retrieve a list of email addresses.

In this example the email addresses are stored in the MAILING_LIST table in an Oracle schema called STAGING.

The table can be created easily using the following code:
CREATE TABLE MAILING_LIST
(
USER_NAME VARCHAR2(255),
EMAIL VARCHAR2(255)
)

To define the Command on Source implementation, click on the Command on Source tab.

Procedure_CommandonSourceButton.jpg

Set the technology to Oracle.
Set the Schema to the logical schema that hosts your MAILING_LIST table.
You don't need to modify the other parameters.
The select statement we want to use is the following:
select EMAIL email_address from STAGING.MAILING_LIST

EMAIL is the column storing the email addresses and email_address is the alias we will use in the Command on Target to refer to them.

As we are following the ODI Best Practices we do not want to hard-code the schema name STAGING in our query. To avoid this we will use the getObjectName substitution method and let ODI complete the table name with the schema name at runtime:
select EMAIL email_address from <%=odiRef.getObjectName("L","MAILING_LIST","D")%>

Refer to the ODI documentation for additional information regarding the substitution methods.

You should now have the following:

Procedure_CommandonSource.jpg

We are done with the Command on Source, we will now define the Command on Target.

Step 4. Define a Command on Target

In the Command on Target tab we will use the OdiSendMail tool to send an email to the email addresses retrieved from the command in the Command on Source tab.

To define the Command on Target implementation, click on the Command on Target tab.

Procedure_CommandonTargetButton.jpg

Set the technology to Sunopsis API.
You don't need to modify the other parameters.

We will use the following command:
OdiSendMail -MAILHOST= -FROM= "-TO=#email_address" "-SUBJECT=log and bad files" -ATTACH=c:/temp/log.txt
Please find attached the log file...

We are referring to the alias email_address defined in the Command on Source tab prefixed by #: #email_address. This gives us access to the email addresses retrieved by the select statement in the Command on Source.

You will have to modify the MAILHOST and FROM parameters according to your SMTP server settings otherwise you will not be able to send any emails.
The SUBJECT and ATTACH parameter can be modified as well as the text.

Refer to the ODI documentation for additional information regarding OdiSendMail and the other ODI tools.

Note: You can use any other ODI tools or technologies in the Command on Source and the Command on Target.

Procedure_CommandonTarget.jpg

The procedure is now complete we can click on Execute to start it and follow its execution in Operator.

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Monday Apr 20, 2009

Using Variables in ODI: Creating a Loop in a Package

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In our last post in this series, we looked into passing parameters to dynamically set the name of the file that we want to process. An alternative may be to parse a list of file names that would be stored in a table in your database and to loop over this list to process all files.

To perform this, we will need to perform the following operations:
- Create a variable to store the file name, and a second variable that we will use as a cursor to point in our table
- Define a package and use the variables to loop over the different values in the table

If you need help with the creation of variables please refer to our post on the usage of variables in ODI Interfaces and Packages.

1. DEFINITION OF THE VARIABLES

1.1 The Counter Variable

We will start here with the definition of a variable that we will use to loop through your list of files. Let’s call it Counter. I usually use an alphanumeric variable and define the SQL query as being

select #Counter+1 from dual

(Note: the code would vary for non Oracle databases). I find alphanumeric variables easier to process than using numerics as numerics come with decimal values and may require conversions based on where and how you use them. One advantage of numeric variables though is that you can use ODI to increment their values when you select the Assign action on the variable.

Create Counter Variable Counter Variable Query


1.2 The FileName Variable

For this example, we assume that the file names are stored into a table called ODI_FILES_TABLE. This table has only one column with the file names, called FILE_NAME. And we store the table in an Oracle database. You can easily adapt the code for different table structures or databases. Here we will parse the table taking advantage of the ROWNUM returned by the Oracle database.

Create a Variable in the same project, and call it FileName. Define the following query for the FileName variable:


select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from ODI_FILES_TABLE)
where COUNTER=#Counter

Note that we are using our fist variable to retrieve one record only. To run this statement, remember that the Counter variable MUST have a value – otherwise the where clause would not be valid.

Create FileName Variable FileName Variable Query


2. DEFINITION OF THE PACKAGE AND DESIGN OF THE LOOP

We will now create a package to design our loop. In the package, drag and drop the Counter variable and set the action to Set Variable. Assign the value 1 to the variable (select Assign in the properties window and enter the value 1 in the text box below the Assign selection).

Drag and drop the FileName variable in the package after the Counter variable. Set the action on the variable to Refresh Variable. In case of success after the refresh, execute any procedures or interfaces where the FileName is used. In case of failure of the refresh, execute a last step to indicate you are done: a failure here would indicate that you have exhausted all files listed in the table. In our example we are sending an email, but you could execute another branch of your process.

After the execution of the procedures and interfaces, add the Counter variable again, but this time set the action to Refresh Variable: this will increment the value of the variable. (If you are using a numeric variable, you can replace this with a Set Variable action and set the operation to Increment).

Then loop back to the refresh step of the FileName variable: this will either select the next file in the list… or fail and exit the loop.

Loop Package


3. BEYOND THE FIRST EXAMPLE

3.1 A Cleaner Exit

For a cleaner exit than the one described here, you may want to use a third variable where keep track of the number of files in your table with the following query:

select count(*) from ODI_FILES_TABLE

Refresh this new variable at the beginning of the package. You can then compare your Counter variable (using the action Evaluate Variable) to that value and exit the loop when you reach this value.

3.2 Guarantying that the tables are found in all Contexts

Instead of hard-coding schema names in your variable refresh queries, remember to use the ODI Substitution Method that will automatically rebuild the schema name in all contexts: odiRef.getObjectName. For instance:

select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from < % = odiRef.getObjectName("ODI_FILES_TABLE") % >)
where COUNTER=#Counter

MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

 


About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today