Accelerating development via the Oracle Data Integrator SDK

A nice post and example of ODI 11g SDK use by David Allan can be seen on this blog.

David writes:


Often using ANY tool there are scenarios where there is a lot of grunt work, imagine Microsoft tools like Excel without VB and macros to accelerate and customize those boring repetitive tasks. Data integration and ETL design is exactly the same, the tool needs to expose an SDK to a base platform that you can use to make your life easier. Something to automate the grunt work that is common and very repetitive. The ODI 11g SDK let's you script these kind of repetitive tasks. As an aside the ODI common format designer (see this post here) has a way for migrating like named objects, however using the SDK let's you control much much more.

To illustrate I have created a simple interface construction accelerator that you can download (, the accelerator generates ODI interfaces from a control file that defines the interface name, the source and the target - simple and a nice example for demo purposes. If you look at the java code, it is very basic (no pun intended).  It literally is a dozen lines of code. The image below illustrates the java program interfaceAccelerator using the ODI 11g SDK to take as inputs the configuration of the connection details and a control file specify the source to target datastore mappings.


The code when called has a bunch of command line parameters shown below and the standard input stream is the interface control file, so the command line looks like;

java -classpath <cp> interfaceAccelerator <url> <driver> <schema> <pwd> <workrep> <odiuser> <odiuserpwd> <project> <folder> < <control_file>

the control file provided in the standard input stream needs to be a comma delimited file with the following structure

    • interface_name,source_model,source_table,target_model,target_table
    • ...

for example a sample command line using an Oracle repository could be

java -classpath <cp> interfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK < icontrol.csv


the interfaces will be created in the folder SDK and the project code is STARTERS. The icontrol.csv file used above was (remember the format is interface_name,source_model,source_table,target_model,target_table, this is just what I happened to use in this simple demo program);


You can created as many interfaces from this driver control file as you desire, the interface generated will map from the source table to the target table and use ODI's auto mapping to perform column level mapping of the source to target table, it will also create default source sets and use the default KM assignment. So you get a pretty useful set of stuff as a basis here.

The interfaces generated whilst executing this accelerator look like the following, the table to table map with all of the like-named columns mapped, the physical flow configured with defaults KMs!


You can take this code and customize to make it fit your needs or send in comments on how to do things. In summary if you are finding you desire ways of tuning your work to make using ODI even more productive, then you should look into the ODI 11g SDK and see if you can automate, automate, automate.



Hi FX/David,

I am using ODI for a data migration project. Can you give me some idea on how to source control the ODI repositoriy mappings ?


Posted by guest on August 02, 2011 at 07:10 PM PDT #

Hi FX/David,

I am facing another issue while migrating data for high volume data tables between one database to another? do we have something inbuilt in ODI for the same (wherein it internally creates the pasrtition and do the lifting and shifting of data )

Posted by guest on August 02, 2011 at 07:12 PM PDT #


You can leverage Oracle database's interval partitioning for automatic creation of partitions if targeting Oracle for example. There are lots of possibilities that could be done within KMs (including partition exchange loading), depends on your scenario. What problems are you having?


Posted by David on August 08, 2011 at 02:31 AM PDT #

Hi David,We are planning to automate the process of interface creation in our project.This article helped us in getting an insight into ODI 11g SDK and it helped us in designing the basic architecture needed for our automation tool.Thank you very much.

Posted by guest on December 16, 2011 at 12:38 AM PST #


Good, I am glad it was helpful, the SDK certainly opens up a lot of automation possibilities.


Posted by David on December 19, 2011 at 02:13 AM PST #

Hi David,
I just want to know whether custom mapping for target(auto mapping not required)is achievable or not using SDK.If yes how?

Thanks in advance.

Posted by aniket on February 09, 2012 at 12:45 AM PST #

Hi Aniket

Yes, you can build whatever you do in the UI with the SDK, so could have an accelerator building the interfaces from an Excel definition or just some textual driver files.

For example the code below has an example of defining an expression using the UPPER function;
Set<MappingDefinition> mappings = new HashSet<MappingDefinition>();
mappings.add(new MappingDefinition("Col1", "UPPER(T.C1)"));
mappings.add(new MappingDefinition("Col2", "T.C2", "mapping for C2"));
interfaceHelper.setTargetDataStore(targetDatastore, mappings);

This uses the MappingDefinition class;

There are other samples on OTN in the zip below worth having a peek at;


Posted by David on February 09, 2012 at 10:37 AM PST #

Check the example below it is a driver that takes a control file as input which defines the interface in a text file and generates an ODI interface...


Posted by David on February 09, 2012 at 01:39 PM PST #

How to run the interface? I am able to create the interface using your tutorial but now i want to run it and get the status of the operation..

how do I automatically run the interface?

Posted by parth on July 04, 2013 at 02:14 AM PDT #


You can use the remote runtime agent class for example to invoke a scenario, check out the class in the SDK. Here is a snippet of its usage;

RemoteRuntimeAgentInvoker remoteRuntimeAgentInvoker = new RemoteRuntimeAgentInvoker("http://localhost:20910/oraclediagent", "YourOdiUser", "YourOdiPassword".toCharArray());
ExecutionInfo exeInfo = remoteRuntimeAgentInvoker.invokeStartScenario("YOURSCENARIO", "001", null, null, "GLOBAL", 5, null, true, "WORKREP");

// Retrieve the session ID
System.out.println("Session : " + exeInfo.getSessionId());


Posted by David on July 08, 2013 at 01:21 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


« May 2016