Tagged ‘odi‘


In the second part of the series we cover:

More discussion on ODI vs Informatica
More on migrating from OWB to ODI
Using ODI outside the data warehouse (BI Apps)
ODI in the cloud
ODI and Big Data


ODI 11g Cookbook – The leading ETL tool now also has the best ETL book

I don’t know too many books or movies where the sequel is better than the original. The ODI 11g cookbook is such a rare case. It is stuffed with 60 valuable recipes that every ODI developer should know. Apart from the value of these recipes in their own right they also showcase the flexibility of ODI and can be transfered to other problems.

My favourite chapters are; Knowledge Module Internals, Advanced Coding Techniques, Advanced Topology, and Using Variables.

A big thank you to the ODI product management team for sharing their insights.

How to query a whole schema or even database?! This tip will save you hours and hours of boring work.

I really like this tip especially its simplicity. It has saved me hours and hours of mind numbing work in the past.

The use case

Imagine the following situation. You are tasked to complete a source to target map. With the help of various source system SMEs, legacy data models, and the data dictionary of the source database you have been able to complete 99% of the mappings. However, there is that one field that is nowhere to be found in the source system (there’s always one). The business users have given you some sample values for this field. Wouldn’t it be nice to run a query such as SELECT column_name FROM schema/database WHERE value = ‘Sample Value’.

I will show you how you can achieve something similar with ODI.

The trick is to dump the content of your schema or database into a folder and then use grep your Windows search or whatever to search for your sample value.

In ODI this is extremely easy. Using the Command on Source/Target functionality it can be completed with two lines of code.

An example

Our objective is to find the table where ODI stores Option values for interfaces.

In a first step we create the procedure that dumps the content of the ODI schema to a folder

Command on Source

We query the data dictionary for those tables that are owned by the ODI work repository user. If you want to dump out the whole database just get rid of the WHERE clause.

SELECT table_name,owner from all_tables WHERE owner = ‘<name of your work schema>’

Command on Target

We use the tool OdiSqlUnload to unload those tables from the Command on Source recordset. For each table we create a separate text file.

OdiSqlUnload “-FILE=C:\unload\#table_name.txt” “-DRIVER=oracle.jdbc.driver.OracleDriver” “-URL=<your connection>” “-USER=#owner” “-PASS=<your password>” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=;” “-ROW_SEP=\r\n” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1″ “-XML_CHARSET_ENCODING=ISO-8859-1″

SELECT * FROM #table_name


In a second step we enter a dummy value for one of our interfaces. This will be the value that we will search for in the dump.

Our dummy value is XXXXXX.

Next we run the procedure. This will dump the table contents into files.

Finally, we use Windows Search, grep or similar to find the file where our sample value is stored.

Windows search comes back with one result. The last column of table SNP_UE_USED, which is I_TXT_VALUE.

I am sure you can think of many more use cases for this. Of course, you can make all of this more sophisticated and wrap it up in a package with options to run against different databases such as MS SQL Server etc.

For very big databases you will need to watch performance. You may want to only extract a subset of tables and search for your sample value there before moving on to the next set and so on.

Load Knowledge Module Oracle to Oracle using database links

ODI comes with an LKM to extract data from another Oracle database instance using db links via a view that is created at the source system.

The existing LKM Oracle to Oracle (DBLINK)

I see two problems with the way this KM is implemented.

1. It creates a database link on the fly. What if you want to run multiple interfaces in parallel that rely on this db link? You are fecked.
2. This is bad practice as far as I am concerned. What if the source DBA does not give you privileges to create views?

In summary this LKM is not usable.

I have created an LKM Oracle to Oracle using a db link that creates a view on the target rather than the source. It also does not generate the db link on the fly. A C$ view is used rather than a C$ table as this is similar to extracting from another Oracle schema and in theory no LKM should be required at all.

How to use the improved LKM Oracle to Oracle (DBLINK View Target)

You need to create a database link in the work schema of your target.

You then need to append the name of this db link to the Resource Name of your source data store(s). In the example below our db link is @PVPROD.


That is it. Now download the LKM Oracle to Oracle (DBLINK View Target) and use it.

If you don’t want to hard code the name of the database link into the Resource name in your datastore you can use the following technique.

Declare the variable in your Interface Diagram, e.g.


And in your Resource name print out this variable


For various reasons you can’t use ODI variables in the Resource name. One of them is that the getfrom() method in the ODI substitution API does not give you the full control that would be needed to set this up correctly. So you are stuck with the technique above.

Need ODI Training? Learn ODI from the experts!

I am proud to announce that Sonra are now offering ODI classes.

Value-priced. Customised. Onsite-Offsite. Online.

Five day standard training course.
Starts 24 February 2014.
Book before 15 January 2014. Pay $949.

More information on our ODI courses.

Accessing a mapped network drive with the ODI agent as a Windows service

The problem

The other day we had a requirement to extract data from an Access database on a mapped network drive. For reasons too complicated to outline here, we could not use a UNC.

While there is no problem to access the mapped drive from the Designer module using the Local Agent, things don’t look so good if you are using an agent running as a Windows service.

The error we got was as follows:

-1023 : S1009 : java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] ‘T:\xxx\xxx.mdb’ is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

What is the issue?

This is basically a limitation of running programs as a Windows service

Below is an extract from INFO: Services and Redirected Drives from Microsoft support

If a service is configured to run under a user account, the system will always create a new logon session for the user and then launch the service in that new logon session. Thus, the service cannot manipulate the drive mappings that are established within the user’s other session(s).

Microsoft recommned to only use UNCs to access resources on network drives when using a Windows service.

The workaround

So what if you can’t use a UNC? To workaround the problem you can map your network drive on the fly in your specific session.

How does this work in ODI?

We simply create a mapped drive in an ODI procedure or as part of a KM that uses the net use command.

NET USE t: \\<server>\ap$ /USER:<Domain>\<User> <Password>

Make sure to tick the Ignore Errors checkbox in your procedure step. The mapped drive exists for the lifetime of the agent, i.e. until it is restarted.


Subselect, derived tables, and subqueries in ODI 11G

I think one of the best new features in ODI 11G is the subselect/subquery feature. In ODI 10 this could only be achieved by a workaround as outlined in a previous post Using subqueries in Oracle Data Integrator.

What is the advantage of a subquery?

In previous versions of ODI we had to physically set down the data for each indiviudal query, e.g. if we wanted to rank a dataset and then select the top ten out of that dataset we had to create an interface for the ranking operation and an interface for the top ten operation. At each point we had to set down the data thereby increasing I/O and decreasing overall performance. In ODI 11 this has changed. While we still need to create two temp interfaces for the operation as a whole we can now tell ODI to treat the first temp interface as a derived table. ODI 11G will then use this to generate a subquery.

How does the subquery work in ODI 11?

The way this works is fairly simple. For each subquery/derived table in your query you create a temp interface. You embed your various subqueries by simply telling ODI that you want to use the temp interface as a subquery.

A step by step guide

We will look at an example from the SH schema. The task at hand is to load a table with the top ten customers based on sales amount.

To accomplish this task we need three temp interfaces.

The first temp interface (INT_SALES_CUST) will aggregate the sales amount from the sales table by cust_id


The second temp interface (INT_SALES_RANK) will take the resultset from the interface in the previous step and dense rank the customers’ sales data.


The third interface (INT_TOP_TEN) will then select the top ten customers, join to the customers table, and physically set down the data.


So far so good. These are the same steps we took in ODI 10.

In a next step we need to subquery enable the interfaces. We open interface INT_SALES_RANK and click on the INT_SALES_CUST data store. In the Source Properties section you will find a checkbox Use Temporary Interface as Derived Table. Select this checkbox.


We also need to perform the same step for our third interface INT_TOP_TEN.

Once this has been done we can execute interface INT_TOP_TEN in Simulation mode.


This will generate the required query with the embedded subqueries from the temp interfaces. What a great feature.

INSERT INTO sh.cust_top_ten
SELECT sales_rank,
FROM   (SELECT sales_rank.sales_rank     sales_rank,
               customers.cust_first_name cust_first_name,
               customers.cust_id         cust_id,
               sales_rank.sales_amt      sales_amt,
               customers.cust_last_name  cust_last_name
        FROM   (SELECT sales_cust.cust_id
                       Dense_rank() over (ORDER BY
                       sales_cust.sales_amt DESC) sales_rank
                FROM   (SELECT sales.cust_id           cust_id,
                               SUM (sales.amount_sold) sales_amt
                        FROM   sh.sales sales
                        WHERE  ( 1 = 1 )
                        GROUP  BY sales.cust_id) sales_cust
                WHERE  ( 1 = 1 )) sales_rank,
               sh.customers customers
        WHERE  ( 1 = 1 )
               AND ( sales_rank.cust_id = customers.cust_id )
               AND ( sales_rank.sales_rank <= 10 )) odi_get_from 

ODI Training. Learn ODI from the experts.

You may also be interested in our ODI training courses. Value-priced. Customised. Onsite-Offsite. Online. Get all the details from our ODI training site

Oracle Data Integrator: ODIInvokeWebService and Java 6

ODI has a built in SOAP client, the ODIInvokeWebService tool. However, there is one caveat when you try using this with Java 6/JDK 6. It simply doesn’t work and you will get the following error:


This is documented in note 1085594.1 and the suggested workaround is to fall back to an earlier version of Java. Another option of course is to install another agent that uses Java 5 alongside your Java 6 agent.

The first step we need to take is to install JDK 5. You can download JDK 5 from the Oracle website.

If repositories are running on Oracle we also need to download the compatible Oracle JDBC 5 driver. If you are running your repositories on another RDBMS get the equivalent JDBC driver.

Next, inside the oracledi folder we create a new folder and name it bin_jdk5.

We then copy the content of the bin folder into bin_jdk5.

The next step will be to create a new environment variable and name it ODI_JAVA_HOME2. The variable needs to be pointed to the JDK 5.


As a next step we need to edit the odiparams.bat file in the bin_jdk5 folder. Open the file in Notepad and replace ODI_JAVA_HOME with ODI_JAVA_HOME2


In the same file we also need to replace any occurrence of the word drivers with drivers2


Next we create a new folder drivers2 inside the oracledi folder and copy and paste the downloaded Oracle JDBC 5 driver into it. Alos copy any other drivers from the drivers folder into the drivers folder that you may want to use with this agent


Finally we create a new agent that uses the JDK 5 as a Windows service.

First edit file agentservice.bat and replace bin\odiparams.bat with bin_jdk5\odiparams.bat as per figure below


Then we create a copy of file snpsagent.conf in folder oracldi\tools\wrapper\conf. Replace any occurrence of drivers with drivers2.

We also replace wrapper.working.dir=../../../bin/ with wrapper.working.dir=../../../bin_jdk5/

Next we create the physical and logical agent in Topology Manager.

Finally we install the agent as a listener or scheduler agent.


In one of the next posts I will show how we can make good use of ODIInvokeWebService to query the OBIEE web services API.

Nesting ODI Substitution Methods. Part II.

Today we look at how we can escape quotes when nesting ODI substitution method calls at different levels of the multi pass parser.

As an example we will store the columns of the C$_ datastore and the columns of a target datastore in a two dimensional java array.

   String[][] ColList = {<%=snpRef.getColList("{", "\u0022[EXPRESSION]\u0022", ",", "}","INS")%>,<%=snpRef.getColList("{", "\u0022[COL_NAME]\u0022", ",", "}", "INS")%>};
for (int i = 0; i < ColList.length; i++) {
   for (int j = 0; j < ColList[i].length; j++) {

As you can see, we are nesting a call to the ODI substitution API inside aparse block. Normally in Java we escape a quote with \”. In ODI this would throw an error. What we do instead is to use the Unicode representation of the quote character: \u0022

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Calling custom Java classes and JAR files in ODI via Jython or Java BeanShell

First we create a simple Java class that creates and writes to a text file and save it as


public class FileWrite

  public void writeFile()
    FileOutputStream fos;
    DataOutputStream dos;

    try {

      File file= new File("C:\\MyFile.txt");
      fos = new FileOutputStream(file);
      dos=new DataOutputStream(fos);
      dos.writeChars("Hello World");

    } catch (IOException e) {

Next we compile the .java class from the command line


Next we create a .jar file from the class

c:\jar cf FileWrite.jar FileWrite.class

We then copy and paste the .jar archive to the ODI drivers folder

Next we restart the ODI agent.

We can now call methods in this class from either Jython

import FileWrite



or we can call it from the Java BeanShell

import FileWrite;

FileWrite fw = new FileWrite();


If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

ODI and analytic functions (again???)

My friend and colleague Maciej Kocon has come up with the following trick. Rather than use a user defined function to implement an analytic function such as MAX() OVER or SUM() OVER as suggested by note 807527.1 there is another way to work around the getGrpBy bug.

We will take advantage of the ODI multi pass parser and hide the analytic function from getGrpBy in our mappings:


The above SUM gets only generated once getGrpBy has been parsed and as a result no GROUP BY clause is generated, which is the correct behaviour.

A brilliant yet simple trick.

Cheers Maciej.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)