o tRos enFoques utilizados

John needs to add more complexity to the workflow by creating a user function for mapping the values. This function will convert the values (0, 1, 2) to (“Mr,” “Mrs,” “Ms”). He also needs to create a variable DELETION_FLAG for counting the number of lines in TRG_CUSTOMER and to have the Delete Target step running only if DELETION_FLAG is greater than 0.

Background:

A common task that is performed by using ODI is using user functions and variables to add more complexity to your workflows and mappings. This practice will explain how to use variables, user functions, and the more advanced features in the packages.

1. In the HandsOnLoads project, define a user function group named Conversion, containing a user function named DearConvert.

2. Use this function in the interface INT_9-1 to convert the values (0, 1, 2) to (“Mr”, “Mrs”,

“Ms”).

3. In ODI Designer, create a numeric variable DELETION_FLAG that counts the number of lines in TRG_CUSTOMER.

4. Duplicate the PKG_14-1 package, naming the new package PKG_14-2.

5. Have the DELETE_TARGET procedure run only if the DELETION_FLAG variable is greater than 0.

Your Tasks:

1. Create a user function called DearConvert in a new user function group called

Conversion. This function converts the values (0, 1, 2) to (“Mr,” “Mrs,” “Ms”). Implement this function for Oracle as linked technology with a CASE WHEN expression.

Note: Remember to use the dollar sign ($) when using the parameters.

Oracle University and (Oracle Corporation) use only.

a. Select the User Functions node in the HandsOnLoads project. Right-click this node and select New User Function.

b. Enter DearConvert in the Name field, enter Conversion in the Group field, and enter the following in the Description field:

Converts the values (0, 1, 2) to (“Mr”, “Mrs”, “Ms”). In the Syntax field, enter: DearConvert($1)

Note: $1 is the parameter name you use in the decision case structure shown in the following screenshot.

Click the Implementations tab, and then click the Add button.

Oracle University and (Oracle Corporation) use only.

c. In the Implementation window, select Hypersonic SQL from the list of Linked

Technologies. Verify that Oracle technology is also selected. Enter the following code in the Implementation syntax field. Click OK to save this implementation. Click the Save icon, and then close the DearConvert user function tab.

Note

• In Locking/Unlocking Object windows that may appear, click Yes.

• To enter the commands, you may refer to the file 14-2.txt provided at the following location: c:\Labs\Text.

CASE WHEN $1 = '0' THEN 'Mr' WHEN $1 = '1' THEN 'Mrs' WHEN $1 = '2' THEN 'Ms' ELSE NULL END

Oracle University and (Oracle Corporation) use only.

Oracle University and (Oracle Corporation) use only.

2. You use this function in the interface INT_9-1 to convert the source DEAR column into the target DEAR column.

a. In the Projects tree view, expand Interfaces. Double-click the interface INT_9-1 to open it. In the Locking window, click Yes. Click the Mapping tab.

b. In the Target datastore, select the Dear column. Edit the implementation in the Properties panel to have the following code:

Oracle University and (Oracle Corporation) use only.

DearConvert(SRC_CUSTOMER.DEAR). Scroll down to select the Staging Area option from the Execute on panel. Click the Save button to save the interface.

3. Run the INT_9-1 interface and review the conversion result.

a. In the interface window, click the Execute button. In the Execution window, select Development context, and then click OK. Click OK when the “Session started”

message appears. Close the INT_9-1 editing tab.

Oracle University and (Oracle Corporation) use only.

b. Click the Operator tab. Select All Executions, and then click Refresh. Expand the All Executions node. The session called INT_9-1 should appear as complete. Note that the steps you see may not be identical to those shown below.

c. In the Mapping tab, right-click the Target datastore and select Data. Verify that the column DEAR was populated with “Mr” as shown below.

Oracle University and (Oracle Corporation) use only.

4. Create a numeric variable called DELETION_FLAG that counts the number of lines in TRG_CUSTOMER.

Oracle University and (Oracle Corporation) use only.

a. In the Designer, open the Projects tab, and select the Variables node in the HandsOnLoads project. Right-click this node and then select New Variable.

b. Enter DELETION_FLAG in the Name field, select Numeric as the Datatype, and enter 0 in the Default Value field. In the Keep History field, select All Values to enable viewing the history of all values for this variable.

Oracle University and (Oracle Corporation) use only.

c. Click the Refreshing tab. Select ORACLE_ORCL_LOCAL_SALES as the schema, and in the Select Query field, enter the following expression. Click the Save button to save the variable. Close the DELETION_FLAG variable tab.

Note: To enter commands, refer to the file 14-2.txt provided at the following location: c:\Labs\Text.

SELECT COUNT(*) FROM <%=odiRef.getObjectName("L", "TRG_CUSTOMER",

"ORACLE_ORCL_LOCAL_SALES", "", "D") %>

5. Modify a package to have the Delete Target step running only if DELETION_FLAG is greater than 0.

a. Duplicate the PKG_14-1 package. Open the package Copy of PKG_14-1 and rename it PKG_14-2.

Oracle University and (Oracle Corporation) use only.

b. Select the Diagram tab. In the project tree, expand the Variables node. Drag the DELETION_FLAG variable from the tree view to the diagram. Click the

Deletion_FLAG step in the diagram. In the Properties > General tab beneath the diagram, rename this step Refresh Customer Count. Ensure that the Type field is set to the Refresh variable.

Note: You need to click the step in the diagram to view the new step name.

Oracle University and (Oracle Corporation) use only.

Oracle University and (Oracle Corporation) use only.

c. Drag the DELETION_FLAG variable from the tree view to the diagram again. Rename this step Flag > 0?, change the Type to Evaluate Variable. In the Operator field, select “>” as the operator, and enter 0 in the Value field.

Oracle University and (Oracle Corporation) use only.

d. Right-click the Refresh Customer Count step and select First Step. Click the

“Next step on success” tool on the Package toolbar, and link the step Refresh Customer Count to the step Flag >0? Using the same tool, link the step Flag >

0? to the step Delete Target tables. Click the “Next step on failure” tool on the Package toolbar, and link the step Flag > 0? to the Countries step, as shown below. Click Save to save the package. Close the tab.

Oracle University and (Oracle Corporation) use only.

6. Run the new package and check if the Delete Target Tables step was executed.

a. Right-click the package PKG_14-2, and then select Execute. Click OK in the

Execution window, and then click OK when the “Session started” message appears.

Oracle University and (Oracle Corporation) use only.

b. In the Operator, verify that the latest session called PKG_14-2 appears as complete, with a Delete Target Tables step executed.

c. Open the Designer tab. Open the package PKG_14-2 again, and then select the Diagram tab. To delete records from the target tables, select the Delete Target Tables step, right-click, and select Execute Step. Click OK in the Execution window, and then click OK when the “Session started” message appears.

Oracle University and (Oracle Corporation) use only.

d. In the Operator, verify that the latest session called PKG_14-2 appears as complete, with a Delete Target Tables step executed.

Oracle University and (Oracle Corporation) use only.

e. Now, with records from the target tables having been deleted, execute the entire package PKG_14-2 again. Click OK in the Execution window, and then click OK when the “Session started” message appears.

Oracle University and (Oracle Corporation) use only.

f. Open the Operator and expand the latest session called INT_14-2. Verify that the Delete Target Tables step is no longer executed.

Note: Because the Customers tables were emptied by the execution of the Delete Target Tables step, the value of variable Flag >0? changes when refreshed, to

“false”, and the Delete Target Tables step is no longer executed. Close the PKG_14-2 tab.

Oracle University and (Oracle Corporation) use only.