The blog for Oracle Data Integrator ( ODI )

August 30, 2009
by kdevendr

Categories: ODI

Tags: ,

Single Post View


First Of all I would like to thank Cezar Santos for his effort in bringing the best of ODI to the world .

Confirming to trend of this site I shall explain the steps to ODI installation for window and Linux.

The latest version can be downloaded from

All the related technical documentation for ODI can be downloaded or accessed from here

After downloading unzip the files at a particular location .

  • For windows , go to Setup->Windows click on the ‘ setup.bat
  • For Linux ,go to Setup -> Linux and run the script ‘ setup.sh ‘ .

Before running the installation make sure you have the required permission to the files inside the folder else you would encounter error related to permission.

To ignore System Prerequisite in Linux , run using

sh setup.sh -ignoreprereqs

1. Automatic Installation

Please find the screenshots of ODI Installation.

Select the products as desired.

For the time being I am installing only the Oracle Data Integrator . I would be covering the Data Profiling and Data Quality at the later posts.

For both complete and client type Installation , all the products will be installed,

whereas for Server Installation type only three products namely

  • Java Runtime Environment
  • Oracle Data integrator
  • Oracle Data integrator Agent

Specify the Name and the Path of where ODI will be Installed .

Click next and we are done with installation.


In Windows , the path is Programs->Oracle ->Oracle Data Integrator , as shown below .


For Linux , use the following path ,

  • Export ODI_HOME=$Home/OraHome_1/oracledi
  • Export ODI_JAVA_HOME=$Home/ OraHome_1/jre/1.4.2
  • CD /OraHome_1/oracledi/bin

run the required tool

[ Note: Please change the above environment parameters accordingly. ]

2. Manual Installation

In case you encounter error or unable to complete the installation, the other easiest solution is to copy the ‘ oracledi ‘ folder from the downloaded unzipped file and give the necessary permission in Linux.
The only thing you would need is to install JDK separately.

For Windows go the ‘ Oracledibin ‘ Folder and access the required tools and go the Windows Environment Variable and add ‘ODI_JAVA_HOME ‘ with your jdk folder path.

For Unix, enter the Environment Variables ,

  • Export ODI_HOME=$PATH/CD_10.
  • CD /OraHome_1/oracledi/bin

[ Note: Please change the above environment parameters accordingly. ]

Here we are with ODI Installed. Look for my next post about setting up Master and work Repository.

Have fun. Keep in touch for more exciting updates on ODI at Odiexperts.com

August 30, 2009
by Cezar Santos

Categories: Architecture , ODI

Tags: , , , , ,

Single Post View

Repository Architecture – Just one Master – Part 2

Hi Everyone,

 Let me continue to discuss about repository architecture…

In the previous post (Repository Architecture – Just one Master – Part 1) we saw a proposal of architecture with one Master repository (MR) and just one Work Repository (WR).

That first discussion was more about how important is to have the best backup policy possible and now I will present architecture for environment with one MR.  We will discuss more than one MR in a future post.

Let me show the suggested design for this:

A complete design for an ODI Environment

A complete design for an ODI Environment

 As you can see, there are 3 environments

  1. Development — GREEN
  2. Test                   — YELLOW  
  3. Production    — RED   

But be careful:


 It happens except for ODI Applications (Agents) that will be installed at each environment.

 In this way we have: 

  • From Development:
    1. The developers access the ODI Development Work Repository (DWR) to storage developed code;
    2. The ODI environment is accessed thru Designer and Operator Module or Metadata Navigator;
    3. The code execution (development) is done at Development System/Hardware
    4. There is an agent that can be used to any development and can be restated or has its configuration changed at any time with no interference at Production process. 
  • From Test:
    1. The Business Analysts (“testers”) can access only an ODI Execution Work Repository (EWR) that contains only executable code. The Designer module can’t connect to EWR;
    2. The ODI environment is accessed only thru Operator Module or Metadata Navigator;
    3. The code execution (tests) is done at Test System/Hardware;
    4. There is an agent that can be used to any test and should be a mirror from production to allow a best test. 
  • From Production:
    1. The Final User (or scheduled process) can access only an ODI Execution Work Repository (EWR) that contains only executable code. The Designer module can’t connect to EWR, then, never a code will be altered at Production;
    2. The ODI environment is accessed only thru Operator Module or Metadata Navigator;
    3. The code execution (production) is done at production System/Hardware
    4. The ODI Application (or agent) will execute all production process, scheduled or on-line.
    5. All ODI Repositories are in this hardware (even at distinct database if you thing necessary). The backup policy must be the same as any production system. The risk of loose information is the same of any other company system.


Well my friends, that is the architecture as I suggest to be used.

I will publish a specific post about how configure users to connect at database and his rights (ORACLE case) for this architecture.

Please, any question just let a comment.


Cezar  Santos

August 27, 2009
by Cezar Santos

Categories: ODI , Tips and Tricks

Tags: , , , , , , , ,

Single Post View

How to refresh ODI variables from file – Part 2 – Getting all lines, once at time

Hi everyone,

After what was showed in the “Part 1” now I will show how to loop thru a text file one line at once until the last.

At true, it is a simple trick, let start….

  • Follow all information from “Part 1”
  • Create a new variable (I named it as vLineNumber) with the following code at refresh tab (Oracle code):

select #vLineNumber + 1 from dual

Choose any Oracle schema for execute the query. It will look like:

vLineNumber ODI variable

vLineNumber ODI variable

  • Go back to the variable created at “Part 1” and change the attribute SNP$CRFILE_FIRST_ROW to:


The code will looks like:

new vReadFile code. Remember to let a single space after the variable nome

new vReadFile code. Remember to let a single space after the variable name









  • Now is just create a package and put the variables together like
    1. Drag and drop the vLineNumber in set mode setting the value to 0 (zero) – First Step
    2. Drag and drop the variable vLineNumber once again in refreshing mode
    3. Drag and drop vReadFile in refreshing mode
    4. Drag and drop any ODI object that uses the variable like Procedure, Interface, etc.
    5. The loop will end with an error when the file reachs the end, then put a KO line from the vReadFile step like:
That is the package to read a file line by line and uses it in any code

That is the package to read a file line by line and uses it in any code

Well, it’s done. Now is just use the technique!
Any comment, just ask me!
Cezar Santos

August 20, 2009
by Cezar Santos

Categories: ODI , Tips and Tricks

Tags: , , , , ,

Single Post View

How to refresh ODI variables from file – Part 1 – Just one value

Dear all,

Get value or values from file is a common question in ODI then, in the following lines and examples, I will try to show how simple is to achieve this!

First of all, what is the problem description?

1) A flat file with just one value that need to be upload into a ODI variable

2) The flat file can or can’t have Header name (I used the case where there is a column header).


File: my_test_file.txt




OK, now to explain how this work in ODI I will use a small trick:

1) Create a datastore to the file with the following characterists:

  • Read to the file  (c:temp my_test_file.txt in my case)
  •  one column (C1 in my case) and datatype String. I know that the file has a number content but let the column as String is intentional.
  • 1 line as header

2) Import the LKM File to SQL

3) Create an interface that maps the datastore, as source, to any  target, doesn’t matter if the interface works or not, it just need to be started in the Operator. At true, we’re “hunting” some code generation… Make sure that the imported LKM (step 2) was used in the interface

4) Go to operator, open the interface execution, go to “Load Data” step, Description tab and copy all content of the superior box. You should get something like:  

Copy the selected texto to your variable

Copy the selected texto to your variable

All of this was done to get that generated code, the file JDBC. It looks a little confuse but I will explain each one of its parameters in a new post.

 5) Now all that is necessary is copy the code into a refresh variable and “Voila”! Now you can refresh your variable from a file! Just drag the variable into a package…

This is how the variable should looks like:

Now just use the generated code at your variable!

Now just use the generated code at your variable!

Best Regards to all!

Cezar Santos

August 16, 2009
by Cezar Santos

Categories: Architecture , ODI

Tags: , , , , ,

Single Post View

Repository Architecture – Just one Master – Part 1

Hi Everyone!

I believe this is a really good subject to start to talk about Oracle Data Integrator (ODI), once it is, necessarily, the first step in an ODI environment.

My first point could be a little confuse to be understood at first because of the common way in programming language and “Development” concept itself but, once understood, it will becomes very clear how ODI environment must be treated.

I hope you get this understanding when all parts of this post finish to be published.

To start, a brief discussion about what are the ODI repositories for:

Definition of the ODI as Software, how it works: –

  • A client-server application that uses a centralized database to store any imputed information from this client side.
  • In this centralized database, ODI uses 3 set of tables (data model) called “Repository”
    1. Master Repository – Used to stores information about:
      • ODI User access rights (Security module)
      • Connection information like IP, database user, file directory, etc (Topology module)
      • Code Version information
    2. Developing Work Repository – Stores developing data information like:
      • Projects,  Packages, KM’s, Variables, Scenarios, etc (Designer module)
      • Executions from any object from Designer (Operator module)
    3. Execution Work Repository – Stores only code ready for execution called Scenarios. They are like a “compiled” program:
      • Executions from any imported Scenario (Operator module)

Obs: Is knew that ODI has features to work through browser interface but they aren’t still not so advanced to achieve all development needs like Designer does (ref. version

Let me create an example to make myself clear:

  • A company will start a data integration from ground and ODI is the toll for that
  • As the project is “from ground” there is no production environment yet and, in cause of that, all ODI requirements for installation were supplied by a development environment with a good hardware.
  • This hardware has the Database where the repositories (Master and developing work) and an ODI agent to test the developed scenarios are installed. I will call this development server as DEV_ENV.
  • There are 5 professionals working on this ODI installation, each one with his own workstation accessing the DEV_ENV like:
Initial Development Environment

Initial Development Environment

Three months after the project start, just 1 week before start the tests from the Business Users (or End users, as you wish) a CRASH of any kind of “no recovering” situation happens to this server… And now? What do to????

 Possible Cases:

Case 1 – The bad one!!!

Situation:  As a Development server, there is no backup policy. The nightmare!

Solution: There is no solution. All work need to be done once again, I mean, the 3 worked months.. I wouldn’t like to be you when you go to your manager office to tell him….


Case 2 – No so bad, but a lot of work..

Situation:  As a Development server, there is no backup policy but as you knew that, you made some ODI exports from Designer as backup. (Those who have this kind of environmet but never did an export stop to read the post and go to make it now!)

Solution: Create empty repositories and try to import the “backup” into them. I already saw some bugs in this process, but they are less at each new version. I still do not trust in that feature, but could work and will bring back your development to the last “export”.

I do not recommend or trust in this procedure once it is dependent of human action, I mean, it is completely manual and a human can “forget” to do it.


Case 3 – Less problems.

Situation:  It is a Development server but there is some backup police

 Solution: You have your development environment as old as is the backup frequency. In my experience, it is really rare to have a company with daily backup police on development servers….



Well my friends, I hope that you understood the problems and risks that this kind of architecture can bring to an ODI project.

In the Repository Architecture – Just one Master – Part 2 I will suggest an architecture that I consider ideal when is possible to have just one Master Repository and too, why and how to use the 2 types of Work Repository (Developing Work Repository and Execution Work Repository).

Thanks you for visit us!

Cezar Santos

August 16, 2009
by Cezar Santos

Categories: Drivers , Knowledge Modules , ODI , ODI Bug , Technology , Tips and Tricks

Tags: , , ,

Single Post View

Welcome to ODI Experts, this is Cezar Santos.

Hi everyone!

I’m really glad to start this site to share knowledge and exchange experiences with everyone that wants! That is an old project that only comes true because I found a really good friend and a incredible professional guy called Kshitiz Devendra who has been working hard to bring all of this to “production”, not more just a “project”.

Thanks Dev, you’re the best.

A little about me: My name is Cezar Santos and I have been working with ODI/Sunopsis since 2003, more than 6 years today. Besides that, I have always worked in the Data Integration world… At true, I started in the IT by “accident”. An accident called GAMES.

Let me try to explain, I started to “work” with computers when I was 12 or 13 years old because of games…. at that time we didn’t have Play Station, X-Box or Wii we had the TK85!!!

Take a look in its picture:

That was the "game console" in 80's

That was the “game console” in 80’s

Seems crazy but we recorded in K7 tape and “load” the program every time that you wanted to executed it. Some time it took more than 30 minutes to 1 hour and, if the k7 tape was old, some noise could make all loading be loose. Too, after typing a game program (bought in specialized magazines from that time), the K7 tape was our “CD” or HD! Besides this, there were programs from radio stations to “download” games… it was like:

“In 10 seconds the computer program transmission will start. 10, 9, 8, 7, 6, 5, 4, 3, 2, 1. GO!   shhhhhiiiiii uhhhhhhh grrrrrrrrrr ziiii xxxxxxxxiiiiiii…

Just like an old modem by half to an hour some times…. all of this to play a game with 3 or 4 colours!!!!

Let me stop to talk about it! There is a lot more to tell but this post isn’t about this! hehehehehehe…..

After got contact with the “IT” world in my childhood I studied everything that I could find about computers and here I’m today, still absolutely in love for this incredible world. I have been working for companies like IBM, Brazilian Army, Banks, Brazilian Mail Company, Health companies, TELECOM, CRM, etc in a few countries like: Brazil, USA, Spain, Portugal, Argentina, Peru, Ireland, Chile (and few more)  at all kind of projects and with a lot of technologies.

This is just our first post with a little about me. From now forwarding we are going to start a lot of discussions about how ODI can be used at its maximum power.

Everyone is welcome!

Cezar Santos