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.


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.


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.


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:

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


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:


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.


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.


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 of ODI. Actual icons and graphical representations may vary with other versions of ODI.


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 #


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:


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)

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 #


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.


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

Good work

Posted by guest on December 22, 2015 at 11:29 AM PST #

Hi Julian, when I use to create temp table inside procedure with Microsoft SQL Server with same transaction (Transaction 1), ODI still creates different session. So even if there are multiple steps with same transaction level is set, they wont see the tables created in previous step.

To overcome this I have to create #temp, then do all DML operation in the same step.

Can you please suggest on this?

Posted by Bhabani on May 24, 2016 at 10:04 AM PDT #

Hi Bhabani,

This doesn't sound right, have you already opened up an SR about this? Also this support note might be useful: How Does ODI Sessions Manage Database Connections At Execution Time? (Doc ID 424274.1)


Posted by Julien on May 24, 2016 at 05:28 PM PDT #


I wanted to do a stored procedure that calculates some things and then upload it.
In the following example I display one simple situation to ilustrate this.


J$prueba as (select sysdate from dual)
J$_prueba1 as (select * from J$prueba)
select * from J$_prueba1

Task2 --->

insert into odi_demo.prueba_fecha
select * from J$_prueba1;


TASK2 doesn't know about J$_prueba1.

How could i do??

Thank you!

Posted by aisasid on June 01, 2016 at 08:20 AM PDT #

Hi aisasid,

You would need to be in the same task unless you actually create J$_prueba1 at the DB level. Use the Command on Source and Command on Target tabs as in the example above to do the select on one side and then the insert in the other command using bound variables.

If you want to persist a value from one Task to another you would need to persist them at the DB level.


Posted by Julien on June 01, 2016 at 03:22 PM PDT #

I've done what you say:
In the same task,
Source command:

J$_prueba as (select sysdate as Fecha from dual)
J$_prueba1 as (select * from J$_prueba)
select * from J$_prueba1

Target command:

insert into odi_demo.prueba_fecha
(select * from J$_prueba1)

It doesn' run ok,

ODI-1228: Task Procedure-Prueba_procedure_2-Transormaciones fails on the target connection SRC_ODIDEMO.
Caused By: java.sql.BatchUpdateException: error occurred during batching: ORA-00942: table or view does not exist

It doesn't know about J$_prueba1

Posted by guest on June 02, 2016 at 02:33 AM PDT #

J$_prueba1 has to exist at the DB level otherwise none of the Tasks will be able to query it. Depending on what you're trying to do it might be best to post this on the ODI forum on OTN:


Posted by Julien on June 02, 2016 at 05:03 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

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


« September 2016