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 ODIexp_FF_RT in the Name field. The Code field is filled automatically. Click OK. The newly created ODIexp_FF_RT project now appears in the Projects pane. You have now successfully added a new ODI project.

Right-click the Flat_Files model folder and select Insert Model.

On the screen that follows, enter the values provided in the following table. Click the Reverse tab (don't click the Reverse button). Select Global from the Context drop-down list. Click OK.

Click the Files tab. Set the File Format to Fixed, and then click the Columns tab.

On the Columns tab, click the Reverse button. The following screen appears. Click Yes. The Column Setup Wizard appears.

Point the cursor, and then click the ruler at the beginning of each column as shown below. The wizard marks each column.

After the columns are marked, click each column data and set the Name and Datatype for each column. For names and datatypes of each column, refer to the following table. Click OK when you have finished.

The columns should look as follows. Click OK to save the model. Expand Flat_Files > Flat_Files > SRC_SALES_PERSON > Columns and view the newly created columns.

Note: If the columns for SRC_SALES_PERSON have not been created, edit the newly created datastore: Right-click the SRC_SALES_PERSON datastore, select Edit, and repeat steps 2, 3, 4, and 5.

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_STAGE2 identified by password
default tablespace users temporary tablespace temp;

Verify that user ODI_STAGE2 was successfully created.

Grant connect privileges to the newly created user by executing the following SQL command:

grant connect, resource to ODI_STAGE2;

Verify that this statement processed successfully.

On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list. Create the table by executing the following SQL commands:

CREATE table "TRG_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")
)
/

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

In the Data Server: 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. 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_STAGE2 for the Schema (Schema) and Schema (Work Schema) fields. Click the Context tab. Click the Add new context button . Set Logical Schema to ODI_STAGE2. Click OK.

Click the Models tab in the left panel of ODI Designer, and then click the Insert Model Folder icon . On the screen that appears, enter the folder name as Oracle RDBMS2. Click OK.

Right-click the Oracle RDBMS2 model folder and select Insert Model. On the screen that follows, enter the values provided in the following table. Click the Reverse tab (don't click the Reverse button).

On the Reverse tab, set Context to Global. Click the Reverse button. The following screen appears. Click Yes, and then click OK.

Expand the new Oracle RDBMS2 model folder and expand the Oracle ODI_Stage2 model. Verify that the TRG_SALES_PERSON datastore is successfully reversed.

On the screen that follows, enter the interface name as INTexp_FF_RT. If selected, deselect the Staging Area Different From Target check box. Click the Diagram tab. If the Help screen appears, click OK.

Note: In this example, you use the staging area on the target datastore.

Click the Models tab to drag the source and target to Designer. Drag the SRC_SALES_PERSON.txt datastore from the Flat_Files model into the Sources container. Drag the TRG_SALES_PERSON datastore from the Oracle RDBMS2 model into the Target Datastore container. When Designer asks "Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows.

Note: For the purposes of this tutorial, default mappings are used.

You must set the value for the DATE_UPDATED column in Target Datastore. If not active, click the Property panel button to activate the Property panel. In Target Datastore, select DATE_UPDATED. To the right of the Mapping window, click the Editor icon.

Note: The DATE_UPDATED column can also be set using the CURDATE() function or an appropriate RDBMS function.

In the window that appears, select Oracle for Technology. Click OK. In the Function window of the Expression Editor, expand Date and Time and double-click SYSDATE as shown in the screenshot. Click OK. Select the Active Mapping check box if not selected. The target column DATE_UPDATED is now mapped to the SYSDATE function. Click the Flow tab.

For this interface, you export a flat file directly to a relational table target. The Knowledge Modules required for this are LKM File to SQL and IKM SQL Incremental Update. To import the KMs, click the Project tab in the left panel and expand the Knowledge Modules folder. Right-click Loading (LKM) and select Import Knowledge Modules.

Note: In this example, the generic SQL KM is used. However, specific KMs for the RBMS technology can be used as well.

On the screen that follows, enter ..\impexp in the File import directory field as shown in the following screenshot. The files to import should appear in the Select the file(s) to import window. Press and hold the Ctrl key, and then select IKM SQL Incremental Update and LKM File to SQL. Click OK.

Expand the Loading (LKM) and Integration (IKM) folders, and view each imported KM in the tree view as shown below.

Click the SS_0 (1-FILE GENERIC) datastore. The properties for the source appear below. For LKM, select LKM File to SQL from the LKM drop-down list if not selected. Set the LKM option DELETE_TEMPORARY_OBJECTS to <Default>:Yes as shown below.

Click the Target datastore. Select IKM SQL Incremental Update if not selected. Set the IKM option Flow_Control to No. Set the IKM option DELETE_ALL to Yes. Click Apply.

To test your interface, click Execute. The following screen appears. Retain the defaults and click OK. On the next screen, click OK.

To verify that your interface was executed successfully, you need to open ODI Operator. Click the ODI Operator icon to start ODI Operator. In ODI Operator, expand Date > Today > INTexp_FF_RT > Steps > INTexp_FF_RT, and view the execution results for each step. Double-click Step 12 and click the Execution tab. View the number of rows inserted into the target table. Click OK.

Click the icon to return to ODI Designer. Click OK to save your newly created interface. Click the Projects tab. It is now shown in the Projects tree structure.

Note: If the Unlocking Object window appears, click Yes to automatically unlock the object.

You have now successfully created an ODI project and an ODI interface. You have also successfully executed the INTexp_FF_RT ODI interface to export a relational table to a flat file target.