Creating an ODI Procedure

A common task that is performed by using ODI is to create and populate a relational table. This practice 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.
In this practice, you use the same RDBMS schema, ODI dataserver, and ODI physical schema–all named ODI_STAGE–which you created in Practice 10-1. In the predefined project,
Procedure-CRT-TBL, you create the procedure PRD-create-populate-table.
Next, you add commands to drop, create, and populate a table, using the syntax provided in text files.
Finally, you execute the procedure and verify that the table was created and populated with records.
1. Create the project Procedure-CRT-TBL.
2. Create the procedure PRD-create-populate-table.
3. Add commands to drop, create, and populate a table.

Your Tasks:
1. Create new project in ODI Designer.
a. Switch to the Designer Navigator. Click the Designer tab. Select the Projects tab, click the New Project icon, and select New Project.



b. Enter the name of the project: Procedure-CRT-TBL. Click the Save button to create the project and close the tab. The project appears in the tree view. Expand the Procedure-CRT-TBL project node.




Note: In this practice, you use the same RDBMS schema, ODI data server, and physical schema that you created in Practice10-1. If you have not performed Practice 10-1,complete Practice 10-1, steps 5 and 7 to create the RDBMS schema, the ODI source data
server, and the physical schema for your new RDBMS model.
c. Open the ODI Topology Navigator, select Physical Architecture. Expand Technologies > Oracle > ODI _STAGE. Verify that you have the physical schema ODI_STAGE created under ODI_STAGE data server.


d. Open ODI Designer. In the Projects tab, select Procedure-CRT-TBL > First Folder.Right-click Procedures and select New Procedure.

 
e. Enter the procedure name as PRD-create-populate-table. Set Target Technology to Oracle. Click the Details tab. Click the icon to add a step in the procedure.




f. In the Command: New window, enter the parameters of the procedure and the command on the target provided in the following table. You need this command to drop the table in case a table with this name exists. Select the Ignore Errors check box.

Parameter                               Value
Name                                    Drop table
Technology                             Oracle
Schema                               ODI_STAGE
Ignore                                   Errors Checked
DROP table ODI_STAGE.SRC_SALES_PERSON
g. Select the Details tab, and then click the icon to add another step in the procedure. In the Command: New screen, if not selected, click the Command on Target tab. Enter the parameters of the procedure and the command provided below.
Note: You can copy this command from the 13-1.txt file, which is provided in your Lab folder in the following location: c:\Labs\Text.
Parameter                            Value
Name                                                    Create Table
Technology                                              Oracle
Schema                                               ODI_STAGE
Ignore                                          Errors Unchecked
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 "SRC_SALES_PERSON_PK" primary key("SALES_PERSON_ID")
)



h. Click the Details tab. Click the icon to enter the step to populate the SRC_SALES_PERSON table.


i. In the Command: New screen, click the Command on Target tab. Enter the parameters and the command shown below. Click the Details tab to view your steps.
Note: You can copy this command from the file 13-1.txt, which is provided at the following location: c:\Labs\ Text.
Parameter                                Value
Name                                   Populate table
Technology                              Oracle
Schema                               ODI_STAGE
Ignore                                 Errors Unchecked
begin
insert into ODI_STAGE.SRC_SALES_PERSON values
(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(20,'Jennie','Daumesnil','28/02/1988',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(51,'Cynthia','Nagata','28/02/1994',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(52,'William','Kudo','28/03/1993',sysdate);
end;


j. Click Save and close the tab.
 
2. Execute your newly created procedure and verify the results
a. Expand Procedure-CRT-TBL > Procedures, select the newly created procedure PRDcreate-populate-table, and then click the Execution button to run the procedure .Click OK. Click OK again.








b. Open ODI Operator. In ODI Operator, click the All Executions node, click the Refresh  icon, find your session, and verify that your procedure executed successfully.


c. Open the Oracle SQL developer and expand the ODI_STAGE connection. Select Tables node and click the Refresh button . Double–click the newly created table SRC_SALES_PERSON, click the Data tab, and verify that your table SRC_SALES_PERSON was successfully created and populated with records as shown in the following screen.

3. Additionally, you create an option to delete rows from the target audit table, which you created in Practice 12-2. To delete rows, you add a command that uses the value of the option in the code. If the value of this option is set to “Y”, the command to delete records
will be executed.
a. Right-click your procedure and select New Option. Name this option DELETE_AUDIT_RECORDS, and set Type to Value. Add the description as shown on the screen. Set the default value of this option to Y (no quotes). Click the Save icon.




b. Open your procedure, PRD-create-populate-table, and then click the Details tab. Click the Add icon to add a new command.

c. Add the new command on Target to delete records in the audit table TRG_SALES_PERSON_H. Set the parameters as shown in the following table.
Note: You can copy this command from the file 13-1.txt, which is provided at the following location: c:\Labs\Text.

Parameter                                 Value
Name                            DELETE AUDIT RECORDS
Technology                               Oracle
Schema                          ODI_STAGE
Ignore                         Errors Checked

d. Click the Options tab and verify that the value of your newly created option is set to Y.Click the Save icon.
Note: To view the inputs in the Options tab, you must click the Refresh button.


e. Execute your procedure. In Operator, verify that the procedure executed successfully.





f. Open SQL Developer and expand ODI_STAGE > Tables. Open the Audit table
TRG_SALES_PERSON_H, click the Data tab, and verify that the rows were deleted
from the Audit table.