Friday Mar 02, 2012

ODI 11g – Insight to the SDK

This post is a useful index into the ODI SDK that cross references the type names from the user interface with the SDK class and also the finder for how to get a handle on the object or objects. The volume of content in the SDK might seem a little ominous, there is a lot there, but there is a general pattern to the SDK that I will describe here.

Also I will illustrate some basic CRUD operations so you can see how the SDK usage pattern works. The examples are written in groovy, you can simply run from the groovy console in ODI

Entry to the Platform

Object Finder SDK
odiInstance odiInstance (groovy variable for console) OdiInstance

Topology Objects

Object Finder SDK
Technology IOdiTechnologyFinder OdiTechnology
Context IOdiContextFinder OdiContext
Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
Data Server IOdiDataServerFinder OdiDataServer
Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

Project Objects

Object Finder SDK
Project IOdiProjectFinder OdiProject
Folder IOdiFolderFinder OdiFolder
Interface IOdiInterfaceFinder OdiInterface
Package IOdiPackageFinder OdiPackage
Procedure IOdiUserProcedureFinder OdiUserProcedure
User Function IOdiUserFunctionFinder OdiUserFunction
Variable IOdiVariableFinder OdiVariable
Sequence IOdiSequenceFinder OdiSequence
KM IOdiKMFinder OdiKM

Load Plans and Scenarios

Object Finder SDK
Load Plan IOdiLoadPlanFinder OdiLoadPlan
Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

Model Objects 

Object Finder SDK
Model IOdiModelFinder OdiModel
Sub Model IOdiSubModel OdiSubModel
DataStore IOdiDataStoreFinder OdiDataStore
Column IOdiColumnFinder OdiColumn
Key IOdiKeyFinder OdiKey
Condition IOdiConditionFinder OdiCondition

Operator Objects

Object Finder SDK
Session Folder IOdiSessionFolderFinder OdiSessionFolder
Session IOdiSessionFinder OdiSession
Schedule OdiSchedule


How to Create an Object?

Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.

import oracle.odi.domain.project.OdiProject;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

project = new OdiProject("Project For Demo", "PROJECT_DEMO")

How to Update an Object?

This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiProjectFinder;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class);
project = prjFinder.findByCode("PROJECT_DEMO");

project.setName("A Demo Project");


How to Delete an Object?

Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.

import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;
import oracle.odi.domain.runtime.session.OdiSession;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class);
sessc = sessFinder.findAll();
sessItr = sessc.iterator()
while (sessItr.hasNext()) {
  sess = (OdiSession)

This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. For details of how specific complex objects are created via the SDK, its best to look at postings such as the interface builder posting here. Have fun, happy coding!

Tuesday Feb 28, 2012

ODI 11g - Scripting the Model and Topology

Scripting is the ideal mechanism to automate start up and teardown for repeated tasks and those that you just want to automate. Here are a couple of more illustrations of how to easily construct a model in ODI, the script will also create all of the topology objects. The script uses two methods; createLogicalSchema and createModel. The createLogicalSchema creates the logical schema, data server, physical schema and logical schema to physical schema mapping via a context all from one function call.

The signature of these methods looks like this;


contextCode – the ODI code for the context used to map the logical schema to the physical

technologyCode – the ODI code for the technology

nameForLogicalSchema – the name for the logical schema to create

NameForDataserver – the name for the data server to create

userNameForAuthentication – the username for the connection to the data server

passwordForAuthentication – the password for the connection to the data server

urlForAuthentication – the URL for the connection to the data server

driverForAuthentication – the JDBC driver for the connection to the data server

schemaForAuthentication – the schema to use for the ODI physical schema


logicalSchemaObject – the ODI logical schema object (instance of ODILogicalSchema)

contextCode – the ODI context code for reverse engineering

nameForModel – the name for the model to create

codeForModel – the code for the model to create

So with these two methods or variations of them you can easily construct your topology objects and models. For example the call below creates a new model named ORACLE_MODEL and all of the topology objects that will allow me to go straight to reverse engineering when the script has been run.

lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT",

    ObfuscatedString.obfuscate("<password>"), "jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT")

createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")

Here is the source code for the script

import oracle.odi.domain.util.ObfuscatedString;
import oracle.odi.domain.model.OdiModel;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.OdiPhysicalSchema;
import oracle.odi.domain.topology.OdiDataServer;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiTechnology;
import oracle.odi.domain.topology.OdiContextualSchemaMapping;
import oracle.odi.domain.topology.AbstractOdiDataServer;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.domain.topology.finder.IOdiTechnologyFinder;

def createLogicalSchema(contextCode, techCode, schName, dataserverName, userName, password, url, driver, schema) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)

  contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);
  context = contextFinder.findByCode(contextCode);

  techFinder = (IOdiTechnologyFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiTechnology.class);
  tech = techFinder.findByCode(techCode);

  lschema = new OdiLogicalSchema(tech, schName)
  dserver = new OdiDataServer(tech, dataserverName)
  con = new AbstractOdiDataServer.JdbcSettings(url, driver)
  pschema = new OdiPhysicalSchema(dserver)
  cschema = new OdiContextualSchemaMapping(context, lschema, pschema)

  return lschema

def createModel(lschema, contextCode, modName, modCode) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)

  contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);
  context = contextFinder.findByCode(contextCode);

  mod = new OdiModel(lschema, modName, modCode)
  return mod

lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT", ObfuscatedString.obfuscate("<password>"),
"jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT")

createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")

Have fun scripting!

Wednesday Feb 08, 2012

ODI 11g – More accelerator options

A few more options added into the interface accelerator that I blogged about earlier here in initial post and a later one here. Added options for doing position based and case sensitive/insensitive options. These were simple changes added into the auto map class. You can now find the latest updates below;

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceAccelerator;

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

In the automapper I created a couple of options that can drive the accelerator, it supports;

  • positional based match (match columns by position from source to target)
  • exact match case sensitive  (match EMPNO with EMPNO, but not empno with EMPNO)
  • exact match case insensitive (match EMPNO with empno)
  • src/target ends with sensitive/insensitive (match PFX_empno with empno/EMPNO)
  • src/target starts with sensitive/insensitive (match empno_col with empno/EMPNO)

Note, you can also use the “diagrams” in the models to greatly accelerate development if source and targets have the same structure – if not then you have to go through the SDK route above if you want to accelerate.

Saturday May 21, 2011

ODI - Extending the Interface Accelerator

The post here illustrates how to use the ODI 11g SDK to extend the classes provided to customize your own auto mapping capabilities;

Thursday May 12, 2011

ODI 11g - Getting Groovy with ODI

Here we will see how you can use the groovy interpreter to automate, automate, automate (with no compile,compile,compile). When you get into large enterprise configurations of software, automation is the name of the game. The ODI 11g SDK as I blogged here lets you interact with pretty much the entire ODI set of metadata. The example I posted was a (or for post here) is a java class that needs compiled and then executed. Rather than compiling and executing (and needing to understand much of the build side of java), let’s look at an alternative….

In OWB, there is an interpreter/interactive shell called OMBPlus and language that lets you automate the construction of artifacts and get smart with the tool. With ODI you can leverage the groovy interpreter for example, which lets you interact with java objects. You’ll need to download groovy and install. When groovy is installed you can then either execute the groovy process and process a groovy script as input or use the groovysh shell/interpreter. The groovy scripts can be scripts which use the ODI 11g SDK and interact with ODI.

For example the command below, takes the groovy script interfaceAccelerator.groovy or post here (which is a groovy version of the java class file previously used) as input, and the tabs.txt is piped in the standard input stream.

groovy interfaceAccelerator.groovy < tabs.txt

The groovy script is a mixture of groovy commands and ODI SDK calls to generate the interfaces in ODI.

The CLASSPATH including all the ODI jars is one thing that needs set up prior to executing the groovy script above, I just included all jars in the oracledi.sdk\lib directory.

There was a small change in the transaction handling APIs after production 11g ODI, hence the different versions of the groovy and java scripts.

Lots of possibilities as you can imagine! There are a few cool things we can extend the actual interfaceAceelerator to do...regardless of groovy or java, but just some more useful capabilities, more to come.


ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« July 2016