automated mapping in groovy for ODI with expression

Question

this is my first question and I hope you can help me. I make a script in Groovy (in Oracle Data Integrator 12c) to automate mappings. Here is the description of my prodecure: 1 step: removing old mapping if exists. 2 step: looking for the project and the folder (if doesn't exist: create new one). 3 step: create new mapping 4 step: implement source and target table 5 step: create expression 6 step: link every column

Now my question: Can someone help me to make this script with a dynamic expression? Like this: step 1: get the data types of the target columns step 2: get the right data types into the expression step 3: change the false types (always Varchar) into the right types (Number or Date or still Varchar) step 4: link every column

My handicap: I have never done something with groovy and in Java I'm not very good. So it is not possible for me to make this dynamic. Almost everything in my Script is placed together from some internet sites. It would be great to find some guys who know something about my problem. And I think it would be a good script for all who will change from OWB to ODI.

Thanks!

//Von ODI Studio erstellt
//
//name of the project
projectName = "SRC_TO_TRG"
//name of the folder
ordnerName = "FEN_TEST"
//name of the mapping
mappingName = "MAP1_FF_TO_TRG"
//name of the model
modelName = "DB_FEN"
//name of the source datastore
sourceDatastoreName = "SRC_TEST_FEN"
//name of the target datastore
targetDatastoreName = "TRG_TEST_FEN"

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


//set expression to the component
def createExp(comp, tgtTable, propertyName, expressionText) { 
  DatastoreComponent.findAttributeForColumn(comp,tgtTable.getColumn(propertyName))    .setExpressionText(expressionText)
}

//delete mapping with the same name
def removeMapping(folder, map_name) {
  txnDef = new DefaultTransactionDefinition()
  tm = odiInstance.getTransactionManager()
  tme = odiInstance.getTransactionalEntityManager()
  txnStatus = tm.getTransaction(txnDef)
  try {
    Mapping map = ((IMappingFinder)     tme.getFinder(Mapping.class)).findByName(folder, map_name)
    if (map != null) {
      odiInstance.getTransactionalEntityManager().remove(map);
    }
  } catch (Exception e) {e.printStackTrace();}
  tm.commit(txnStatus)
}

//looking for a project and folder
def find_folder(project_code, folder_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_code)

//if there is no project, create new one
  if (project == null) {
     project = new OdiProject(project_code, project_code) 
     tme.persist(project)
  }
//if there is no folder, create new one
  folderColl = ff.findByName(folder_name, project_code)
  OdiFolder folder = null
  if (folderColl.size() == 1)
    folder = folderColl.iterator().next()
  if (folder == null) {
     folder = new OdiFolder(project, folder_name) 
     tme.persist(folder)
  }
  tm.commit(txnStatus)
  return folder
}

//name of the project and the folder
  folder = find_folder(projectName,ordnerName)
//delete old mapping
  removeMapping(folder, mappingName)

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

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

//create new mapping
  map = new Mapping(mappingName, folder);
  tme.persist(map)

//insert source table
  boundTo_emp = dsf.findByName(sourceDatastoreName, modelName)
  comp_emp = new DatastoreComponent(map, boundTo_emp)

 //insert target table
  boundTo_tgtemp = dsf.findByName(targetDatastoreName, modelName)
  comp_tgtemp = new DatastoreComponent(map, boundTo_tgtemp)

 //create expression-operator  
  comp_expression = new ExpressionComponent(map, "EXPRESSION")

// define expression
  comp_expression.addExpression("LAND_KM",     "TO_NUMBER(SRC_TEST_FEN.LAND_KM)", null,null,null);
  comp_expression.addExpression("DATE_OF_ELECTION",     "TO_DATE(SRC_TEST_FEN.DATE_OF_ELECTION, 'DD.MM.YYYY')", null,null,null);
//weitere Transformationen anhängen möglich   

//link source table with expression
  comp_emp.connectTo(comp_expression)

//link expression with target table
  comp_expression.connectTo(comp_tgtemp)

  createExp(comp_tgtemp, boundTo_tgtemp, "ABBR", "SRC_TEST_FEN.ABBR")
  createExp(comp_tgtemp, boundTo_tgtemp, "NAME", "SRC_TEST_FEN.NAME")
  createExp(comp_tgtemp, boundTo_tgtemp, "LAND_KM", "EXPRESSION.LAND_KM")
  createExp(comp_tgtemp, boundTo_tgtemp, "DATE_OF_ELECTION",     "EXPRESSION.DATE_OF_ELECTION")

  tme.persist(map)
  tm.commit(txnStatus)

Answer 1

You can pass the Datatype as the third argument of the method addExpression. You can also pass the size and the scale as fourth and fifth arguments.

For instance, for the LAND_KM expression, replace your line by this :

MapAttribute map_attr = DatastoreComponent.findAttributeForColumn(comp_tgtemp,boundTo_tgtemp.getColumn("LAND_KM"))
comp_expression.addExpression("LAND_KM", "TO_NUMBER(SRC_TEST_FEN.LAND_KM)", map_attr.getDataType(),map_attr.getSize(),map_attr.getScale());

It retrieves the target column for LAND_KM thanks to findAttributeForColumn, then retrieves the datatype, the size and the scale, and use that when adding the new expression in the Expression component.

If you want to auto map it based on the name, David Allan wrote a post on the official Oracle blog about how to do it and he provides his code : https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_sdk_auto