MULTIPLE FILES - SINGLE TARGET TABLE-SINGLE INTERFACE

Let me share very important concept of ODI called using Variable as Resource name.

Suppose if we have multiple files of same structure to be loaded into a single target table, then we need not to use multiple interfaces.

Instead we can use a single interface to load all the flat files of same structure into the single target table.

I am going to try to explain the procedure with following steps

Suppose if we have three Flat files namely EMP1.txt, EMP2.txt and EMP3.txt to be loaded into TRG_EMP table of ORACLE database.

Before going to do this, you need to learn first how to load a single source flat file to a target table of oracle database.

To do the above please go through the following link provided by oracle by examples

ODI11g: Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table

Using the above procedure load the EMP.txt file to

EMP table .

you can download the required files from the following link

Download source files

Now follow the following steps.

First we need to create a table using the following query

CREATE TABLE SRC_FILE_DETAILS

( FILE_NAME VARCHAR2(10 BYTE)

);

Then load the required file names into this table.

INSERT INTO src_file_details values 'EMP1'

INSERT INTO src_file_details values 'EMP2'

INSERT INTO src_file_details values 'EMP3'

Now create three variables with the following deatails

1) Name: count

Data type: Numeric

Action : latest Value

2) Name: Files_Count

Datatype: Numeric

Action: latest Value

Refreshing: select count(*) from src_file_details

3) Name: FILE_NAME

Datatype: Alphanumeric

Action: Latest value

Refreshing:

SELECT FILE_NAME FROM (SELECT FILE_NAME,ROWNUM RN FROM SRC_FILE_DETAILS) WHERE RN=#Project_Name.count

Note: Please replace Project_Name with your project name

Now open the source data store which is participating in the interface for populating target and replace the resource name with #Project_Name.FILE_NAME.txt

Now we are going to design a package looks like below:

Step1:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Step2: Drag and drop the FILE_NAME variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Step name: GetTheFileName

Type: Refresh variable

Step3:

Just drag and drop the interface and change the step name to PopTrgWithThisFileName

Step4:

Drag and drop the count variable.

Click on it. Change the properties as shown in the following

Step5:

Drag and drop the Files_Count variable.

Click on it. Change the properties as shown in the following

Step type : Refresh Variable

Step6:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Drag and drop the Files_Count variable.

Click on it. Change the properties as shown in the following

Step type : Refresh Variable

Step6:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Please replace Proj_Name with your project name

Save all

Run the package

That it.

Now life is so easy.

You can load a table with several files with single interface.