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.

Comments:

javax.mail.SendFailedException: Sending failed; nested exception is: javax.mail.MessagingException: 530 5.7.0 Must issue a STARTTLS command first. 21sm2334375pzk.0 ............ I don't send mail Please hepl me!! thanks

Posted by connan on April 24, 2010 at 12:33 PM PDT #

Hi connan, You are trying to send an email through a secured SMTP server, please have a look at note 424304.1 on our Support portal. Thanks, Julien

Posted by Julien Testut on April 30, 2010 at 08:55 AM PDT #

Excellent example, it took only minutes to evaluate in our test environment, and it frees us up from using a Domino (our legacy email service) or Exchange (current service) mailing lists.

Posted by Peter Nines on July 06, 2010 at 10:06 PM PDT #

Hi Jtestut! How to set value to #email_address variable. When i run, It cause error : Variable has no value :#email_address

Posted by guest on May 19, 2011 at 12:53 PM PDT #

Hi, If you use the query I've posted in the Command on Source tab of the procedure you should have a value in email_address, see below: - Command on Source: select EMAIL email_address from <%=odiRef.getObjectName("L","MAILING_LIST","D")%> When the select statement is executed #email_address will get a value. Thanks, Julien

Posted by Julien on May 20, 2011 at 08:25 AM PDT #

can a odi procedure call another odi procedure?

Posted by guest on January 20, 2012 at 02:40 AM PST #

Hi,

Yes, you can create a scenario for your procedure and then use OdiStartScen to start your scenario from the other procedure.
You will find OdiStartScen under Utilities in the Expression Editor if you select the ODI Tools technology.
Here is a link to the OdiStartScen documentation: http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/appendix_a.htm#BGBDEDCG

Thanks,
Julien

Posted by Julien on January 20, 2012 at 05:07 AM PST #

I am calling a pl/sql procedure with an out parameter from ODI procedure.
The pl/sql procedure is test.getvalue(o_dat out varchar2)
i am calling the above pl/sql proc like this.
step 1: declare a odi variable v_data in REFRESH mode
Step 2: odi procedure sample_odi_proc where in command_on_traget, i have the following code to make the call to pl/sql proc.
c = odiRef.getJDBCConnection("SRC")
sql=""" begin <%=odiRef.getInfo(test.getvalue(#v_data); end;"""
stmt = c.prepareStatement(sql)
stmt.execute()
stmt.close()

upon executing i am getting the following error:

PLS-00363: expression ' NULL' cannot be used as an assignment target

Any idea why or what i am doing wrong ..

Posted by guest on February 01, 2012 at 07:18 AM PST #

Hi,

Were you trying to retrieve the PL/SQL out value and store it inside the ODI variable? Unfortunately this cannot be done this way. I'd recommend to have a PL/SQL wrapper around your current call to capture the output value and store it inside a DB table. Call your PL/SQL wrapper and then refresh your variable using this table and get the output value.

Thanks,
Julien

Posted by Julien on February 06, 2012 at 10:41 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
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
23
24
25
26
27
28
29
30
   
       
Today