Create Project in ODI12c using Groovy Script

Create Project using ODI12c Groovy Script:

This is a quick post based on the request coming from newbies in OTN. Using this post you will be able to create a project and mapping in ODI 12c. To create a groovy script in ODIStudio, navigate to Tools>Groovy>New Script. You can refer this post written by DavidAllan to get more insight about 12c Scripts.

Make sure you have reversed EMP table under these models.

USERSRC
USERDEST

import oracle.odi.domain.project.OdiProject
import oracle.odi.domain.project.finder.IOdiProjectFinder
import oracle.odi.domain.model.finder.IOdiDataStoreFinder
import oracle.odi.domain.project.finder.IOdiFolderFinder
import oracle.odi.domain.project.finder.IOdiKMFinder
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.adapter.project.IKnowledgeModule.ProcessingType
import oracle.odi.domain.model.OdiDataStore
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition

def setExpr(comp, tgtTable, propertyName, expressionText) {
DatastoreComponent.findAttributeForColumn(comp,tgtTable.getColumn(propertyName)).setExpressionText(expressionText)
}

//CREATE PROJECT STARTS
def createProject(project_name, project_folder_name){
project_code = project_name
txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)

pf = (IOdiProjectFinder)tme.getFinder(OdiProject.class)
ff = (IOdiFolderFinder)tme.getFinder(OdiFolder.class)
project = pf.findByCode(project_name)
if (project != null) {
println "Project Already Exists. Project Creation Skipped"
}
else{
project = new OdiProject(project_name, project_name)
tme.persist(project)
folder = new OdiFolder(project, project_folder_name)
tme.persist(folder)
tm.commit(txnStatus)
println "Project Created Successfully"
}
}
//CREATE PROJECT COMPLETES

//CREATE MAPPING STARTS
def createMapping(project_name,project_folder_name,myMap) {

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

pf = (IOdiProjectFinder)tme.getFinder(OdiProject.class)
ff = (IOdiFolderFinder)tme.getFinder(OdiFolder.class)
project = pf.findByCode(project_name)
folderColl = ff.findByName(project_folder_name, project_name)
OdiFolder folder = null
if (folderColl.size() == 1)
folder = folderColl.iterator().next()

dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)
mapf = (IMappingFinder) tme.getFinder(Mapping.class)

Mapping map = (mapf).findByName(folder, myMap)
if ( map!=null) {
println "Map Already Exists. Map Creation Skipped"
}
else{
map = new Mapping(myMap, folder)
tme.persist(map)

ds_source = dsf.findByName("EMP", "USERSRC")
ds_src_comp = new DatastoreComponent(map, ds_source)
ds_target = dsf.findByName("EMP", "USERDEST")
ds_tgt_comp = new DatastoreComponent(map, ds_target)

ds_src_comp.connectTo(ds_tgt_comp)
setExpr(ds_tgt_comp, ds_target, "EMPNO", "EMP.EMPNO")
setExpr(ds_tgt_comp, ds_target, "ENAME", "EMP.ENAME")
setExpr(ds_tgt_comp, ds_target, "JOB", "EMP.JOB")
setExpr(ds_tgt_comp, ds_target, "MGR", "EMP.MGR")
setExpr(ds_tgt_comp, ds_target, "HIREDATE", "EMP.HIREDATE")
setExpr(ds_tgt_comp, ds_target, "SAL", "EMP.SAL")
setExpr(ds_tgt_comp, ds_target, "COMM", "EMP.COMM")
setExpr(ds_tgt_comp, ds_target, "DEPTNO", "EMP.DEPTNO")

deploymentspec = map.getDeploymentSpec(0)
node = deploymentspec.findNode(ds_tgt_comp)
println deploymentspec.getExecutionUnits()
aps = deploymentspec.getAllAPNodes()
tgts = deploymentspec.getTargetNodes()

ikmf = (IOdiKMFinder)tme.getFinder(OdiIKM.class)
ins_ikm = ikmf.findByName("IKM Oracle Insert");
lkmf = (IOdiKMFinder)tme.getFinder(OdiLKM.class)
sql_lkm = lkmf.findByName("LKM Oracle to Oracle Pull (DB Link)");

api = aps.iterator()
ap_node = api.next()
ap_node.setLKM(sql_lkm)
ap_node.getOptionValue(ProcessingType.TARGET,"ADD_DRIVING_SITE_HINT").setValue("true")

tme.persist(map)
tm.commit(txnStatus)
println "Mapping Created SUccessfully"
}
}
//CREATE MAPPING ENDS

//Call project
createProject("PRO_SCOTT","First Folder")
//Call mapping
createMapping("PRO_SCOTT", "First Folder", "New_Mapping")

ODI12CGROOVY

Let me know if you have any issues while executing the script.

Thanks!!

 

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

21 comments

Hi,

while executing the script i am getting following error..

Project Created Successfully
Cannot invoke method getColumn() on null object
(Subtract 17 from the error line number to account for the standard imports)
java.lang.NullPointerException: Cannot invoke method getColumn() on null object
at org.codehaus.groovy.runtime.NullObject.invokeMethod(NullObject.java:77)
at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.call(PogoMetaClassSite.java:45)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at org.codehaus.groovy.runtime.callsite.NullCallSite.call(NullCallSite.java:32)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
at odi.setExpr(odi.groovy:30)
at odi$setExpr.callCurrent(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:49)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:153)
at odi.createMapping(odi.groovy:82)
at odi$createMapping.callCurrent(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:49)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:149)
at odi.run(odi.groovy:107)
at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:257)
at groovy.lang.GroovyShell.run(GroovyShell.java:481)
at groovy.lang.GroovyShell.run(GroovyShell.java:163)
at oracle.odi.ui.groovy.GroovyScriptRunInstance.run(GroovyScriptRunInstance.java:323)
Script exited.

Please make sure you have created USERSRC, USERDEST model and reversed the EMP datastore under it. In next post I will show, how to create Model and reverse engineer datastores.

Hi,

I have created MODEL_SRC_STG1,MODEL_TRG_STG1 model and EMP datastore can i use this..
?

Yes you can. Just replace the code as given below.

ds_source = dsf.findByName("EMP", "MODEL_SRC_STG1")
ds_src_comp = new DatastoreComponent(map, ds_source)
ds_target = dsf.findByName("EMP", "MODEL_TRG_STG1 ")

i have replced this models in code then i am getting above error already i posted..

please check following code..

import oracle.odi.domain.project.finder.IOdiProjectFinder
import oracle.odi.domain.model.finder.IOdiDataStoreFinder
import oracle.odi.domain.project.finder.IOdiFolderFinder
import oracle.odi.domain.project.finder.IOdiKMFinder
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.adapter.project.IKnowledgeModule.ProcessingType
import oracle.odi.domain.model.OdiDataStore
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition
import java.util.Iterator

def setExpr(comp, tgtTable, propertyName, expressionText) {
DatastoreComponent.findAttributeForColumn(comp,tgtTable.getColumn(propertyName)).setExpressionText(expressionText)
}
//CREATE PROJECT STARTS
def createProject(project_name, project_folder_name){
project_code = project_name
txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)
pf = (IOdiProjectFinder)tme.getFinder(OdiProject.class)
ff = (IOdiFolderFinder)tme.getFinder(OdiFolder.class)
project = pf.findByCode(project_name)
if (project != null) {
println “Project Already Exists. Project Creation Skipped”
}
else{
project = new OdiProject(project_name, project_name)
tme.persist(project)
folder = new OdiFolder(project, project_folder_name)
tme.persist(folder)
tm.commit(txnStatus)
println “Project Created Successfully”
}
}
//CREATE PROJECT COMPLETES
//CREATE MAPPING STARTS
def createMapping(project_name,project_folder_name,myMap) {
txnDef = new DefaultTransactionDefinition()
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)
pf = (IOdiProjectFinder)tme.getFinder(OdiProject.class)
ff = (IOdiFolderFinder)tme.getFinder(OdiFolder.class)
project = pf.findByCode(project_name)
folderColl = ff.findByName(project_folder_name, project_name)
OdiFolder folder = null
if (folderColl.size() == 1)
folder = folderColl.iterator().next()
dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)
mapf = (IMappingFinder) tme.getFinder(Mapping.class)
Mapping map = (mapf).findByName(folder, myMap)
if ( map!=null) {
println “Map Already Exists. Map Creation Skipped”
}
else{
map = new Mapping(myMap, folder)
tme.persist(map)
ds_source = dsf.findByName(“EMP”, “MODEL_SRC_STG1”)
ds_src_comp = new DatastoreComponent(map, ds_source)
ds_target = dsf.findByName(“EMP”, “MODEL_TRG_STG1”)
ds_tgt_comp = new DatastoreComponent(map, ds_target)
ds_src_comp.connectTo(ds_tgt_comp)
setExpr(ds_tgt_comp, ds_target, “EMPNO”, “EMP.EMPNO”)
setExpr(ds_tgt_comp, ds_target, “ENAME”, “EMP.ENAME”)
setExpr(ds_tgt_comp, ds_target, “DEPTNO”, “EMP.DEPTNO”)
deploymentspec = map.getDeploymentSpec(0)
node = deploymentspec.findNode(ds_tgt_comp)
println deploymentspec.getExecutionUnits()
aps = deploymentspec.getAllAPNodes()
tgts = deploymentspec.getTargetNodes()
ikmf = (IOdiKMFinder)tme.getFinder(OdiIKM.class)
ins_ikm = ikmf.findByName(“IKM Oracle Insert”);
lkmf = (IOdiKMFinder)tme.getFinder(OdiLKM.class)
sql_lkm = lkmf.findByName(“LKM Oracle to Oracle Pull (DB Link)”);
api = aps.iterator()
ap_node = api.next()
ap_node.setLKM(sql_lkm)
ap_node.getOptionValue(ProcessingType.TARGET,”ADD_DRIVING_SITE_HINT”).setValue(“true”)
tme.persist(map)
tm.commit(txnStatus)
println “Mapping Created SUccessfully”
}
}
//CREATE MAPPING ENDS
//Call project
createProject(“PRO_SCOTT”,”First Folder”)
//Call mapping
createMapping(“PRO_SCOTT”, “First Folder”, “New_Mapping”)

Hi Ram,
Somehow your target datasource object getting null. I will try to reproduce this error and let you know.
Thanks

update: Can you send me the screenshot of your target model? I am able to reproduce this error by giving an invalid model name. Please check if any typos are there. Remember it is case sensitive.

ds_target = dsf.findByName("EMP", "THIS_MODEL_DOES_NOT_EXIST")

in message log showing like this..

2014-12-01 20:50:50.917 NOTIFICATION New data source: [ODI/*******@jdbc:oracle:thin:@localhost:1521:orcl]
2014-12-01 20:51:01.975 NOTIFICATION Cannot find AgentETLHelper.

Hi Bhabani,

I am getting following error..

Project Created Successfully
[ExecutionUnit ODI_UNIT]
null
(Subtract 17 from the error line number to account for the standard imports)
java.util.NoSuchElementException
at java.util.ArrayList$Itr.next(ArrayList.java:834)
at java_util_Iterator$next$0.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:112)
at odi.createMapping(odi.groovy:94)
at odi$createMapping.callCurrent(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:49)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:149)
at odi.run(odi.groovy:106)
at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:257)
at groovy.lang.GroovyShell.run(GroovyShell.java:481)
at groovy.lang.GroovyShell.run(GroovyShell.java:163)
at oracle.odi.ui.groovy.GroovyScriptRunInstance.run(GroovyScriptRunInstance.java:323)
Script exited.

This error comes when you provide an invalid source model name.

ds_source = dsf.findByName("EMP", "USERSRC")

pls check following code

map = new Mapping(myMap, folder)
tme.persist(map)
ds_source = dsf.findByName("EMP","USERSRC")
ds_src_comp = new DatastoreComponent(map, ds_source)
ds_target = dsf.findByName("EMP","USERDEST")
ds_tgt_comp = new DatastoreComponent(map, ds_target)
ds_src_comp.connectTo(ds_tgt_comp)
setExpr(ds_tgt_comp, ds_target, "EMPNO", "EMP.EMPNO")
setExpr(ds_tgt_comp, ds_target, "ENAME", "EMP.ENAME")
setExpr(ds_tgt_comp, ds_target, "DEPTNO", "EMP.DEPTNO")

Please send a screenshot of your data models and datastores to support@dwteam.in or bhabaniranjan@gmail.com

hi,

i was sent scereenshots of models and datastore to ur mail pls check it..

This looks good to me. Can you execute the script now? Let me know the script you executed and the error details.

Hi Bhabani,
while executing this script again i am getting same error pls check this below code ..

import oracle.odi.domain.project.OdiProject
import oracle.odi.domain.project.finder.IOdiProjectFinder
import oracle.odi.domain.model.finder.IOdiDataStoreFinder
import oracle.odi.domain.project.finder.IOdiFolderFinder
import oracle.odi.domain.project.finder.IOdiKMFinder
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.adapter.project.IKnowledgeModule.ProcessingType
import oracle.odi.domain.model.OdiDataStore
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition
def setExpr(comp, tgtTable, propertyName, expressionText) {
DatastoreComponent.findAttributeForColumn(comp,tgtTable.getColumn(propertyName)).setExpressionText(expressionText)
}
//CREATE PROJECT STARTS
def createProject(project_name, project_folder_name){
project_code = project_name
txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)
pf = (IOdiProjectFinder)tme.getFinder(OdiProject.class)
ff = (IOdiFolderFinder)tme.getFinder(OdiFolder.class)
project = pf.findByCode(project_name)
if (project != null) {
println “Project Already Exists. Project Creation Skipped”
}
else{
project = new OdiProject(project_name, project_name)
tme.persist(project)
folder = new OdiFolder(project, project_folder_name)
tme.persist(folder)
tm.commit(txnStatus)
println “Project Created Successfully”
}
}
//CREATE PROJECT COMPLETES
//CREATE MAPPING STARTS
def createMapping(project_name,project_folder_name,myMap) {
txnDef = new DefaultTransactionDefinition()
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)
pf = (IOdiProjectFinder)tme.getFinder(OdiProject.class)
ff = (IOdiFolderFinder)tme.getFinder(OdiFolder.class)
project = pf.findByCode(project_name)
folderColl = ff.findByName(project_folder_name, project_name)
OdiFolder folder = null
if (folderColl.size() == 1)
folder = folderColl.iterator().next()
dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)
mapf = (IMappingFinder) tme.getFinder(Mapping.class)
Mapping map = (mapf).findByName(folder, myMap)
if ( map!=null) {
println “Map Already Exists. Map Creation Skipped”
}
else{
map = new Mapping(myMap, folder)
tme.persist(map)
ds_source = dsf.findByName(“EMP”, “USERSRC”)
ds_src_comp = new DatastoreComponent(map, ds_source)
ds_target = dsf.findByName(“EMP”, “USERDEST”)
ds_tgt_comp = new DatastoreComponent(map, ds_target)
ds_src_comp.connectTo(ds_tgt_comp)
setExpr(ds_tgt_comp, ds_target, “EMPNO”, “EMP.EMPNO”)
setExpr(ds_tgt_comp, ds_target, “ENAME”, “EMP.ENAME”)
setExpr(ds_tgt_comp, ds_target, “DEPTNO”, “EMP.DEPTNO”)
deploymentspec = map.getDeploymentSpec(0)
node = deploymentspec.findNode(ds_tgt_comp)
println deploymentspec.getExecutionUnits()
aps = deploymentspec.getAllAPNodes()
tgts = deploymentspec.getTargetNodes()
ikmf = (IOdiKMFinder)tme.getFinder(OdiIKM.class)
ins_ikm = ikmf.findByName(“IKM Oracle Insert”);
lkmf = (IOdiKMFinder)tme.getFinder(OdiLKM.class)
sql_lkm = lkmf.findByName(“LKM Oracle to Oracle Pull (DB Link)”);
api = aps.iterator()
ap_node = api.next()
ap_node.setLKM(sql_lkm)
ap_node.getOptionValue(ProcessingType.TARGET,”ADD_DRIVING_SITE_HINT”).setValue(“true”)
tme.persist(map)
tm.commit(txnStatus)
println “Mapping Created SUccessfully”
}
}
//CREATE MAPPING ENDS
//Call project
createProject(“PRO_SCOTT”,”First Folder”)
//Call mapping
createMapping(“PRO_SCOTT”, “First Folder”, “New_Mapping”)

and

i am getting below error..

Project Created Successfully
[ExecutionUnit ODI_UNIT]
null
(Subtract 17 from the error line number to account for the standard imports)
java.util.NoSuchElementException
at java.util.ArrayList$Itr.next(ArrayList.java:834)
at java_util_Iterator$next.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:112)
at odi.createMapping(odi.groovy:94)
at odi$createMapping.callCurrent(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:49)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:149)
at odi.run(odi.groovy:106)
at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:257)
at groovy.lang.GroovyShell.run(GroovyShell.java:481)
at groovy.lang.GroovyShell.run(GroovyShell.java:163)
at oracle.odi.ui.groovy.GroovyScriptRunInstance.run(GroovyScriptRunInstance.java:323)
Script exited.

It comes when the mentioned data store in the script is not really present under the corresponding model.

hi bhabani,

can you explain why data store is not present under the corresponding models.

Hi Bhabani,

I wanted to run the script by having the source and target details from a excel sheet for ODI 12.1.3. Version.Could you please share if you have any link for it to learn and do the same.

Thanks,
KV.

Hi Bhabani,
How to do reverse engineering for odi using groovy scripts(or java). What is the code?Till now i have written code for creating project,physical scheme,logical schema,context and model but i am stuck at reverse engineering.

Hi bhabani,
Thank u for the code..
The above code creates a model folder and masks the name ..

But i have created a tables in SQL developer and i need to get tables sql developer to odi..

I used u r above code and created models and if i right click on models then only revese engineering is happening in odi. can we write a code to happen reverse engineering automatically without clicking reverse enginering in odi.??

the attachment of word file u have given is prefect but there creating the table in code only(means creation of table is done eclipse–this is not good approach because input table can be anything we can’t change the code of creating table each time) but can u suggest me how to approach if the tables are in sql developer and do reverse engineering …

I am doing complete automation of odi a user should not do anything in odi when he runs my java code and if he opens odi and refresh it mapping should be done in odi from source to table that is my idea .. so help me out with this..

Gaurav Saraogi / Reply

Could anyone please provide a script for reverse engineering a data store via groovy ?

Leave a reply

required*

Are you a human? *