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 – Expert Accelerator for Model Creation

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have experts for capturing user input, with the groovy console we open up opportunities to build UI around the scripts in a very easy way – even I can do it;-)

After a little googling around I found some useful posts on SwingBuilder, the most useful one that I used for the dialog below was this one here. This dialog captures user input for the technology and context for the model and logical schema etc to be created. You can see there are a variety of interesting controls, and its really easy to do.

The dialog captures the users input, then when OK is pressed I call the functions from the earlier post to create the logical schema (plus all the other objects) and model. The image below shows what was created, you can see the model (with typo in name), the model is Oracle technology and references the logical schema ORACLE_SCOTT (that I named in dialog above), the logical schema is mapped via the GLOBAL context to the data server ORACLE_SCOTT_DEV (that I named in dialog above), and the physical schema used was just the user name that I connected with – so if you wanted a different user the schema name could be added to the dialog.

In a nutshell, one dialog that encapsulates a simpler mechanism for creating a model. You can create your own scripts that use dialogs like this, capture input and process.

You can find the groovy script for this is here odi_create_model.groovy, again I wrapped the user capture code in a groovy function and return the result in a variable and then simply call the createLogicalSchema and createModel functions from the previous posting. The script I supplied above has everything you will need. To execute use Tools->Groovy->Open Script and then execute the green play button on the toolbar.

Have fun.

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!

Friday Feb 24, 2012

ODI 11g - Getting Scripting with Groovy

The addition of the groovy interpreter to the ODI designer now let’s you easily script any tasks that you repeatedly perform. The documentation has illustrations here, so using the ODI 11g SDK you can encapsulate common tasks in simple groovy functions.

Groovy can be executed by executing a script, you can create a new one or open an existing groovy script;

You will then see a new groovy window appear in the IDE plus the execute green button is enabled on the toolbar.

I have taken the script defined here and shown below in its more minimal groovy form and parameterized the script in a groovy function ‘createProject’. I can then call createProject with whatever values for the project and folder I wish to create.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.OdiFolder;

def createProject(projectName, projectCode, folderName) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)
  project = new OdiProject(projectName, projectCode)
  folder = new OdiFolder(project, folderName)

createProject("EDW Staging", "EDW", "Initialization")

So in the UI if I execute as follows;

After executing the script I refresh the Designer tree and see my new project.


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


« April 2014