ODI Interview Questions

ODI interview questions:

1. What is Oracle Data Integrator ?
2. Can we call ODI as a ETL tool. If yes then How ?
3. Is it a ELT tool? Explain.
4. What are the new features available in ODI 11g?
5. What is agent in ODI. Differentiate local agent and java ee agent.
6. What is the role of context ?
7. What is logical schema ? What is the importance of it ?
8. Explain ODI Architecture.
9. What is the error handling mechanism available in ODI ?
10. What is the difference between flow_control vs static_control ?
11. What are knowledge modules ? Which kind of codes they support.
12. What are different ODI Tools you knows ?
13. How interface works with aggregation ?
14. What is dataset and how they are helpful in interface ?
15. What do you mean by control append and incremental update ?
16. What is scenario in ODI and why it is required ?
17. What is local variable and global variable in ODI. How can we access them inside KM and procedures ?
18. What are the different datatypes and history of an ODI variable ?
19. What is temporary interface ? How they are helpful ? Give some scenarios on this.
20. Can we use a sql query as source in interface ? if yes then how if not then why ?
21. How can you configure a java ee agent ? Explain.
22. What is internal id in ODI ? Explain.
23. What is sunopsis memory engine ?
24. Can we create multiple local agent ? If yes then how ?
25. What are the different strategy for promoting a project from one env to other env.
26. What are the minimum things required to take master and work repository backup.
27. Is it necessary to have a key in interface. Explain your answer.
28. How to create agent as a service in 10 and 11g version.
29. What are the known bugs of ODI you have listened ?
30. What is the oracle forum id of oracle data integrator?
31. How do you create different user for ODI login. What are the different profile available ? Explain.
32. What do you mean by webservice ? How ODI support web services ?
33. What is ODI console and EM ?
34. How ODI is different than OWB ?
35. How can you send authenticated emails in ODI.
36. What are the different temporary tables created in ODI ?
37. How can you handles when multiples interfaces loading to a same table ?
38. What is substitution api ? How to get the error message using substitution api ?
39. What do you know about ODI SDK. What is the use of ODI SDK ?
40. What technology you will chose to process mdb files ?

I will append more questions as soon as i can.
Thanks guys.

About Bhabani 86 Articles
Bhabani has 12 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.


    • Srikanth, I have not given the answers knowingly. Its not only you but also everyone in this world including me are looking for shortcut. If i will post the answers then people wont go for looking other available answers on internet. The more you will search the more your understanding will be clear. Hope you got my intention of not posting the answers.

  1. Guys..its better to apply the theory “Practice makes it perfect”, rather than looking at a tool by its questions. Gather some Hands On training on the tool, you don’t need to look at it as a bunch of questions.

    • Agreed. Practice makes it perfect. nothing comes easily without hard work. That’s why i didn’t posted the solution. More research in the tool will provide better understanding.

  2. Nice…Blog created Rajan

    Maintain and continue with regular posts on blog…It helps to others and improve your skill.

    If we find some thing missing on blog,we will also happy to participate.

  3. hey my question is
    why do we need odi…if we need to do integration we can just do it with simple coding we have drivers for connecting to databases, we can use code for integration,then why do we need this platform.. i know it does have some advantage then how can you correlate with its need..

    • Good question. But prior to that you should get some knowledge about ELT,ETL mechanism. These are the common terms used in data warehouse and all tools used for the same. ODI can be called as ELT (Extract,Transform,Load) as well as ETL tool.

      So these tools can connect and read data from multiple sources/heteregenous sources like relational databases (sql server, NetZZa,MySql,DB2,Essbase,Interbase), flat files, xml files, cobol files etc.

      The capability of connecting and reading data from different sources is readily built in ETL tools. As an user you don’t need to write a code for this.

      ETL tools comes with built-in error handling capability

      You can process data in parallel with ETL tools easily.

      Now if you will all these thing then you have to
      1. Write your own for connection code to multiple sources and reading.
      2. If you wont get odbc driver to connect all systems then ?
      3. Take care of error handling by identifying each and every possible error.
      4. Parallel with programming languages may result in thread safety issues and might lead to data corruption.

      Now tell me which one is better for you ?

      • thanks for your reply,
        Ya I know about ETL, I am an OCA and works on weblogic, recently i have been working to deploy ODI on Weblogic, i.e your ODI console, πŸ™‚
        I have few queries since I am doing now some R & D on ODI

        (A) suppose I have deployed both ODI console and Oracle Hyperion on weblogic and now I want my hyperion to use ODI to get the data and display it on portal for analysis.
        now how do they interact(mean how do we integrate ODI and Hyperion).

        (B) Now the above work as mentioned in (A) like getting data for hyperion from different databases and then integrating it can be done through coding since hyperion uses java and we have JDBC to connect to different databases. so there are no issues in it….Then why would I invest in ODI.

        I will be thankful if you explain its beniifts with some practical scenrio πŸ™‚

          • Hmm, question A, I believe John can answer on this very well. I am not expert in hyperion πŸ˜€ Please put this question in OTN Forum https://forums.oracle.com/forums/forum.jspa?forumID=374

            so that all experts can answer on this. For last part of question B, are you going handle all jdbc connections, data validation, data cleansing , log count details ( inserted,updated deleted, violated ), time consumed , peformance tuning, data consistency bla bla mannually ? If yes then I salute to you man πŸ˜€ ..And its good not to invest on ODI.

            Now if the decision is really sensitive for you please have talk with oracle support, they can explain far better than me. And let me also know your final decision.


  4. Informatica is there for long time as an integration tool and lots of companies have successfully used it for their data integration. I know the basic difference between Informatica & ODI is ETL vs ELT , but question is what way both are different and what are each’s merits over other.

    Note : Lets keep Oracle marketing/hype aside, as this question is only to evaluate the technical merit of ODI over other tools.

    • Hi Dinesh, I am not an expert in Informatica. But here is my observation.
      1. Less budget, Less data/Less data (atleast 1 million) : Go for ODI
      2. More Budget, More data : Depends on the transformation to chose the tool

      ODI as ETL (staging is a separate server than the target)
      On everyday basis I am dealing with 4 million records to insert as well as update on target and the maximum time taking is 20 minutes for me. If i will be only inserting then maximum time is 5 minute.

      I doubt (not sure) if you can load 5 million records within 5 min into target table using Informatica (Informatica experts can better comment on this).

      Now if I will use ODI as ELT (Staging server is same as target server), I am pretty confident it will take much less time mentioned above.

      So the time factor is going to play an important role. Now if you are dealing with more than 10 millions record then it will be better to test it in both the tool.

      Another point is if you have complex logic then ODI is the best option to deal with it. But informatica will make the process slower.

      Next point ODI Process setup is far easier than Informatica.

      Coming to ODI demerits given by other people is :
      1.ODI KM: But I will say this is the flexibility to get your requirement done. You can achieve any kind of customization using either odi studio or grrovy or sdk or java beanshell or rest technology avaiable. Never expect everything will be done by just drag and drop. Sometimes you have to use your brain.
      2: Project Promotion from Env to Env: I agree with this some extent. But it depends on the architecture and policy setup by the data modeler. Once this will done properly, no one will face issue going forward.( Here you have pay to more attention in maintaining them)

      Many developers says that in case of huge data , Informatica works fine but till now i have not found any major difference when time factor comes (Might be i have not come across). So why I am going to invest huge amount on Informatica.

      Let me know your views.

  5. thanks for your reply πŸ™‚

    Ya I knw about ETL I am an OCA working on weblogic recenlty i have been working to deploy ODI on weblogic..i.e ODI console
    Now I have some queries since i am doing some R & D on it..

    (A) Suppose I have deployed ODI and Oracle Hyperion on weblogic, now I want hyperion to use ODI to get data for creating reports and analysis and displaying it on portal for analysis.So how can we make them interact(i.e integrating ODI and Hyperion)

    (B) Now for the above work as mentioned in (A) we can use coding since hyperion works on java and we have JDBC to connect to different databases (oracle,mysql,MS sql server etc.)so we can use coding to get data and then pass that data to hyperion to create reports for analysis. Then why should i invest in ODI?

    I would request you to consider this point on an Enterprise level since we need to purchase license to use at Enterprise level.

    I would be thankul if you reply me with some practical scenario that it how does it works wen a user quries for some data on a portal.

    • Krisna, I believe most of the questions are pretty easy. Please let me know which question seems to be difficult for you so that i can answer it right here.

    • Plz create one option ORDER_BY in your KM and put below codes into the end of select statement.

      Now if you will go to the flow control to select the IKM you can see the order by option there. Right here provide the column names that you want in your order by clauase

      • Hi Bhabani,

        I create option in the KM, but in that there is a option (type) for this what should i select (check box,value (or)text). where should i copy order_by function ? but i copied it in Details tab in “insert new rows” command and it throws an error while runinng the interface. It is little bit confusing…
        can u please guide me………


        • You can select text or value. Then open the KM step as i said earlier and at the end of select statement write ORDER BY odiRef.getOption(“ORDER_BY”) (dont forget to provide the scriptlet tag arround the substitution api )
          Now save the KM and reopen interface. Go to the flow tab , you must be able to see the option ORDER_BY. Provide the column name that you want to see in order by clause.

          • Hi Bhabani,

            At the end of the select statement i wrote like this in KM.


            where (1=1)

            In interface i wann perform order by class on SALARY port, for that in flow tab i enterd SALARY on ORDER_BY class then i ran that interface it throws an error.

      • Hi Bhabani,

        At the end of the select statement i wrote like this in KM.


        where (1=1)

        In interface i wann perform order by class on SALARY port, for that in flow tab i enterd SALARY on ORDER_BY class then i ran that interface it throws an error.

          • Hi Bhabani,

            I am trying to copy the select statement code, but it is not displayed in the comment box that why i tried 3 times. when i ran that interface the error show like this.

            ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection SCOTT.
            Caused By: java.sql.SQLException: SQL string is not Query
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1442)
            at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3752)
            at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3806)
            at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1667)
            at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
            at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:89)
            at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
            at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:67)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
            at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
            at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
            at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
            at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
            at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
            at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
            at java.lang.Thread.run(Thread.java:662)

  6. Hi Bhabani,

    I’m new to ODI and i’m using ODI 10 version.i would like to upgrade the version ODI 11.And i’m using the databases Microsoft SQL Server,Netezza,Oracle.can you please suggest a step by step instruction document?i had gone through some docs like http://docs.oracle.com/cd/E21764_01/upgrade.1111/e12642/tasklist.htm and so that i have a picture but it is not clear.While i gone through some other topics in your blog found the well explained with picturized.Hope you can help us .

    • Please let me know the database on which your repository lies. If its oracle then you can refer my post. If its other than oracle then please take the backup of your repository. This is the 1st thing you have to do. Then proceed with the general approach.

  7. hi……i am Working on ODI technology since from 7 months…i want to switch the compony + technology….so is there any requirement for 1 yr experienced employee..
    Reply soon

  8. Hi Bhabani,

    It was one of the best blog for ODI and your efforts are greatly appreciable.

    Awaiting for your new implementations and ideas on ODI.

    Many Thanks,
    Pavan Kumar

    • SDK just means Software Development Kit. In ODI, it normally just referes to a library of code. If something cannot be achieved by the available functionality in ODI studio, you can take the help of SDK to write your custom java codes. You have to import bunch of jar files from odi.sdk lib folder to your java program. Then use your logic to achieve this. For example you dont want set the scheduling details in odi studio rather from outside. How is that possible ? Well write your java code and achieve this. You can refer one of my post on this. Thanks.

  9. Hi Bhabani,

    I am working in odi tool from last 7 months.I want to know to be a good resource and to get a good job in this tool what other technology we should also learn related to this domain.Please guide me.


  10. Hi Bhabani,

    Thanks, this blog really helps a lot.
    Can you explain about load plans in ODI please?


  11. Can you please tell how to migrate scenarios/objects/packages or code from ODI dev environment to QA/prod.

  12. Hi,

    Does ODI supports SQL server 2012?
    If so which is sjdbc/java version need to use?

    thanks for your help!


  13. Bhahani

    Could you please send the answers to the above list of questions…

    Thanks a lot

  14. Hi I am working on ODI for around 1.5 years.I want to switch.Let me know if there is any Requirement .
    Thanks in advance πŸ™‚

Comments are closed.