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

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

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

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;

Verify that user ODI_STAGE3 was successfully created.

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.

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

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

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.

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.

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.

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

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")
)

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.

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;

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

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.

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.

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.