ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Multiple Files – Single Interface

Some time you want to update a single table from different Flat File Sources.

This post shows how to carry out such logic with the assumption all the file are stored in a single directory and they all have the same structure and file formatting.

I have implemented this using two option

1. Using a Temp Data store

2. Using Procedure.

Variable Used

LOOP USED TO ASSIGN AND INCREMENT  NO OF TIME FLOW HAS TO BE PROCESSED
GET_INTERFACE SOURCE FILE TO BE PROCESSED BY INTERFACE
CHECK USED TO CHECK WHETHER ALL FILES ARE PROCESSED OR NOT AT EVERY LOOP

All the variable are Alphanumeric and Historize property.

Using Temp Data store

Temporary Interface – FILE_LIST

Get the list of files in the Temporary Datastore  in Work schema, so that we can process Interface sequentially based on the list of files.

image

Next Step is to Assign the variable  ‘ LOOP ‘ = 0 (Zero )

image

image

Next Step is to get the File Name to be processed the Interface . Refresh the Variable.

image

image

image

Apply the required query so it fetches the next record in the loop.

Here iam getting the ‘ File name ‘ to be processed based on the Loop Number so the First loop will fetch the First file , second loop next file and  keep doing so for others.

SELECT  FILE_NAME FROM <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST
WHERE S_NO=#LOOP

The Next step is to use that File and process the Interface. Here in the Model i have used the  ‘ Resource Name – #GET_INTEFACE ‘ as i want to get  the right file for processing.

image

image

image

Next Step is to Refresh the Variable ‘ Check ‘ ,which checks if all the interface is been processed or not.

image

image

In this Step iam checking whether all the interface is been processed or not.

image

image

The logic is pretty simple to subtract the Loop value from the Total count , so if i have  10 files  in the first loop i would have

Count(*) = 10

Loop        = 1 so remaining interface 10-1 =9 to be process and when its Zero i stop the Flow .

SELECT COUNT(*)-#LOOP  FROM  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST

Next Step is to increment the Value of  ‘ LOOP’  as there are still interface to be processed.

image

image

This is summary of  the overall flow.

1. Get the File list into the Temporpary Datastore ( ‘FILE_LIST’)
2. Assign the Loop to Zero
3. Get the Next File to processed.
4. Process the Interface by passing the value from #GET_INTERFACE  variable
5.  Refresh the Variable Check
6. Check if there are still  Files to be processed or not. If so Next step , else Exit .
7. Increment the Loop by 1.

image

Using ODI Procedure

You can perform the above logic , using an ODI Procedure , instead of a temporary datastore.

I personally would prefer this method as its easy to maintain and modify when required.

The steps are very simple and as follows.

1. Drop the Temporary Table.
2. Create the Temporary table in the Work Schema.
3. Insert the Required File and Values.

image

image

image

DROP TABLE <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST

image

CREATE TABLE <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST
(S_NO NUMBER(3),
FILE_NAME VARCHAR2(50))

image

INSERT ALL
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST VALUES (1,’FILE1.TXT’)
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST VALUES (2,’FILE2.TXT’)
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST VALUES (3,’FILE3.TXT’)
SELECT * FROM dual

image

[ Note : Please note its a best practise to use a  unique work_schema and use in all the Physical schema defination . I have used a common work_schema called ‘ WORK_SCHEMA’  , so when i create all the temporary data store or procedure they are all in the Temporary work schema ‘ WORK_SCHEMA’ . The other good point will be that if any error occurs during the Interface  and due to some reason the temporary objects like C$_,I$_ etc dont get dropped and they will be seen in the main table.]

8 Comments

  1. hi devendra pls help me to solve multiple xml files in single target table with same structure using odi 11g in google also dnt find the multiple xml files so pls help to sove this task

  2. Hi, I’m reading with interest the sample you demonstrated on “Multiple Files – Single Interface”. I was wondering if you could export that example? I would like to understand it further. Thank you.

  3. Hi Devendra,

    Can you please explain about the intermediate interface (PROCESS_INTERFACE) ?
    Which model do we need to consider for the source datastore and what about the reverse engineering?

  4. Hi,
    This post was vry helpful..is there anyway to get multiple file from single interface. i.e my file should contain only 500 line, if my source table contains 5000 records then 10 files need to be created, each file containing only 500 rows of records. How this can be done using ODI.

  5. hi.. i want to execute a package /scenario in more than 1 context.using file wait..

  6. Multiple Files – Single Interface was very good.

    I have a scenario where I want to execute a single interface for multiple projects. for an example.

    For Project PRJ1

    suppose I have designed a interface INT1 which have source & target oracle with S1 & T1
    S1 model tab have logical schema LS1 & Target T1 has logical schema LT1.

    PRJ2 Project :

    Now i have another project with oracle target & source S2 & T2. S2 model tab have logical schema LS2 & Target T2 has logical schema LT2.


    S1 & S2 source have same structures of tables& similarly T1 & T2 Target have same table structure.

    Now I want use the same interface INT1 for project2 (S2 & T2) as i have already told that table structure in same both in project PRJ1 & PRJ2.

    How can we use the single interface for multiple projects.

    I can we use the single interface Dynamically for multiple projects. I want to avoid create two interfaces.

  7. Thanks you for all the good articles and good posts…they have helped me to tackle a lot of my day to day complexities using ODI….

Leave a Reply

Required fields are marked *.