1. Hi there. I came across your site and firstly just wanted to say a big thank you to the tutorials and information you have posted. It is indeed very helpful and clears up alot of the grey areas surrounding the tool. I wanted to send you this email in regard to how the whole export/import of scenarios work. Basically what I’m trying to understand is, say in development, we have created a set of interfaces, put them in a package and generated a scenario for this package. When this scenario is exported and imported (via import replace) to the QA repository, it can be executed and it works fine but since none of the interfaces were exported/imported, how does the scenario actually execute? Where does it get the information to do the execution if the interfaces themselves don’t exist? Let me know if it’s not clear enough and I can elaborate. Thanks!

    • Hi Mayank thanks for visiting my blog.
      Generally scenario code (the language generated) is frozen, and all subsequent modifications of the components which contributed to creating it will not change it in any way.its like complied code for all objects in to one file. So instead of running all objects one by one you can run the scenario which contains codes for all. This is safe because no one is going to modify the used code inside scenario.
      You can take a small example of OWB projects/maps. You are developing maps having spliter,cube etc etc. But at the end it generates one package in backend. If you run that package then you will get the same thing like you run inside OWB. But the problem is that any one can modify the codes available in package. If it would have been a scenario there is no chance to change the code. Because its a sealed code. Thats why everyone likes to promote scenario to other environment instead of promoting all objects one by one which includes a lot of risky.

      • Hi. Thanks for your reply. Yes I understand why scenarios are used for code promotion but I’m trying to understand how the scenario executes the code if the packages/interfaces don’t exist in the repository. Using your OWB example, if we create a mapping in DEV and export/import the mdl in QA and go open that mapping in design center in QA, we will see the actual exact mapping with the objects bound accordingly. But with ODI, say we we have a package whose scenario we have exported/imported in QA, when we go to QA, we don’t actually the package. For example, in my case, I have one master, one work repository in DEV and same in QA. Both are development repositories. Now when I create a package in DEV, I generate a scenario for it and promote it to QA using Import Replace mode. So now only the scenario exists in QA, but none of the underlying packages, datastores, interfaces etc are in the repository, but the execution runs fine. So I’m curious how this happens even though QA is not an execution repository. Shouldn’t it be erroring out since the actual interfaces and datastores don’t exist?

  2. Hi Bhabani,
    This is my first time at your blog(Although I know you through otn odi discussion forum).I have a request.If it is possible to expose odi scenario as webservice, then please write it step by step using snapshots.

  3. Thanks Bhabani for a quick turn around@OTN. i could continue there since OTN downtimw was going on

    I could sense that the issue has something to do with the DATE type function. but the worry is how do i incorporate the solution?
    in source flat file after i do a Reverse Engineering the Attributes i get if i change their data type and format especially for DATE datatype for HIREDATE Attribute , then the data load doesn’t happen on to Staging area.
    if i don’t change and keep the DATA type as it is then Data load happens to Staging. in the Datastore that i create from the model has all ATTRIBUTES reverse engineered but if i change the format and data type of any ATTRIBUTE it doesn’t take effect.
    i know there has something that needs to be done with DATE data type.
    my TARGET table create script is:

    (EMPNO NUMBER(10),
    JOB VARCHAR2(50),
    MGR NUMBER(10),
    SAL NUMBER(10),
    COMM NUMBER(10),

    SOURCE is a flatfile.

    where do i incorporate the to_date function and the requirement being that the HIREDATE Attribute data should be of DATE type on TARGET
    i followed the below OBE but have a different flat file to test where in i have a DATE ATTRIBUTE.


    • I have just replied you in OTN..I believe you are doing some minor mistake. Can i have a look(team viewer) into your interface ?? Or you can send some screenshots to understand whether the mapping expression is correct or not ?


  4. Dear Bhabani,

    The site is very useful and thanks a lot for the nice posts. I have one query regarding ODI Java EE agent. I want to execute Shell scripts or OS command via Java EE agent. Please let me know if it is possible. Please also let me know if we can configure multiple Java EE agent.


  5. I am not able to make multiple work repos in a single master repo.
    when i make a new work repo in a master repo where one already exists the older one gets unbounded from the master repo.
    how can i maintain both the work repos bonded with the master repo at the same time?
    please help

  6. i have create a variable then i used that variable in package and set its type to refresh and then i create scenario for this package and then i want to invoke this scenario in KM it is possible as u told me by ODI tools


    i came across an error

    java.lang.Exception: Error while running child scenario:

    Suggest please ………

  7. hello bhabani,
    i have installed odi as standalone but my master repository stored in my oracle server which credentials for that is server1:1521:init2
    when i creating agent i get error
    my master repository user is DEV_ODI_REPO
    i encode the password and i add this into the ODIPAREMS.bat
    when i done with this i get error that bad password
    then i tried for encript again DEV_ODI_REPO and add to the ODIPARAMS.bat
    and also the supervisor password i am encripted and add to the .bat file then its show the agent is running and agin it gives error the bad credetials are not match
    so what should i do
    if u have any sol please replay urgent
    if no please reply

  8. How to join DWTeam site as i cant see new user tab there…
    i am working in OBIEE and looking ahead for ODI,OBIA with ODI as ETL.
    Awesome Work in ODI anyways.Much Appreciated.

  9. Hello Sir,
    I have export odi folders using sdk (SmartExportServiceImpl ) it is working fine, Now requrement is smartImport using sdk, I tried with
    final ISmartImportService impl = new SmartImportServiceImpl(odiInstance);

    but giving me error -SmartExport.xml is not a smart export file and can not be imported by smart import.
    I tried ur suggestion given on otn but still error is coming

    I was waiting for your post on same topic.
    Please add post on topic “Smart import using sdk”


  10. Hi,

    While using the Global variables in password field in dataserver connection in ODI12c, I am facing “invalid ORA-01017: invalid username/password; logon denied”.

    If I hardcode the password the connection works fine. If I use Global variables in User field even then it works fine. But with #GLOBAL. in Password field , the connection fails and works only with the hardcoded password value.

    All was working fine in ODI 11g. After importing code from ODI 11g to ODI 12c, I am not able to execute with global variables in password fields.

    Please kindly help with this issue.

  11. Hello Sir,

    I was trying to set the behavioral properties of columns for SCD2 using groovy script. As I have to change more than 500 column properties it is hectic.

    Can you please help me with the groovy script code, and where to add the groovy script and how it works.

    Thanks in Advance

  12. Hi Bhabani,

    can you please post some explanation on
    How to load the data to single target from two different sources like XML or Flat file or SQL.

    Thanks a million,

  13. Hello Sir,

    I am using OdiSqlUnload to load sql query data into csv file in a Package. when I run just OdiSqlUnload step, it ran without any errors. But when I looked at the file, it is not refreshed.
    How can I make OdiSqlUnload, to refresh the file.
    I am thinking to generate a csv file thr this OdiSqlUnload, and call OdiSendMail, to send that file as an attachment.

    Thanks & Regards

    • Hi,

      Still I don’t see the file being refreshed in the Directory, but OdiSqlUnload o/p file is getting attached to OdiSendMail. Now I am facing some issues with the CSV file. ” I am sending a text msg with character “–” but it is converting into “?” in the attached csv file. Can you please help me how to avoid this conversion in csv file from “–” to “?”.

      Thanks & Regards,

  14. Hi,

    I have to send link of the file uploaded on ShareFile using SSIS..How can I do that?

    I can upload the file on ShareFile by copying it to the folder on Desktop(or some local drive) and then I need to copy the ShareFile link and email through SSIS.How can I get the ShareFile Link?

    Please help.


  15. Hi Bhabani,
    I have a requirement in which I am migrating data from DB2 to Oracle. I have created all my process and data is is getting transferred successfully too. But now I came to know of a scenario in which data in Source is getting delete (Table has a primary key col & 4 cols as unique index) and the new row getting inserted has the same unique index col values.
    As a result when I am trying to merge the data it is checking the PK and since there is no match it is trying to insert.
    So I need to delete the record where Unique index key matches (some column has null value too). I have modified the KM to delete data but since some column has null value it is not deleting all the records. Can you help me to add NVL in the below code:

    delete from
    () IN
    (select from )

    Rajib Banik

      • I can not update since in db2 unique index key value matches but the primary key is different. The application deletes the existing row based on some logic and then reinsert the data with same unique key values but different primary key.

        • Then it make sense to delete the record before insert. Can you share the complete code that you have customized as I am not sure what type of customization you have done. If the column name is fetched using then you can wrap NVL around the COL_NAME. The same applies to

          [code lang=”java”]
          <%=odiRef.getColList("", "NVL([EXPRESSION],00)", ",\n\t", "", "(not TRG) and REW")%>

          select <%=odiRef.getPop("DISTINCT_ROWS")%>
          <%=odiRef.getColList("", "NVL([EXPRESSION],00)", ",\n\t", "", "(not TRG) and REW")%>
          from <%=odiRef.getFrom()%>
          where (1=1)


  16. Hi Bhabani,
    Actually we are working on ODI CDC.When there is a change in data ,it has entries in our journal(J$) table .But occasionally when our ODI agent stops ,the data from journal table(J$) gets lost without reflecting into our final table once the packages starts running the subsequent time .Whereas the data flows properly into our final table from J$ table if the packages are continously running with ODIWaitFORDATA without intruption .But since we dont want to lose our data from J$ table when ODI agent stops ,can you pl help us with some suggestions

  17. Hi,

    I am using ODI and I want to upgrade it to but I am new for ugrading. Do you have any basic document to use it step by step, do you think it will be hard to uprgrade it or it is easy ?


Leave a Reply