Using Native Sequences with ODI Interface

In this practice, you first use ODI to define the procedure Create_ORCL_SEQ_FAM_ID. You then execute this procedure, which creates the sequence SEQ_FAMILY_ID in the RDBMS.
Next, you use ODI to create an ODI definition of the native sequence you just created in the RDBMS, using the same name, SEQ_FAMILY_ID.
Finally, you create and execute the interface INT-11-1 to load the TRG_PROD_FAMILY target table by using the new native sequence to generate ID numbers for that table.



Note: Completing this practice is critical for all the following practice sessions.
Your Tasks:
1. Create and execute an ODI procedure that creates an Oracle native sequence.
a. If not connected, connect to the DEV_ODI_REPO work repository (User:SUPERVISOR, Password: SUNOPSIS). Click the Designer tab.


b. In the Projects tab, expand: HandsOnLoads > HandsOn. Right-click Procedures and select New Procedure. Name the new procedure: Create_ORCL_SEQ_FAM_ID. Set the Target technology to Oracle, as shown next. Click the Details tab.




c. In the Details tab, select the Add command button . In the Name field, enter: Create ORCL SEQ. In the Command on target tab, set the Technology field to Oracle. In the Schema drop-down list, select ORACLE_ORCL_LOCAL_SALES. In
the Command panel, enter the command, which creates the following sequence. Click the Save icon to save the procedure. Close the tab. .
Note: This command is provided in the text file 11-1.txt in the following location:c:\LABS\Text.


d. Run the procedure Create_ORCL_SEQ_FAM_ID and verify the result of the execution in Operator.









e. Open SQL Developer and expand: Administrator > Other Users > SALES_DEV >Sequences. Verify that your sequence SEQ_FAMILY_ID was created in RDBMS.


2. Create a new Native Sequence in ODI.
a. Open Designer. Click the Projects tab and expand: HandsOnLoads > HandsOn . Rightclick Sequences, and then select New Sequence.

b. Name the new sequence SEQ_FAMILY_ID. Select the Native Sequence check box. In the schema filed, select ORACLE_ORCL_LOCAL_SALES. Click the icon. In the
window that follows, select the DEVELOPMENT context, and then select the native sequence SEQ_FAMILY_ID. Click OK. Your screen should look as shown next. Save the sequence, and then close the tab.






3. Create a new interface to load the Product Family table by using the Native Sequence.
a. Right-click Interfaces, and select New Interface. Enter the new interface name:INT_11-1. In the Optimization Context field, select Development. Click the Mapping tab.




b. In the Models tab, expand the HSQL_SRC model and Oracle Sales Application model.Drag the SRC_PRODUCT datastore from the HSQL_SRC model to the source area in your interface. Drag the TRG_PROD_FAMILY datastore from the Oracle Sales
Application model to the target area. Click Yes to perform automatic mapping.


c. In the Target Datastore area, select FAMILY_ID, and then click the Expression Editor icon . Expand the Project Sequences node, and then drag the sequence SEQ_FAMILY_ID to the “expression” area. Click OK. Click the Flow tab.






d. In the Flow tab, click Target datastore, and then set IKM Selector to IKM SQL Control Append. In the Options, set FLOW_CONTOL to false, and set DELETE_ALL to true.
Select the Distinct Rows check box. Save your interface. When the Fix Issue Dialog window appears, select Fix. This option enables not checking the FAMILY_ID column for not null.




4. Execute the newly created interface and verify the data in the target table.
a. Execute Interface INT_11-1 as shown next, and then verify execution in the Operator tab.






b. In the INT_11-1 tab, select the Mapping tab, right-click the Target Datastore TRG_PROD_FAMILY, and then select Data. Verify the data inserted in the table. Close the Data Editor window and INT_11-1 tab.