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.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
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


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.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


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



This is a nice and helpful writeup. I´m trying to use this method for XML-files.
And in the physical schema for my xml-files and xsd-files I have hardcoded one of my xml-files, e.g. like this:


Which obviously is fine when I only need to insert one file.
What do I need to do in order to get the interface to use the filename in the #FileName variable in stead of the hardcoded name from the physical schema/jdbc-URL?
I have inserted 10 rows in the ODI_FILES_TABLE with names for 10 different xml-files, and my package is looping 10 times inserting the same hard coded file 10 times.
I tried to exchange the xml-filename in the jdbc-URL with #FileName, but that did not work.
I´m using ODI ver


Posted by guest on January 11, 2012 at 11:02 PM PST #


You can indeed use variables in Topology... but there is a catch. The variable value must be set before your package even starts. So you will need 2 packages: a first one that does all the necessary processing for the XML file. And a second one that retrieves the proper entry from your table, invokes the first package (actually the scenario for that package) and passes that value as a parameter to this scenario. Remember to declare the variable at the beginning of the first package so that the parameter is properly received.

A more detailed explanation of the mechanics involved is available in these entries:

Posted by Christophe on January 12, 2012 at 12:28 AM PST #

Thanks Christophe!
I have tried to follow your instructions but I'm still struggeling a bit here.
I've created two packages as you suggested. One with a declaration of a filename-variable and several interfaces in it, all of them has the same XML-file as source. So in the topology I have now inserted the #filename to the f-parameter (i.e. f=#project.filename&d=....xsd...). These interfaces all works fine with a fixed filename by the way.
The second package is a simple loop identical to the example in this article.
I have created a scenario for the first package which is inserted in the loop package. When I try to run the scenario I can see that the variable filename is given the correct name. But when I try to run the loop-package the process stops and complains about tables that already exist. If I replace the variable with a fixed filename the loop-package run the correct number of times and exits as expected. The DELETE_TEMPORARY_OBJECTS is set to TRUE for the target table. I have tested this with KMs IKM SQL Incremental Update and IKM SQL Control Append with the same result.

The code from the error is:

And the message starts like this:
ODI-1217: Session PKG_LOAD (29001) fails with return code 8000.
ODI-1226: Step Schemaname fails after 1 attempt(s).
ODI-1240: Flow Schemanema fails while performing a Loading operation. This flow loads target table SCHEMA_TABLE.
ODI-1227: Task SrcSet0 (Loading) fails on the source XML connection XML File.
Caused By: java.sql.SQLException: object name already exists: DELIVERED_AMOUNT in statement [create table....

Is there anything here you can see (or think of) that could cause this to happen?


Posted by guest on January 16, 2012 at 10:12 PM PST #

I think that what is happening here is that all XML files that you are trying to load use the same in-memory schema to store the data when you are using the variable approach. and when the second file tries to create object in that schema it fails because some of the objects are already there.
I haven't had a chance to try this, but I am thinking of 2 approaches here:
- Truncate schema in between interfaces: You want to make sure that the schemas are empty before you attempt to load the next file. The XML driver can receive commands to define what to do with the different schemas, in particular you can issue a "TRUNCATE SCHEMA" command to make sure that each interface works in a clean schema. You can issue this command in a KM or in an ODI procedure: make sure that you set the technology to XML and that you point to your logical schema. The complete documentation for the commands you can issue to the XML driver is available here:
- use a variable for the schema name: You would be using more memory, but if you can link the schema name to the actual file name, then each file would have its own in-memory schema - hence avoiding conflicts altogether.
Hopefully one of these approaches will work for you!


Posted by Christophe Dupupet on January 20, 2012 at 07:21 AM PST #

Hi there !

Thank you Christophe for this helpful post.
I need your help to adapt it to my case :
"I have a list of tables names in a string variable, separated by ";". I want to split the string variable and for each table name I get, export the content of the table to a csv file".

As I am a beginner in ODI, I don't know how to do it without interface and moreover in a generic way.

Could you please help ? Thanks in advance

Posted by HJ on May 31, 2012 at 01:45 AM PDT #


Parsing text in the variable itself is not necessarily the most natural approach. How do you build the string in the first place? The reason I am asking this is that if you could have this list in a table or a file, then all you would have to do is loop through all entries in that file or table...

Now if you really want to use ODI variables and an ODI package, you can use a couple of additional ODI variables and a little bit of SQL to do this - in particular by using the Oracle function INSTR (or equivalent on another database if you are not using Oracle) to locate your separators.
It would be something like this:
- Create a variable sCount to know which occurence you are currently working with in your string. Set this variable to 1.
- Create two variables sPointerStart and sLength to keep track of where you are in the string and to check if there is more to process in that string
- create a variable sTable to retrieve the name of each table

The pseudo code of the loop would be as follows (MyVariable would be the variable where you have the list):
- sCount is only incremented with each loop
- sPointerStart = select INSTR('#myVariable', ";", 1, #sCount) from dual
(note the single quotes around the variable name to make sure that it is seen as a string by the database)
- if sPointerstart=0, exit the loop. If not continue.
- sLength = select INSTR('#myVariable', ";", 1, #sCount+1) - #sPointerStart from dual
(if your last entry is not followed by a semicolon, you will have to be a little bit more crafty with that one)
- sTable= select substr('#myVariable', sPointerStart+1, sLength) from dual.

I haven't tested this code so you will most likely have to adapt. But again, if you can have the data in a table or a file, things will be much simpler for you!

Hope this helps!

Posted by Christophe Dupupet on June 01, 2012 at 09:01 AM 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


« April 2014