Creating an ODI Package

A common task that is performed by using ODI is to create a package that executes a number of objects (Interfaces, Procedures) in the flow. In this practice, students create an ODI procedure and package to execute the sequence of transformations with the interfaces created in the previous practices.
In this practice, you create a procedure, DELETE_TARGET, for deleting from the TRG_CUSTOMER, TRG_CITY, TRG_REGION, and TRG_COUNTRY tables.
Then you create and execute a package, PKG_14-1. This package runs your new procedure,and then runs four interfaces you defined earlier in Lessons 8 and 9, which will use these same tables as targets:

• The DELETE_TARGET procedure
•  Practice 8-1 interface
•  Practice 8-2 interface
•  Practice 8-3 interface
• Practice 9-1 interface

Finally, you run this package and review its execution in ODI Operator.

Your Tasks:
1. Create a DELETE_TARGET procedure that deletes the data in the TRG_CUSTOMER,TRG_CITY, TRG_REGION, and TRG_COUNTRY tables in the ORACLE_LOCAL_SALES logical
schema.
a. If not open, log in to DEV_ODI_REPO and enter SUPERVISOR for the username and SUNOPSIS for the password.

b. In the Designer tab, click the Projects tab, and then select the Procedures node fromthe HandsOnLoads > HandsOn folder. Right-click the Procedures node and select the New Procedure option.

c. Enter DELETE_TARGET in the Name field, and select ORACLE as the Target Technology. Click the Details tab, and then click the Add icon .

d. In the Name Field, enter DELETE TRG_SALES. In the Schema field, select ORACLE_ORCL_LOCAL_SALES. In the Context field, select Development.In the Command field, enter the following code:

DELETE FROM <?=odiRef.getObjectName("L", "TRG_SALES",
"ORACLE_ORCL_LOCAL_SALES", "", "D") ?>
This method returns the table name with run time–dependent information, such as the Oracle schema name that may vary depending on the context and topology. Select the Ignore Errors check box. Select the Details tab.

e.Click the Add icon. In the Name Field, enter DELETE TRG_CUSTOMER. In the Schema field, select ORACLE_ORCL_LOCAL_SALES. In the Context field, select
Development.
In the Command field, enter the following code:
DELETE FROM <?=odiRef.getObjectName("L", "TRG_CUSTOMER",
"ORACLE_ORCL_LOCAL_SALES", "", "D") ?>
This method returns the table name with run time–dependent information, such as the Oracle schema name that may vary depending on the context and topology. Select the Ignore Errors check box. Select the Details tab.

f. Repeat the two previous steps to create the commands DELETE TRG_CITY, DELETE TRG_REGION, and DELETE TRG_COUNTRY, which delete the tables TRG_CITY,
TRG_REGION, and TRG_COUNTRY, respectively, as shown in the following screen.
Note: To enter the commands, you may refer to the file 14-1.txt provided at the following location: c:\Labs\Text.

g. Open the Details tab to view the commands. Click the Save button, and then close the procedure tab.

2. Create a package, PKG_14-1, that runs:

•  The DELETE_TARGET procedure
•  The INT_8-1 interface
• The INT_8-2 interface
•  The INT_8-3 interface
• The INT_9-1 interface

Note: These interfaces were created in Practice 8-1 and Practice 9-1.
a. Expand the Interfaces and Procedures nodes in the HandsOnLoads > HandsOn folder.Right-click the Packages node and select the New Package option.

b. Enter PKG_14-1 in the Name field, and then click the Diagram tab. In the tree view,expand the Interfaces node and Procedures node. Select the DELETE_TARGET procedure from the tree view, and then drag it to the diagram workbench. A DELETE_TARGET step appears in the package.

c. Repeat the operation for the INT_8-1, INT_8-2, INT_8-3, and INT_9-1 interfaces. The package should appear as follows.

d. Rename your steps with the names of the tables loaded by the step. In the Diagram window, click the step for the DELETE_TARGET procedure. Edit the Step name:
DELETE TARGET TABLES, as shown in the following screen. In the diagram, click the empty space. The step is now renamed Delete Target Tables.

e. Similarly edit the names of the other steps in the diagram, as follows.

f. Click the Step After Success tool icon on the Package toolbar. Click the Delete Target Tables step. Press and hold the left mouse button and move the cursor over the Countries step. Then, release the mouse button. A green arrow appears between these steps.

g. Click the Countries step. Press and hold the left mouse button and move the cursor over the Regions step. Then, release the mouse button. A green arrow appears between these steps. Repeat this operation to link the steps in the correct order:
Countries > Cities > Regions > Customers, as follows.

h. Click the Save button to save this package and close the package tab.

3. Run this Package and review its execution in ODI Operator.

a. Click the Execute package button to run this package. Click OK in the Execution window that appears, and then click OK again.

b. In ODI Operator, click the Refresh icon. In the session list, expand the All Executions node. The session called PKG_14-1 should appear as complete. You can expand the nodes in the session to see the steps and tasks corresponding to your procedure
commands and the interfaces that have been executed.

 
c. Open the Designer tab and close the PKG_14-1 tab. Click Yes to unlock this package.