In the window that opens, click Projects tab, and then click the Insert Project button to add a new project.

In the screen that opens, set the Name of the project to ODIexp_RT_FF in the name field. The code field will be filled automatically. Click OK. The newly create project ODIexp_RT_FF now appears in the Project tree view. You have now successfully added a new ODI project

In Oracle Database Express Edition Home screen open the SQL drop-down menu and select SQL Commands > Enter Command.

You 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 you want to create;
<MY_PASS> corresponds to the password you have given to it;
<MY_TBS> corresponds to the Oracle tablespace where the data will be stored;
<MY_TEMP> corresponds to temporary default tablespace;

To create user enter the following command. Click Run.

create user ODI_STAGE1 identified by password 
default tablespace users temporary tablespace temp;

Verify that user ODI_STAGE1 was successfully created.

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

grant connect, resource to ODI_STAGE1;

Verify that this statement processed successfully.

In Oracle Database Express Edition Home screen open the SQL drop-down menu and select SQL Commands > Enter Command. You create the table by executing the following SQL commands:

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 "TRG_SALES_PERSON_PK" primary key("SALES_PERSON_ID")
)
/

To populate the SRC_SALES_PERSON table, go to SQL Commands window, enter the commands from the table below. Click RUN and then, verify that statements processed successfully.

Note: You can copy this script from the text file SRC_SALES_PERSON_DATA, provided with this OBE. To download this file click here.

begin
insert into ODI_STAGE1.SRC_SALES_PERSON values
(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(20,'Jennie','Daumesnil','28/02/1988',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(51,'Cynthia','Nagata','28/02/1994',sysdate);
insert into ODI_STAGE1.SRC_SALES_PERSON values
(52,'William','Kudo','28/03/1993',sysdate);
end

Verify that table SRC_SALES_PERSON populated with data successfully by executing the statement provided below.

Note: The data must be loaded correctly before proceeding to the next step.

select* from ODI_STAGE1.SRC_SALES_PERSON

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

In DataServer: New window enter the values provided in the table below. Click JDBC tab.

Click for JDBC driver. Select Oracle JDBC Driver. Click OK. Click for 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 to the JDBC Url field! That may cause problems with entering valid url string.
• You may need to enter appropriate driver and URL for your RDBMS.

On the Physical Schema screen, select ODI_STAGE1 for Schema (Schema) and Schema (Work Schema) fields. Click Context tab. Click Add new context button . Set logical schema to ODI_STAGE1. Click OK.

Click the Models tab within the left panel of ODI Designer, and then click Insert Model Folder icon . In the screen that follows, enter the folder name: Oracle RDBMS1.

Right-click Oracle RDBMS1 model folder and select Insert model. In the screen that follows, enter the values provided in the table below. Click Reverse tab ( don't click Reverse button!).

In the Reverse tab, set Context to Global. Click Reverse. The following screen will appear. Click Yes, and then click OK.

Expand new Oracle RDBMS1 model folder, expand Oracle ODI_Stage1 model. Verify that the datastore SRC_SALES_PERSON have been successfully reversed.

Right-click Flat Files model folder and select Insert model.

In the screen that follows, enter the values provided in the table below. Click Reverse tab ( don't click Reverse button!) Select Global context. Click OK.

In this OBE, you will use the sample files delivered with the installation of ODI. Browse to the ODI_HOME\demo\file directory. Make a copy of the SRC_SALES_PERSON.txt file and name the copy TRG_SALES_PERSON.txt, as shown in the example on the screenshot below.

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

In the Columns tab click Reverse button. The following screen will appear. Click Yes. The Column Setup Wizard will appear.

Drag the mouse, click at the beginning of each column and the wizard will mark each column.

Once the columns are marked, click on each column data and set the Name and Data Type for each column as shown on the screenshots. For names and datatypes of each column refer to the table below. Click OK when done.

The columns should look as follows. Click OK to save the model. Expand Flat Files > Flat Files > TRG_SALES_PERSON > Columns and view the columns. Expand Oracle RDBMS1 >Oracle ODI_STAGE1 > SRC_SALES_PERSON > Columns folder. View your source and target models.

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

In the screen that follows, enter interface name INTexp_RT_FF. Select the "Staging Area Different From Target" check box and select ODI_STAGE1 from drop-down menu. Click Diagram tab. If Help screen appears, click OK.

Note: In this example you use the ODI_STAGE1 (the source) as the staging area instead of target file.

Click the Models tab to drag and drop the source and target to the designer. Drag the SRC_SALES_PERSON datastore from the Oracle RDBMS1 into the Sources container. Drag theTRG_SALES_PERSON.txt datastore from the Flat Files model into the Target Datastore container. When Designer asks "Do you want to perform and Automatic Mapping?", click Yes. The Diagram tab should look as follows. Click the Flow tab.

For this interface, you export a relational table into a flat file target. The KM needed for this is the IKM SQL to File Append. To import the KM, click the Project tab in the left panel and expand Knowledge Modules folder. Right-click Loading (LKM) and select Import Knowledge Modules.

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

In the screen that follows, enter ..\impexp in the File import directory field as shown on the screen below. The files to import should appear in the Select the file(s) to import window. Select IKM SQL to File Append. Click OK.

Expand the Integration folder and view each imported KM in the tree view as shown below. Click the Flow tab.

Click the Target (FI LE_GENERIC). This will make the properties for the target appear below. For the IKM, click the IKM drop-down menu and select the IKM SQL to File Append. Set the IKM option TRUNCATE to Yes, as shown below. Click Apply.

To test your interface click Execute. The following screen will appear. Leave the defaults and click OK. On the next screen click OK.

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

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

Note: If Unlocking Object Window appeared, click Yes to automatically unlock object.

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