The blog for Oracle Data Integrator ( ODI )

September 29, 2010
by kdevendr

Categories: Administration , Architecture , Drivers , How to , Logic , ODI , Tips and Tricks

Tags: , ,

Single Post View

Drivers Location in ODI 11g


A small but very helpful tip!

Initially when I was scrolling  the documentation there were two location where we would need to place the Drivers one in <WINDOWS_USER_HOME>AppDataRoamingodioraclediuserlib

and the other will be the <ODI_HOME>/agent/oracledi/drivers  Folder.

It confused me why two places and this is not feasible architecture , then I turned my attention towards the Global Architecture of  Server  and Client .  So if my ODI Server  is located on a UNIX server then I would be placing all my drivers under the agent/oracledi/drivers   folder,since Agent will be located on my Server . While my Windows Client machine  would be using the  AppDataRoamingodioraclediuserlib folder for drivers ,as I would be using the Local Agent for reversing and if I in case I need to use  respective RKM automatically Server Agent can be used to connect.

Moreover when we install the ODI Studio namely the ODI client the user wont have the agent folder and so they would have no confusion and would have to use AppDataRoamingodioraclediuserlib for drivers .


See you in the next post!

September 21, 2010
by Cezar Santos

Categories: Administration , Architecture , How to , ODI , Technology , Tips and Tricks

Tags: , , ,

Single Post View

11g Oracle Data Integrator – Standalone Agent but, and about the Windows Service???

Hi Guys,

Let me start a new discussion that, for sure, some ODI gurus will commnet! (Please, do it! hehehehehe)

In ODI 11g when I was doing the installation , I got a setup screen to setup the agent  and I created the agent called MAIN_AGENT and I initially thought it’s a cool feature and thought that the Agent was created and automatically the ODIPARAMS and other files will get update .

Once the installation is done and the ODI 11g instance is started , I just triggered the agent and I got an Error . Oops I have not started the agent_MAIN_AGENT.bat and when I started it suddenly vanished , I had a feeling that some thing was wrong  and something is not setup properly, so I went inside the agent_MAIN_AGENT.bat script and saw this

D:OracleMiddlewareOracle_ODI1oraclediagentbinagent.bat “-NAME=MAIN_AGENT” “-PORT=20910”

In short its just the file calling the agent.bat with the required parameters populated , so started digging around and looked at the odiparams .

The odiparams file was located where the standalone agent is located namely


I looked at it and saw that its pretty much the same but rather than the HSQL driver I was able to see the parameters for oracle. I started updating and provided the required parameters, at the ODI_MASTER_ENCODED_PASS I need to supply the encoded password , so I started typing in the command prompt

agent encode <password>


and ODI was trying to start the agent <password> ,so I knew some thing is changed again and I looked at the folder and I was able to see the encode.bat . Ah ! new funda . so I tried

encode <password >


Voila ! it worked Smile

After updating the required parameters , I was scrolling and looking at the other option , I saw new options  ODI_CONNECTION_RETRY_COUNT , ODI_CONNECTION_RETRY_DELAY


and also the ODI_JAVA_HOME is set with the required jdk path  which mean it no more reads from the JAVA_HOME or setting the environment variable ODI_JAVA_HOME. hmm !

Coming back to the above two option I was trying to find out what do they mean and the definition was defined as follows


I thought ok , let me play with these option some time later and let me restart the agent.

So I went to topology and created and updated the physical and logical agents as required .

Now I thought let me start the agent_MAIN_AGENT.bat and start again .


It  started . Cool ,but then I thought how about if I want to set up again another standalone agent on 20911, so I went to Topology created the Physical and Logical and called it second agent.

After doing so I made the duplicate of the agent_MAIN_AGENT.bat and called it agent_second_agent.bat  and update the required parameters

D:OracleMiddlewareOracle_ODI1oraclediagentbinagent.bat “-NAME=second_agent” “-PORT=20911”

Now I thought let me start that too and slowly the second_agent started too .


Now I wanted to make them as service so I was looking for agentservice.bat and I looked into the documentation and search for agent service but in vain .I even tried to bring in the Wrapper codes from ODI 10 g into 11g and make it as a service , i was successful in making the service but the conf files calls few java classes which could not be migrated since that would be complicated process.

Looks like we can only use the agentscheduler mode in windows but I can’t believe it… I will try to find out how to start it as service and publish as soon as I discovery!

See you soon!

September 15, 2010
by Cezar Santos

Categories: Administration , Architecture , How to , Knowledge Modules , Logic , ODI , Technology , Tips and Tricks

Tags: , , , , ,

Single Post View

11g Oracle Data Integrator – Part 5/11g – odiConsole, the new Metada Navigator

Dear Friends,

In ODI 10g we used Metadata Navigator to login which acted as Web portal to Trigger and view the listed Scenarios and also Light weight designer .

In ODI 11g we have odiconsole which can be configured using the Oracle WebLogic.This post shows step by step configuration  and initial setup to login into ODI console and setting up of Standalone agent to trigger scenarios from odiconsole.

[ Note – The Server name , Port No and other configuration used is for demonstration only and Please use the appropriate  for your environment ]

Launch the Initial configuration Wizard of weblogic.


Select the Following options as shown below.


Give a meaningful domain name.


Provide the Administrator User Name and Password ,required to login into domain account.


Select the required JDK from the options


Provide the details of the MASTER Repository account


Provide the details of the WORK Repository account


Click Next and test the accounts . If you have error in this step , its means the account information is wrong , Please go back and do the required changes and once the account are correct ,you will get a Test successful message.


Click the Following as we need to do few settings changes.


If required change the Listener address to the required IPaddress or hostname you want to domain to be and similarly the Port No . For this post the default is used .


Configure the Machine Name with Node Manager listener address and  Port


Assign the Server , although by default the odi_server1 is assigned but I have assigned Adminserver too so that I can access the odiconsole from 7001 port itself.


Make sure you check the option oraclediagent and odiconsole under Application


Check the JDBC  Services and odiMasterRepository and odiWorkRepository


Confirm all the setting and options are correct and click on Create.


Finally ,you will get a Domain created with the Admin Server URL . Check  mark Start Admin Server


Go to your Web Browser and type in the following address –http://localhost:7001/console/login/LoginForm.jsp

Change the local host  and port to whatever is listed in the Admin Server URL above .


Once logged in , Under Environment – > click on Servers


You will able to see all the Server .


Click on Adminserver . You will be taken to a page as shown below  , click  Deployments and you will be able to see odiconsole , oraclediagent .


Expand odiconsole and you will be able to see odiconsole under Modules . Click that and you will be taken to another page


Go to Testing tab and expand odiconsole there .ah! finally we got our url . Click the url on default.  http://localhost:7001/odiconsole


Once logged in you will be able to see the Master and Work Repository.


Use the required SUPERVISOR account to login


Sample Work Repository Console Screen


Sample Master Repository Console Screen



Here I am trying to trigger the Scenario EMPLOYEES  from odiconsole and I get the following error.


To Resolve the above error , go your <ODI_HOME>COMMONBIN folder and type in the following command

D:OracleMiddleware_32bitOracle_ODI_32bitcommonbin >wlst.cmd

We need to connect to the above created weblogic connection ,provide the user name , password  and url as shown in the image . [ Please find the scripts below the images  ]



Once connected , create the credential with the SUPERVISOR user account and password details


Finally disconnect


The scripts is mentioned here  , Please change accordingly.

createCred(map=”oracle.odi.credmap”, key=”SUPERVISOR”, user=”SUPERVISOR”, password=”SUNOPSIS”, desc=”Key for Supervisor”)

Here the weblogic is the username , odiexperts01 is the password to login into weblogic and localhost:7001 is the  listener address and port number

In the createCred , provide your SUPERVISOR user id and password

Learn more about the above scripts here in this Oracle Link –http://download.oracle.com/docs/cd/E14571_01/core.1111/e16453/install.htm#ODING412

I triggered the same Scenario again and this time the execution was successful.


Successfully executed in the Operator


Successful execution report in odiconsole.


We are trying to bring more post and blog on ODI 11g to make your transition to ODI 11g as smooth as possible. Smile

Keep looking for odiexperts for  more tips , tricks on ODI 11g .

Thank you for visit us!

September 10, 2010
by Cezar Santos

Categories: Architecture , How to , ODI , Tips and Tricks

Tags: , , , , ,

Single Post View

Using one of ODI’s lesser-known secrets: the Common Format Designer (CFD) – by Craig Stewart

Hi Reader,

It’s a great pleasure starts a new “sector” from ODI Experts. It is our “Invited Experts” and we will start with one of the main ODI Guru in the world. Craig Stewart!!!

Thank you Craig!

Please, enjoy his post!


As I’m essentially in pre-sales, I like tools which I can use to rapidly do tasks which might otherwise take a lot of grind. Things like the Knowledge Modules in Oracle Data Integrator (ODI) are well known to take complex tasks and to make them simple – like integrating slowly changing dimensions: Simple with the IKM. One of the things which was introduced into ODI (actually into Sunopsis, before the acquisition by Oracle, was the Common Format Designer, In the latest release of ODI (11g) it is actually much better documented (even if it has not evolved much!) than in earlier versions – but what I’d like to show you briefly here is how it can be used to rapidly implement a new schema and to generate the interfaces to populate it.

To start with, let’s say I have a set of tables in my source database, in my case I’ll use MySQL. If I want to move that set of tables, with data across to Oracle, I’ll need to have a schema in Oracle where I can put the tables. In ODI’s Topology Navigator (see, I’m already using the new 11g terminology!) I need to define the Data Servers, the physical schemas and associate them with the relevant logical schemas. This is the point at which I’ll begin, I have a data model in ODI which has my “source” data defined in it:


I want to put this into my Oracle schema which I’ll call ORCL_TMP, so in Desgner, I create a new, empty data model in the Oracle technology for this schema:



Now I create a diagram, by using the Right-Mouse-Button on the Diagrams Node:


I must simply give this a name and any description, and naturally, the documentation…


Then, we go to the Diagram tab so we can start doing the business:


Now we drag & drop the datastores from our MySQL model, onto the new ORCL_TMP diagram, ODI will prompt us with a dialog box:


Which of course we know, and accept. The table is then added to the model, automatically using the metadata about datatype translations to construct the target table.


This will then show in the diagram:


We can then drag across additional datastores to make our model:


Note that in 11g, you are able to select multiple datastores and drag them across to the diagram. One drawback to this is that when referential integrity is defined, using this technique won’t get you the referential integrity, whereas if you drag one at a time, you will. It will give you all keys, indexes foreign keys as defined in the source system, and applicable in the target.

Now for some magic: if you right-mouse-button the Data Model, one of the optrions is to “Generate DDL”:


If we select this option, ODI will ask if we want to process tables not in the moidel (i.e. generate deletes to synchronize the two, or just to generate the delta code for the tables we have in the model. I’ll select “No” for this:


This will bring up a dialog where I can choose which of the differences I want synchronized:


If I select all, and press the OK, it will generate an ODI procedure for me in the Default Folder I entered when I defined the model:


I can press the “Play”/”Execute” button to get ODI to execute the generated code, which I can then follow in the Operator Navigator:


Often, you will get errors with the tables which contain DATE/DATETIME/TIMESTAMP fields as ODI is not always clever about translating them. In this instance I just edited the generated code to take out the TIMESTAMP(23) and changed it to TIMESTAMP. So now I have a fully created data model. The next step is to get some data moved in, so I need interfaces. ODI will of course remember where the data stores were dragged from, and to, so will generate for me an interface which just does a one-to-one mapping using the default knowledge modules:


I select the “Generate Interfaces In” and ODI brings up a dialog for the tables:


The required interfaces are then generated in that default directory:


I need to go into each one in order to execute it (If you try to execute without going in and saving it, you will get an error) You have to force a change, so that ODI can go through the process of saving it, which in turn makes it do some internal stuff, enabling execution. Then execute it.

Now you have data in your newly created data structure!

Craig Stewart

September 1, 2010
by Cezar Santos
1 Comment

Categories: ODI

Tags: ,

Single Post View

First year of many…. Thank you Friends!

While writing another post for odiexperts , I realized that its been a long time since we have opened odiexperts.com and we haven’t celebrated our first birthday Smile . So I scrolled back to Cezar’s first lovely post – Welcome to ODI Experts, this is Cezar Santos.I saw the posted date  of August 16 , 2009 . Yeppiee !!!  we have crossed an year but we forgot to celebrate our first birthday, so even though we are a little late but not too late .

I  welcome you all for our lovely birthday and Thank you so much Cezar for giving me the opportunity to write in this blog and above all a big Thanks you to all our viewers and everyone who has made this blog a success . Thank you so much.

A special thanks to Craig Stewart who have provided us his value expertise and suggestion and making the post more valuable .

We will try our best to keep supporting and bringing more ideas ,tips , best practices of ODI to all of you and above all we request all viewers to provide us more value suggestion so that we can provide more quality and work in our effort towards bringing the best of ODI .

— Kshitiz Devendra

Yes… Birthday…  this is one of the days that we realize how fast is the time…

I like to write but I’m not sure if I have the right words today… but Thank you ALL!

Dev, our partnership is a success! Your hard-work, intelligence, dynamism and fast mind could bring to ODI Experts the possibility to be here until today.

I don’t know if all friends and readers of ODI Experts Blog knows but we don’t make any money from this work. We are here because we love what we do. A lot of people thinks we are Oracle employee, but not, we doesn’t have any professional relationship to Oracle.

When we got emails (and they are several by day) or reply a comment or even reply at ODI Official Forum, is just because we like to help and, even more, see the people happy in realize his job.

Because of that, I need to thank to some friends that helped us in the same spirit.

They are  Craig Stewart and Christophe Dupupet
Thank you very much guys! Every time I asked something  you always are there!
— Cezar Santos


Thanks a lot our lovely viewers from both of us . We love to see you again and keep helping us to grow odiexperts.com


Cezar Santos and Kshitiz Devendra

August 31, 2010
by Cezar Santos

Categories: Architecture , How to , Logic , ODI , Tips and Tricks

Tags: , , , , , ,

Single Post View

11g Oracle Data Integrator – Part 3/11g – Graphical User Interface

Hi Everyone…

One more post in our “Oracle Data Integrator 11g” serie.

In the last two features of the ODI 11g we have been covering the Initial and repository logic part . Now in this Post we will be covering few of the Inside looks, option changes in ODI 11g.

The idea is show what changed in the GUI. How to find some features that we already know and some new.

As I already said, now there is just an “ODI Studio” and the 4 ODI modules are inside the same visual interface.

Once logged into the ODI 11g we find that unlike different component in ODI 10g we have all the Designer, Topology , Operator and Security Manager integrated into Sub tabs which can be Closed and called again. If you have accidently closed any Navigator , you can call from View options.


Even in the each Component, instead of Tabs we find Sub sections.


Others and Solution are something we don’t use more and we can minimize them to have more space. To minimize, at the end you will find down arrow under each sub section and you can click on it and click on Minimize.


All the minimized sections are stored under this small bar with three dots at the end .


Once click you will find all the minimized objects. Click on it to expand.


Option which were seen in the title bar option in ODI 10g is not available as Scroll bar option



You can find the Import Work Repository option and other options which we found in File in 10g are now again as scroll menu at the top tab in Designer.


Similar such categorization and formatting is pretty much applied to other components of ODI.

ODI 11g also allows few good handling of Tabs while working with multiple or parallel Interface or Models etc . By default when you open objects in ODI they open by tabs .


and if you want to see two parallel tabs , and select New Editor Tab Group and see the objects in parallel and you can do for multiple objects.



The Interface is a little different in ODI 11g .


In ODI 11g we can split the Inter face into two screens, you can enable those options


And this way,you can see the two part of the ODI Interface simultaneously, the only restriction you cant see the same option on both section.


We can also view the source data store as Compact and symbolic.



There is also new add a lookup functionality ,where you can add datastore and make the required joins in a form of wizard.




and has a different color for lookup


You can also optimize the size of Datastore .



There are more user defined Flags


There is also a Quick Edit tab where we can see all our joins , lookup and all other information and we can edit and modify them accordingly .


The Flow Control bar is little different from previous version and you can select the IKM and LKM when you select the Staging Area.


ODI 11g supports Partition and Sub partition too and we shall cover them in a separate post in near future in detail .


The Models are pretty much having the same functionality except instead of Reverse button we have an icon to do that . The green arrow icon lets us to do our Reverse engineer.


In the Column as we have discussed before we have support for Partition and Sub Partitioning


Well, we tried to give you a general vision of everything in the graphical interface and its characteristics. More will come in the next days….

Thank you for visit us!

August 28, 2010
by kdevendr

Categories: Administration , Common Errors , How to , Logic , ODI , Technology , Tips and Tricks

Tags: , , , , ,

Single Post View

Datatypes – Creating what is missing for any technology

Hi Guys,

Another really usefull tip about data types!

There are some time it happens we don’t have the required data type in respective Technology in ODI say for example DATETIME YEAR TO DAY ,DATETIME YEAR TO SECOND etc and so when we do a Selective reverse or RKM we don’t get the correct data type listed causing which we get the Null data type or get assigned to String .

The other simple solution if you are particular in getting the data type listed in Model . Here are the steps to Manually add the data type in Topology and create the respective conversation syntax.

Step1 . Go to the Respective data type and right click and Insert Data type or Duplicate an already existing datatype

Step2. Provide the required code and syntax


Step 3 . Provide the required converted To and Converted From detail for the Technology you are going to Work with . Lets say my target will be Oracle in that case , I have assigned to Timestamp .


Easy and Simple solution in dealing with Data types missing.

Any comments???

It’s our pleasure to have you here, at  www.odiexperts.com

August 26, 2010
by Cezar Santos

Categories: How to , Logic , ODI , SQL , Tips and Tricks

Tags: , , , , ,

Single Post View

IS_NUMBER (and IS_CHAR) at Oracle? A workaround…

Hi Friends,

Right now I’m in São Paulo airport waiting for a flying that is very delayed! With some time free, let me write about something that I want to publish there is a long time….

Surprise! Isn’t an Oracle Data Integrator issue! hehehehehehe……

I really like to work with ORACLE RDBMS but doesn’t exist a “Is Number” function is, in my opnion, a lack of funcionality that I can’t understand.

Every time that is necessary to check if the returned value from a column (or a substring from it ) is number, I created a database (PL/SQL) function that return true or false and then uses it in the query.

In this way works, but if I want to do the same in a Microsoft SQL Server, there is a “native” function to do it.

The big problem is that, sometimes, you don’t have an user that can create PL/SQL procedures and, then, you can’t do this type of validation.

For solve this, I created the following technique:

A single SQL query that can return if a column is number or alphanumeric

select case when trim(TRANSLATE('1234f','0123456789-,.', ' ')) is null
            then 'numeric'
            else 'alpha'
from dual

Where ‘1234f’ should be substituted by the column that you wish to validate.

The principle is very simple, if  the numbers from a string are removed and the result is different of null then the string isn’t numeric. The Trim funcion eliminates any remained space.

Plus, it is pretty usefull when there some non-visible ASCII char  in a column.

Well, I couldn’t let to show a use of this in ODI.

When there is a text file to be loaded and it is necessary to check (CKM) if the columns are with the right data (thru ODI constraint) this single query can send to the error table (E$) any record that has the wrong data.

See you around!