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!!

 

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

23 Comments

  1. 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.

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

          [code lang=”java”]
          ds_source = dsf.findByName(“EMP”, “MODEL_SRC_STG1”)
          ds_src_comp = new DatastoreComponent(map, ds_source)
          ds_target = dsf.findByName(“EMP”, “MODEL_TRG_STG1 “)
          [/code]

          • 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.
            [code lang=”java”]
            ds_target = dsf.findByName(“EMP”, “THIS_MODEL_DOES_NOT_EXIST”)
            [/code]

  2. 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.

        • pls check following code
          [code lang=”java”]
          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”)
          [/code]

  3. 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.

  4. 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.

  5. 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..

        • Hi Darshan,

          Did you create complete automation?

          I am also planning to do same in ODI 12C automation with a Groovy script so, can you please help me with this same.

  6. Hi Bhabani ,

    While executing this code , i am getting below error . Can you please look into this issue

    Cannot invoke method getColumn() on null object

Leave a Reply

Your email address will not be published.


*