Create Interfaces from Excel Mapping Sheets in ODI

Create Interfaces from Excel Mapping Sheets in ODI:

Hey Guys!!

Many of you have requested to provide Groovy codes that will create interfaces by reading mapping information from Excel sheet. Though I have done 95% of homework, I have left rest of work for you to customize as per your need. Check out the post by Julian to know more about building automation scripts. Remember one thing about automation scripts, though it takes few second to create multiple objects, it might be dangerous if you do not understand the effect of your code. These codes must be passed through all the test cases before applying in your development environment. It gives lots of pain when it affects hundreds of artifacts and undoing the changes. Nevertheless sometimes it is convenient to create these scripts as it saves lot of your time in building interfaces, models etc. Business Analysts from different companies follows standard mapping sheet for ODI Development. It would be productive if you can utilize those information to create models, data stores, interfaces, scenarios, projects etc. Here are some major issues that I have faced so far.

1. If you have more than 500 attributes required mapping in target data store, it would be really a tedious task for you as you have to drag each fields and adding expression to individuals.

2. If you have to build 100 interfaces, it is annoying to build one by one.

3. Creating hundreds of models and data store one after other takes lots of time.

If I have thousands of mappings and only few of them needs some changes to add expressions like cases or decodes etc, I would love to create them in excel using formulas and concatenate them to generate the final mappings. It would take 80% less time than doing in ODI Studio. The excel sheet that I am going to show has just two or three mapping as this article represents just a small sample. In my file I have 4 sheets and i expect 4 interface of Table to File, File to File, File to table and Table to Table. Here is the sheet info.

For “source” and “target” second column takes model name and third column takes data store name.

excelsheeets1

Now I will execute scripts in ODI Studio which will create 4 interfaces for me as per the mapping sheet. It will include the lookups, joins, filters, mappings. Also it will set your staging area to different logical schema in case it is different from target.

excelsheeets2

excelsheeets3

excelsheeets4

excelsheeets5

excelsheeets6

Here is the code. Don’t forget to download jxl jar and keep it in odi local client directory.

ODI Version: 11.1.1.7


/*The first row of excel sheet contains info about Project Code, Folder name inside project, Interface name, Context code, Is Staging Different from Target (Y/N), Staging Logical Schema, Interface Type(FF/FT/TT/TF).
This code expects the model,datastore,project,context,logical schema are already exist.
Author: Bhabani Mahapatra
Mail: support@dwteam.in
*/
import oracle.odi.core.OdiInstance
import oracle.odi.core.config.OdiInstanceConfig
import oracle.odi.core.config.MasterRepositoryDbInfo
import oracle.odi.core.config.WorkRepositoryDbInfo
import oracle.odi.core.security.Authentication
import oracle.odi.core.config.PoolingAttributes

import oracle.odi.domain.project.OdiProject
import oracle.odi.domain.project.finder.IOdiProjectFinder
import oracle.odi.domain.project.finder.IOdiFolderFinder
import oracle.odi.domain.model.finder.IOdiDataStoreFinder
import oracle.odi.domain.topology.finder.IOdiContextFinder
import oracle.odi.domain.project.OdiFolder
import oracle.odi.domain.project.OdiInterface
import oracle.odi.domain.project.interfaces.DataSet
import oracle.odi.domain.model.OdiDataStore
import oracle.odi.domain.topology.OdiContext
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterDefault
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerColumnName
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyColumnName
import oracle.odi.interfaces.interactive.support.targetkeychoosers.TargetKeyChooserPrimaryKey
import oracle.odi.core.persistence.transaction.ITransactionStatus
import oracle.odi.core.persistence.transaction.ITransactionManager
import oracle.odi.core.persistence.transaction.ITransactionDefinition
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition
import oracle.odi.interfaces.TargetIsTemporaryException
import oracle.odi.interfaces.basic.NoSourceSetException
import oracle.odi.interfaces.basic.support.BasicInterfaceCreationHelperWithActionsHelper
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetDataStoreComputeAutoMapping
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddLookup
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddJoin
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddFilter
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetMappingSetSql
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetTargetDataStore
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerDoubleChecker
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterLazy
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerLazy
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyLazy
import oracle.odi.domain.topology.finder.IOdiLogicalSchemaFinder
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnStagingAreaSetLogicalSchema
import oracle.odi.domain.project.finder.IOdiLKMFinder
import oracle.odi.domain.project.finder.IOdiCKMFinder
import oracle.odi.domain.project.finder.IOdiIKMFinder
import oracle.odi.domain.project.OdiLKM
import oracle.odi.domain.project.OdiCKM
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerHomonymy
import oracle.odi.domain.topology.OdiLogicalSchema
import oracle.odi.interfaces.basic.MappingDefinition
import oracle.odi.impexp.support.ImportServiceImpl
import oracle.odi.impexp.IImportService
import oracle.odi.domain.project.OdiIKM
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM.KMType
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKMOptionValue
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy
import oracle.odi.domain.project.interfaces.SourceDataStore
import oracle.odi.domain.project.interfaces.SourceSet
import java.util.Collection
import java.io.*
import jxl.*
import jxl.write.*

// Repository credentials
url = "jdbc:oracle:thin:@localhost:1522:orcl11g"
driver = "oracle.jdbc.OracleDriver"
schema = "ODIMASTER11G"
schemapwd = "odi"
workrep = "WORKREP406"
odiuser= "SUPERVISOR"
odiuserpwd = "SUNOPSIS"
projectcode = "TRAINING_BATCH1"
foldername = "LEVEL 1" //This project folder not model
// Common variables
contextcode = null
stagingLogicalSchemaName=null
isStagingDifferent=null
iname = null
interfaceType =null
OdiContext context=null
OdiInterface odiInterface=null
InteractiveInterfaceHelperWithActions interactiveHelper=null
OdiProject sdkProject=null
Collection<OdiFolder> odiFolders=null
OdiFolder odiFolder=null
SourceSet srcset= null
//For IKM array follow this order to set values if you have multiple sheets. Index 0 for file type km and index 1 for table type km
ikmNameTarget=["IKM SQL to File Append","IKM SQL Control Append"]
ckmName="CKM Oracle"
lkmName="LKM SQL to SQL"
filepath="C:/Bhabani/control/interface.xls"
kmInstallFolder = "C:/Oracle/Middleware/Oracle_BI1/Oracle_ODI1/oracledi/xml-reference/"

try {
Workbook workbook = Workbook.getWorkbook(new File(filepath))
String [] sheetNames = workbook.getSheetNames()
Sheet sheet=null

//Get all sheets and loop in them
try {
for (int sheetNumber =0; sheetNumber < sheetNames.length; sheetNumber++){
sheet=workbook.getSheet(sheetNames[sheetNumber])
int rows=sheet.getRows()
print "Processing "+sheet.getName()+".....\n"

MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(url, driver, schema, schemapwd.toCharArray(), new PoolingAttributes())
WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo(workrep, new PoolingAttributes())
OdiInstance odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo))
Authentication auth = odiInstance.getSecurityManager().createAuthentication(odiuser, odiuserpwd.toCharArray())
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth)
ITransactionDefinition txnDef = new DefaultTransactionDefinition()
ITransactionManager tm = odiInstance.getTransactionManager()
ITransactionStatus txnStatus = tm.getTransaction(txnDef)
IImportService impService = new ImportServiceImpl(odiInstance)

projectcode=sheet.getCell(0,0).getContents()
foldername=sheet.getCell(1,0).getContents()
iname=sheet.getCell(2,0).getContents()
contextcode=sheet.getCell(3,0).getContents()
isStagingDifferent=sheet.getCell(4,0).getContents()
stagingLogicalSchemaName=sheet.getCell(5,0).getContents()
interfaceType=sheet.getCell(6,0).getContents()

odiFolders = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(foldername,projectcode)
sdkProject = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)).findByCode(projectcode)
Collection<OdiIKM> odiIKM_1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[0],projectcode)
Collection<OdiIKM> odiIKM_2 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[1],projectcode)
Collection<OdiCKM> odiCKM = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(ckmName,projectcode)
Collection<OdiLKM> odiLKM = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).findByName(lkmName,projectcode)
if (odiIKM_1.size()==0){
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+ikmNameTarget[0]+".xml", sdkProject, false)
}
if (odiIKM_2.size()==0){
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+ikmNameTarget[1]+".xml", sdkProject, false)
}
if (odiCKM.size()==0)
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+ckmName+".xml", sdkProject, false)
if (odiLKM.size()==0)
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+lkmName+".xml", sdkProject, false)

if (odiFolders.size() == 0) {
System.err.println("Error: cannot find folder "+foldername+" in project "+projectcode)
}
odiFolder = (OdiFolder) (odiFolders.toArray()[0])
context = ((IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findByCode(contextcode)
oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiLogicalSchema.class)).findByName(stagingLogicalSchemaName)
odiInterface = new OdiInterface(odiFolder, iname, context)
interactiveHelper = new InteractiveInterfaceHelperWithActions(odiInterface, odiInstance, odiInstance.getTransactionalEntityManager())
DataSet dataSet = odiInterface.getDataSets().get(0)

//Get all rows present in one sheet and loop in them
for (int i =1; i < rows; i++){
Cell getval=sheet.getCell(0,i)

//Add sources
if(getval.getContents()=="source"){
Cell srcmodel=sheet.getCell(1,i)
Cell srctab = sheet.getCell(2,i)
OdiDataStore odiDatastore1 = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(srctab.getContents(), srcmodel.getContents())
interactiveHelper.performAction(new InterfaceActionAddSourceDataStore(odiDatastore1, dataSet, new AliasComputerDoubleChecker(), new ClauseImporterLazy(), new AutoMappingComputerLazy()))
}
//Add single target
if(getval.getContents()=="target"){
Cell tgtmodel=sheet.getCell(1,i)
Cell tgttab = sheet.getCell(2,i)
OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(tgttab.getContents(), tgtmodel.getContents())
interactiveHelper.performAction(new InterfaceActionSetTargetDataStore(targetDatastore,
new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()))
}
//Add join
if (getval.getContents()=="join") {
Cell join_exp=sheet.getCell(1,i);
interactiveHelper.performAction(new InterfaceActionAddJoin(dataSet, join_exp.getContents(), OdiInterface.ExecutionLocation.SOURCE))
}
//Add filter
if (getval.getContents()=="filter") {
Cell filter_exp=sheet.getCell(1,i)
interactiveHelper.performAction(new InterfaceActionAddFilter(dataSet, filter_exp.getContents(), OdiInterface.ExecutionLocation.SOURCE))
}
//Add lookup
if (getval.getContents()=="lookup") {
Cell src_model=sheet.getCell(1,i)
Cell src_tab=sheet.getCell(2,i)
Cell alias=sheet.getCell(3,i)
Cell lookup_exp=sheet.getCell(4,i)
OdiDataStore lkpDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(src_tab.getContents(), src_model.getContents())
interactiveHelper.performAction(new InterfaceActionAddLookup(dataSet, lkpDatastore, alias.getContents(), lookup_exp.getContents(), OdiInterface.ExecutionLocation.SOURCE, false))
}
//Add expressions in mapping
if (getval.getContents()=="mapping") {
Cell col=sheet.getCell(1,i)
Cell exp=sheet.getCell(2,i)
interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql(col.getContents(), exp.getContents(), dataSet))
}
}

//Set staging area different if the flag is Y and if the logical schema is not null and if the schema actually exist in repository
if (isStagingDifferent == "Y" || interfaceType == "FF"){
if (stagingLogicalSchemaName != null){
if (oracleLogicalSchema != null){
odiInterface.stagingArea.setOnTarget(false)
odiInterface.stagingArea.setLogicalSchema(oracleLogicalSchema)
}
}
}
//Set IKM1
if(interfaceType != null && (interfaceType=="TF" || interfaceType=="FF")){
for (Collection<OdiIKM> odiIKM1 : ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[0],projectcode))
{
interactiveHelper.performAction(new InterfaceActionSetKM(odiIKM1, odiInterface.getTargetDataStore(), KMType.IKM, new KMOptionRetainerLazy()))
interactiveHelper.performAction(new InterfaceActionSetKMOptionValue(odiInterface.getTargetDataStore(), KMType.IKM, "TRUNCATE", true))
}
}
//Set IKM2
if(interfaceType != null && (interfaceType=="TT" || interfaceType=="FT")){
for (Collection<OdiIKM> odiIKM2 : ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[1],projectcode))
{
interactiveHelper.performAction(new InterfaceActionSetKM(odiIKM2, odiInterface.getTargetDataStore(), KMType.IKM, new KMOptionRetainerLazy()))
interactiveHelper.performAction(new InterfaceActionSetKMOptionValue(odiInterface.getTargetDataStore(), KMType.IKM, "TRUNCATE", true))
}
}
//Set CKM
if(interfaceType != null && (interfaceType=="TT" || interfaceType=="FT")){
for (Collection<OdiCKM> odiCKM1 : ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(ckmName,projectcode))
{
interactiveHelper.performAction(new InterfaceActionSetKM(odiCKM1, odiInterface.getTargetDataStore(), KMType.CKM, new KMOptionRetainerLazy()))
}
}

try {
interactiveHelper.computeSourceSets()
}
catch (Exception e) {
println "Exception at first catch: "+e
}
try {
interactiveHelper.preparePersist()
}
catch (Exception e) {
println "Exception at second catch: "+e
}
odiInstance.getTransactionalEntityManager().persist(odiInterface)
tm.commit(txnStatus);
println "Interface successfully created for "+sheet.getName()+"\n"
}

} catch (Exception e) {
println "Exception at third catch: "+e
}
} catch (Exception e) {
println "Exception at final catch: "+e
}

Thats it. Let me know if you face any issues. For creating projects and model see the link I have shared in the beginning of this post. As always dont forget to provide your suggestions and comments for further improvement.

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.

27 Comments

  1. Hi Bhabani,

    I was looking the same groovy script for updating the behavioral columns, tried couple of options and ended up changing the SNP_COLUMN.

    Can you please help with groovy script for behavioral column update.

    Thanks,
    Niharikaa

  2. interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql(col.getContents(), exp.getContents(), dataSet))

    Regarding the above line. It doesn’t apply the mapping expression in the script I’m using. Can you help me with this? Here is my script without the jxl parts.

    helper.performAction(new InterfaceActionOnTargetMappingSetSql(“TAB_COUNT”, “COUNT(1)”, dataSet))

  3. Hi Bhabani

    Regarding the script:

    interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql(col.getContents(), exp.getContents(), dataSet))
    Regarding the above line. It doesn’t apply the mapping expression in the script I’m using. Can you help me with this? Here is my script without the jxl parts.
    helper.performAction(new InterfaceActionOnTargetMappingSetSql(“TAB_COUNT”, “COUNT(1)”, dataSet))

    Thanks,
    Anthony

    • Hi Anthony,
      I did a check by setting to COUNT(1) and could able to see the mapping in the expression. In this case the “execute on” will be automatically set to target.

      It seems like there are some issue with your code. Can you send your artifacts (excel mapping file, groovy script, source table script and target table script) to my mail id?

  4. <>

    Dear Bhabani,

    Could you please let me know the exact location?
    Because I have put the jxl.jar in below location:-
    C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client
    And getting the below error-
    startup failed:
    odi.groovy: 93: unable to resolve class Workbook
    @ line 93, column 10.
    Workbook workbook = Workbook.getWorkbook(new File(filepath))
    ^

    odi.groovy: 95: unable to resolve class Sheet
    @ line 95, column 7.
    Sheet sheet=null
    ^
    etc etc
    Please help.

  5. Hi
    Can u please provide code to add custom columns in temporary target and set its data type.
    also can u explain the implementation of the constructor with example.
    InterfaceActionOnTargetColumnSetDataType(java.lang.String pColumnName, OdiDataType pOdiDataType)
    Thanking you
    SALMAN SHARIFF

  6. Hi Bhabani,

    I’m getting the below error of ArrayOutOfBoundsException. Please let me know if you have faced similar kind of issue in 3rd catch block.

    Thanks,
    Agni

  7. Hi Bhabani,

    Could you please confirm only the jxl jar is the prerequisite to try this code.I have odi installed in my machine.

    Thanks,
    kalai.

      • Hi Bhabani,

        However I am still getting the same error.
        “Unable to resolve class workbook”

        Code which I am trying:

        import java.util.Date;

        import java.io.File;

        import java.util.*;

        import java.lang.*;

        def project_name=”GRO_PROJECT2″
        def project_folder_name=”NEW_FOLDER1″
        File file=new File(“D:/Kalaivani/interfaces.xls”)
        def s=0

        println “Mapping Created SUccessfully”+project+”with the folder”+folder
        println “filepath is ” +file + ” verified”

        Workbook workbook = new Workbook();

        String fileName = workbook.getWorkbook(new File(file))
        String [] sheetNames = workbook.getSheetNames()
        Sheet sheet=null
        println “Sheetname is ” +sheetNames

  8. Hi Bhabani, really appreciate you having shared this information.

    we tried using it and got this compilation error for the line (Collection<OdiFolder>odiFolders=null), if we comment the line then this error does not happen, but since it is being used in the rest of the code we cant do that isn’t it?

    No such property: lt for class: di1
    (Subtract 18 from the error line number to account for the standard imports)
    groovy.lang.MissingPropertyException: No such property: lt for class: di1
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:53)
    at org.codehaus.groovy.runtime.callsite.PogoGetPropertySite.getProperty(PogoGetPropertySite.java:52)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGroovyObjectGetProperty(AbstractCallSite.java:307)
    at di1.run(di1.groovy:113)
    at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:263)
    at groovy.lang.GroovyShell.run(GroovyShell.java:518)
    at groovy.lang.GroovyShell.run(GroovyShell.java:497)
    at groovy.lang.GroovyShell.run(GroovyShell.java:170)
    at oracle.di.studio.groovy.GroovyScriptRunInstance.run(GroovyScriptRunInstance.java:222)
    Script exited.

  9. Hi,

    when I execute the above code only for table to table using below one
    //For IKM array follow this order to set values if you have multiple sheets. Index 0 for file type km and index 1 for table type km
    ikmNameTarget=[“IKM SQL Control Append”]
    //ckmName=”CKM Oracle”
    //lkmName=”LKM SQL to SQL”
    filepath=”C:/Users/VASANTH/Desktop/Input_data/Info.xlsx”
    kmInstallFolder = “C:/Oracle/product/11.1.1/Oracle_ODI_1/oracledi/xml-reference”

    ran into below issue
    startup failed:
    odi.groovy: 211: expecting ‘)’, found ‘;’ @ line 211, column 30.
    if(interfaceType != null && (interfaceType==”TF” || interfaceType==”FF”)){
    ^

    1 error

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
    odi.groovy: 211: expecting ‘)’, found ‘;’ @ line 211, column 30.
    if(interfaceType != null && (interfaceType==”TF” || interfaceType==”FF”)){
    ^

    1 error

    below is my only one sheet used:
    SAMPLE First Folder INF_TRG_EMP GLOBAL N TRG_SCOTT_LS TT
    source SRC_SCOTT EMP
    source SRC_SCOTT DEPT
    target TRG_SCOTT TRG_EMP
    mapping JOB UPPER(DEPT.DNAME)
    mapping DEPTNO ABS(EMP.EMPNO)
    join EMP.DEPTNO=DEPT.DEPTNO
    filter EMP.SAL>1
    lookup SRC_SCOTT BONUS BONUS BONUS.ENAME=EMP.ENAME

  10. Hi,

    I was trying to implement this and getting below error:

    groovy.lang.MissingPropertyException: No such property: lt for class: int
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:50)
    at org.codehaus.groovy.runtime.callsite.PogoGetPropertySite.getProperty(PogoGetPropertySite.java:49)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGroovyObjectGetProperty(AbstractCallSite.java:231)
    at int.run(int.groovy:89)
    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:228)
    Script exited.

    Please help.

  11. Hi Bhabani,

    ODI Version : 12.2.1.
    Placed the jxl.jar file in the C:\Users\AppData\Roaming\odi\oracledi\userlib, while trying to implement the code getting the below error:

    startup failed:
    di.groovy: 114: unable to resolve class Workbook
    @ line 114, column 10.
    Workbook workbook = Workbook.getWorkbook(new File(filepath))
    ^

    di.groovy: 116: unable to resolve class Sheet
    @ line 116, column 7.
    Sheet sheet=null
    ^

    di.groovy: 172: unable to resolve class Cell
    @ line 172, column 6.
    Cell getval=sheet.getCell(0,i)
    ^

    di.groovy: 176: unable to resolve class Cell
    @ line 176, column 6.
    Cell srcmodel=sheet.getCell(1,i)
    ^

    di.groovy: 177: unable to resolve class Cell
    @ line 177, column 6.
    Cell srctab = sheet.getCell(2,i)
    ^

    di.groovy: 183: unable to resolve class Cell
    @ line 183, column 6.
    Cell tgtmodel=sheet.getCell(1,i)
    ^

    di.groovy: 184: unable to resolve class Cell
    @ line 184, column 6.
    Cell tgttab = sheet.getCell(2,i)
    ^

    di.groovy: 194: unable to resolve class Cell
    @ line 194, column 6.
    Cell join_exp=sheet.getCell(1,i);
    ^

    di.groovy: 199: unable to resolve class Cell
    @ line 199, column 6.
    Cell filter_exp=sheet.getCell(1,i)
    ^

    di.groovy: 204: unable to resolve class Cell
    @ line 204, column 6.
    Cell src_model=sheet.getCell(1,i)
    ^

    di.groovy: 205: unable to resolve class Cell
    @ line 205, column 6.
    Cell src_tab=sheet.getCell(2,i)
    ^

    di.groovy: 206: unable to resolve class Cell
    @ line 206, column 6.
    Cell alias=sheet.getCell(3,i)
    ^

    di.groovy: 207: unable to resolve class Cell
    @ line 207, column 6.
    Cell lookup_exp=sheet.getCell(4,i)
    ^

    di.groovy: 213: unable to resolve class Cell
    @ line 213, column 6.
    Cell col=sheet.getCell(1,i)
    ^

    di.groovy: 214: unable to resolve class Cell
    @ line 214, column 6.
    Cell exp=sheet.getCell(2,i)
    ^

    15 errors

    (Subtract 18 from the error line number to account for the standard imports)
    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
    di.groovy: 114: unable to resolve class Workbook
    @ line 114, column 10.
    Workbook workbook = Workbook.getWorkbook(new File(filepath))
    ^

    di.groovy: 116: unable to resolve class Sheet
    @ line 116, column 7.
    Sheet sheet=null
    ^

    di.groovy: 172: unable to resolve class Cell
    @ line 172, column 6.
    Cell getval=sheet.getCell(0,i)
    ^

    di.groovy: 176: unable to resolve class Cell
    @ line 176, column 6.
    Cell srcmodel=sheet.getCell(1,i)
    ^

    di.groovy: 177: unable to resolve class Cell
    @ line 177, column 6.
    Cell srctab = sheet.getCell(2,i)
    ^

    di.groovy: 183: unable to resolve class Cell
    @ line 183, column 6.
    Cell tgtmodel=sheet.getCell(1,i)
    ^

    di.groovy: 184: unable to resolve class Cell
    @ line 184, column 6.
    Cell tgttab = sheet.getCell(2,i)
    ^

    di.groovy: 194: unable to resolve class Cell
    @ line 194, column 6.
    Cell join_exp=sheet.getCell(1,i);
    ^

    di.groovy: 199: unable to resolve class Cell
    @ line 199, column 6.
    Cell filter_exp=sheet.getCell(1,i)
    ^

    di.groovy: 204: unable to resolve class Cell
    @ line 204, column 6.
    Cell src_model=sheet.getCell(1,i)
    ^

    di.groovy: 205: unable to resolve class Cell
    @ line 205, column 6.
    Cell src_tab=sheet.getCell(2,i)
    ^

    di.groovy: 206: unable to resolve class Cell
    @ line 206, column 6.
    Cell alias=sheet.getCell(3,i)
    ^

    di.groovy: 207: unable to resolve class Cell
    @ line 207, column 6.
    Cell lookup_exp=sheet.getCell(4,i)
    ^

    di.groovy: 213: unable to resolve class Cell
    @ line 213, column 6.
    Cell col=sheet.getCell(1,i)
    ^

    di.groovy: 214: unable to resolve class Cell
    @ line 214, column 6.
    Cell exp=sheet.getCell(2,i)
    ^

    15 errors

    at org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector.java:310)
    at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:958)
    at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:605)
    at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:554)
    at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298)
    at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:268)
    at groovy.lang.GroovyShell.parseClass(GroovyShell.java:688)
    at groovy.lang.GroovyShell.run(GroovyShell.java:517)
    at groovy.lang.GroovyShell.run(GroovyShell.java:497)
    at groovy.lang.GroovyShell.run(GroovyShell.java:170)
    at oracle.di.studio.groovy.GroovyScriptRunInstance.run(GroovyScriptRunInstance.java:222)
    Script exited.

Leave a Reply

Your email address will not be published.


*