Why odi if we have PL/SQL lanuguage

Question

Someone asked me why we need ODI tool if we have PL/SQL code. Odi is generating the PL/SQL code in the back end . why we need ODI interface if we can use code generated by odi interface even using on step less instead of putting data into I$ table we can directly push it with PL/SQL.

Let's take and example: IF we have to insert 2000 records into a another table from one table we can directly use PL/SQL code instead of designing odi interface which make me confused thinking how odi is better than just a tool.

Answer 1

There are a lot to say, but I can mention to you the most important aspects, in my opinion:

• In ODI, you can write KM's (Knowledge module - some SQL/OS commands/Groovy/Java generic code, that generates the statements that you need, based on the source and target tables/table). After creating, you can use it in many mappings. Conclusion: write once, use many times;
• ODI have an API: with it, you can automatically generate mappings/objects. So, you don't need to manually create 100 mappings (for example), but maintain a Metadata Repository, from where you can generate mappings automatically;
• The fact that you can combine sql with Groovy gives you such a power, that you can't find it in other ETL tools (from what I know);
• ODI Contexts - permit you to run the same mapping on different servers or for parallel work;

For your example, it's clearly that it's easy to made it through sql, if it's once. But if you have 10 similar sql to build, you may save some time writing a KM that meets your desire and then generate/create 10 mappings.

There are more to say. If you need, I can expand this post with more. Feel free to tell me.