Creating and inserting data into table through ODI
1. Start ODI studio first
2.Connect to your Work Repository.
3. Connection to work repository is initializing.
4. Connection to work repository done.
5. Click on Designer tab and then click on project icon and select New Project.
6. Name the Project as ODIcreate_table. The code block will be filled automatically. If code block will not filled automatically then copy the project name and paste the same in code area also.
Save the changes.
7. Once you save the project, you can see the Project name in Designer navigator.
8. Open the SQL Developer and connect it as Administrator by filling up following credentials.
9. Once you connected as Administrator create a User with name ODI_STAGE3 by giving password oracle1.
10. Grant connect privileges to the newly created user by executing the following SQL command:
11. Click the Physical Architecture tab of Topology Manager. Expand Technologies, right-click Oracle and select Insert Data Server.
What is Data Server?
Ans: Data servers describe connections to your actual physical application servers and databases. It storage the IP, User and Password for instance. They can represent for example:
§ An Oracle Instance,
§ A File System,
§ An XML File,
§ A Desktop database such as Microsoft Excel Workbook,
§ an application such as SAP or Salesforce
§ JMS server instance,
§ a scripting engine
Under this data server, subdivisions called Physical Schemas are created.
Recommendation: if you have a single system (Oracle …), only one data server must be created. Having 2 data servers defined pointing to a single physical data server may cause extra loading phases that are not needed.
For Oracle
An Oracle Data Server corresponds to an Oracle Database Instance connected with a specific Oracle user account. This User will have access to several schemas in this instance, corresponding to the Physical Schemas in Oracle Data Integrator created under the data server.
12. In the Data Server tab fill out the following credentials and save the changes.
13. In JDBC tab of Data Server tab specify the Driver and URL and save the changes.
14. Now create a new Physical Schema under newly created Data Server by right clicking on ODI_STAGE3 data server under Oracle technology in Physical Architecture navigator.
What is Physical Schema?
The physical schema is a decomposition of the data server, allowing the Data stores (tables, files, etc) to be classified. Objects stored in data servers with this mode of classification can be accessed by specifying the name of the schema attached to the object name.
For Ex: Oracle classifies its tables by "schema" (or User). Each table is linked to a schema, thus SCOTT.EMP represents the table EMP in the schema SCOTT.
15. Specify the following credential for new physical schema.
16. Save the changes and click on ok if any warning comes.
17. Now create a logical Schema by navigating to Logical Architecture navigator window.
What is Logical Schema?
Ans: Logical Schema is a single alias for several different physical schemas that have very similar data structure based on the same technology but in different context.
Suppose you have two physical schema say, conn1 and conn2 but all are pointing to same data server called empoyee, at this stage you can create a logical schema called emp_logical and club up conn1 and conn2 in different context.
18. Give the name of Logical Schema and set the context to physical schema.
What is context?
Ans: Context maps logical resources onto their implementations as physical resources. In a given context one logical resource is mapped to one unique physical resource.
Save the Changes.
19. Again go to earlier created physical schema and open it.
20. Set the context to Logical schema under context tab.
21. Now go to Designer tab and select procedure under first folder which is inside your project. Right click on procedure and select New Procedure.
22. Name the procedure as create-populate_table and specify all other details.
23. Save the changes and click on NO of Locking Object Pop Up.
24. Go to Details tab and Click on Add icon.
25. Name the Command as create table and specify the following details. Then write down your SQL code in command block for table creation under command on target tab and save the changes.
26. Again go to Detail table and Add another command.
27. Name the Command as insert into table and specify the following details. Then write down your SQL code in command block to insert data into table under command on source tab and save the changes.
28. Right click on your procedure under project name and select execute.
29. Click OK.
30. Click OK.
31. TO verify that your procedure was executed successfully, you need to open ODI Operator.Click the ODI Operator icon 
32. Now connect your SQL Developer as user ODI_STAGE3.
33. Check whether data is inserted into table or not.
Simply wish to say your article is as astonishing. The clarity in your post is simply great, and I could assume you are an expert on this subject. Same as your blog i found another one Oracle ODI 12c .Actually I was looking for the same information on internet for Oracle ODI 11g and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.
ReplyDelete