Creating an ODI Project and Procedure to Create and Populate a Relational Table

Purpose

This tutorial walks you through the steps that are needed to create a project and a procedure in Oracle Data Integrator (ODI) to create and populate a relational table.

 

Time to Complete

Approximately 20 minutes

Topics

This OBE tutorial covers the following topics:

 Overview

Scenario

 Verifying the Prerequisites
 Creating a New Project with Oracle Data Integrator
 Creating a New RDBMS Schema for the ODI Source Datastore
 Creating a New ODI Source Data Server and Physical Schema
 Creating a New Procedure with Oracle Data Integrator
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, the response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment that you are using. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

A common task that is performed using ODI is to create and populate a relational table. This tutorial walks you through the steps that are needed to create a project and a procedure that will create and populate a relational table with data. You also execute the procedure and verify the execution within ODI Operator.

Back to Topic List

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on the various resources within the organization. In particular, Linda is responsible for data loading, transformation, and validation. To begin working on her projects, Linda created the new Master repository and Work repository. Now Linda needs to create a project and a procedure to create a relational table and populate it with data.

Back to Topic List

Verifying the Prerequisites

Before you start the tasks, make sure that the following requirements are met:

If not done before, start the services and components for Database 10g XE and Oracle Data Integrator 10g (10.1.3.4).

Back to Topic List

 

Creating a New Project with Oracle Data Integrator


To create a new project in Oracle Data Integrator, perform the following steps:

1.

Start ODI Designer: Start > All Programs > Oracle > Oracle Data Integrator > Designer. Select WORKREP from the Login Name drop-down list if not already selected. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.

 

2.

In the window that opens, click the Projects tab, and then click the Insert Project button  to add a new project.

 

3.

On the screen that appears, set the Name of the project to ODIcreate_table in the Name field. The Code field is filled automatically. Click OK. The newly created ODIcreate_table project now appears in the Projects tree view. You have now successfully added a new ODI project

 

Back to Topic List

Creating a New RDBMS Schema for the ODI Source Datastore

You have to create a schema (ODI_STAGE1) to host the ODI source datastore. To create a new RDBMS schema for the ODI datastore, perform the following steps:

1.

Open the Oracle XE Database Home page: Start > All Programs > Oracle Database 10g Express Edition > Go to Database Home Page. The Login screen appears. Log in to Oracle XE Database as system. The default password is oracle1. Click Login.

 

 

2.

On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.

 

 

3.

Create the schemas by executing the following SQL commands:

create user <MY_SCHEMA> identified by <MY_PASS>
default tablespace <MY_TBS> temporary tablespace <MY_TEMP>;
grant connect, resource to <MY_SCHEMA>;


Where:
<MY_SCHEMA> corresponds to the name of the schema that you want to create
<MY_PASS> corresponds to the password that you gave
<MY_TBS> corresponds to the Oracle tablespace where the data will be stored
<MY_TEMP> corresponds to the temporary default tablespace

To create the user, enter the following command. Click Run.

create user ODI_STAGE3 identified by password
                                      
default tablespace users temporary tablespace temp;

 

4.

Verify that user ODI_STAGE3 was successfully created.

 

5.

Grant connect privileges to the newly created user by executing the following SQL command:

grant connect, resource to ODI_STAGE3;

Verify that this statement processed successfully.

 

Back to Topic List

 

Creating a New ODI Source Data Server and Physical Schema

In the following steps, you create a new ODI source data server and physical schema. To create the ODI source data server and the physical schema, perform the following steps:

1.

Start ODI Topology Manager: Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager. Select Master Repository. The default User and Password are preset as SUPERVISOR and SUNOPSIS. Click OK to login.

Note: The Master repository was created in the OBE titled Creating and Connecting to ODI Master and Work Repositories. To access this OBE, click HERE.

 

2.

Click the Physical Architecture tab of Topology Manager. Expand Technologies, right-click Oracle and select Insert Data Server.

 

4.

In the DataServer: New window, enter the values provided in the following table. Click the JDBC tab.

Parameter Value
Name ODI_STAGE3
Instance/dblink (Data Server) localhost
User ODI_STAGE3
Password password

 

5.

Click  for JDBC Driver. Select Oracle JDBC Driver. Click OK. Click  for JDBC Url, and then enter jdbc:oracle:thin:@localhost:1521:XE for Oracle XE Database ( don't copy and paste ). Click Test. Click Test again.

Notes:

  • Don't copy and paste in the JDBC Url field! This may cause problems with entering a valid URL string.
  • You may need to enter the appropriate driver and URL for your RDBMS.

6. Verify successful connection and click OK. Click OK again. The Physical Schema screen appears.

7.

On the Physical Schema screen, select ODI_STAGE3 in the Schema (Schema) and Schema (Work Schema) fields. Click the Context tab. Click the Add new context button  . Set Logical Schema to ODI_STAGE3. Click OK.

Back to Topic List

 

 

Creating a New Procedure with Oracle Data Integrator

To create a new ODI procedure, perform the following steps:

1.

Click the ODI Designer icon  to open ODI Designer. In ODI Designer, click the Projects tab. Expand the ODIcreate_table project, and then expand First Folder. Right-click Procedures and select Insert Procedure.

2.

On the screen that follows, enter the procedure name as PRGcreate-populate_table. Set Source Technology to Oracle. Click the Details tab. Click the icon  to add a step.

 

3.

On the screen that follows, enter the parameters and command from the following tables and click OK.

Parameter Value
Name Create table
Technology Oracle
Schema ODI_STAGE3

Note: You may need to set the Technology field to other appropriate technology.

CREATE table "SRC_SALES_PERSON" (
                                      
"SALES_PERSON_ID" NUMBER(8,0) NOT NULL,
"FIRST_NAME" VARCHAR2(80),
"LAST_NAME" VARCHAR2(80),
"DATE_HIRED" VARCHAR2(80),
"DATE_UPDATED" DATE NOT NULL,
constraint "TRG_SALES_PERSON_PK" primary key("SALES_PERSON_ID") )

4.

Now, you enter the step to populate the SRC_SALES_PERSON table. Click the icon  to add a step. Click the Command on Source tab. Enter the parameters and the command from the following tables. Click OK, and then click Apply.

Note: You can copy this command from the SRC_SALES_PERSON_DATA text file, which is provided with this OBE. To download this file, click here.

Parameter Value
Name Populate table
Technology Oracle
Schema ODI_STAGE3

 

begin
insert into ODI_STAGE3.SRC_SALES_PERSON values
(11,'Andrew','Andersen','22/02/1999',sysdate);
                                      
insert into ODI_STAGE3.SRC_SALES_PERSON values
(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(20,'Jennie','Daumesnil','28/02/1988',sysdate); insert into ODI_STAGE3.SRC_SALES_PERSON values
(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE3.SRC_SALES_PERSON values
(51,'Cynthia','Nagata','28/02/1994',sysdate); insert into ODI_STAGE3.SRC_SALES_PERSON values
(52,'William','Kudo','28/03/1993',sysdate);
end;


 

5.

To test your procedure, click Execute. The following screen appears. Retain the defaults and click OK. On the screen that follows, click OK.

 

6.

To verify that your procedure was executed successfully, you need to open ODI Operator. Click the ODI Operator icon  on the menu bar to start ODI Operator. In ODI Operator, expand Date > Today > PRGcreate-populate_table > Steps and view the execution results for each step.

 

7.

To verify that the SRC_SALES_PERSON table was populated successfully, start Oracle XE Database. Log out, and then log in as user ODI_STAGE3. The password for this user is password.

 

8.

On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list. Verify that the SRC_SALES_PERSON table was populated with data by executing the following statement.

select* from ODI_STAGE3.SRC_SALES_PERSON

Note: If you want to execute the procedure again, you have to first drop the SRC_SALES_PERSON table.

9.

In ODI, click the icon  to return to ODI Designer. Click OK to save your newly created procedure. It is now shown in the Projects tree structure.

You have now successfully created an ODI procedure to create and populate the SRC_SALES_PERSON relational table.

 

Back to Topic List

 

Summary

In this lesson, you learned how to:

 Verify the Prerequisites
 Create a New Project with Oracle Data Integrator
 Create a New RDBMS Schema for the ODI Source Datastore
 Create a New ODI Source Data Server and Physical Schema
 Create a New Procedure with Oracle Data Integrator

Back to Topic List

 

 

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document