ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Oracle to Flat file

This is the Sample of source Table to be populated as Flat File.

Target [Flat File – region.dat]

1. Go to the Physical architecture of the Topology and insert a New Data Server under File Technology

2. For the JDBC Driver choose the " Sunopsis File JDBC Driver "

3. Define Physical schema by entering the Directory as shown in the image for Windows or define the Directory path of the Unix environment. ( Make sure your agent should be having permission for that folder and file )

For windows enter the path with " /" even though windows shows directory with " "

4. Link to the Logical Schema

5. Define your Target model with the required Logical Schema defined above .

6. Next Step is to define the columns and make sure all the data types are defined as string even for dates.

7. Comparing to the source , I have defined Target as with Two columns region_id and region_name with data type string.

8. Next step is to define the Properties of the File to be populated.

( Note – The most important part of File property is how  we define  them i.e  how the Records are stored and how ODI can categorize Columns while reading from the File ]

9. Next step is to define the Interface , since our target is a File we have to check mark the option " Staging area different from target " and selecting either the source or other schema which you want to use as the Staging schema , but preferably Source Schema.

10. Select the required LKM if the staging is different from the Source and " IKM Sql to File Append" . Apply and Execute .

11. Enable the option " GENERATE_HEADER " if you wish to populate the data with Headers.

12. Finally your file is created with the data and headers in it.

[Tip : – If you wish not to create a new data server for File Technology , you can use the default data server " FILE GENERIC " , the path is to store and create file under FILE_GENERIC  is  ‘ ODI_HOMEoracledidemofile ‘ . ]

5 Comments

  1. We’re struggling to get going in ODI with the simple stuff so we thought we’d give you a try.

    I think it all boils down to two questions:-

    Number 1 – directories
    What we thought we’d do is keep our physical and logical definitions to a minimum to keep the projects as simple as possible (as we’re all new to it here and want to reuse the same logical and physical definitions in lots of projects), this is giving us some headaches when it comes to directories and files for unloading/loading tables.

    Not sure if this is advisable but we’d like to have a directory that we can use with several different subdirectories referenced under it for different purposes but not to have lots of logical definitions for each sub directory. We’d just like to have one logical definition of the “root” dir like:-

    /uxx/{project_name}/

    with several sub directories that don’t change between contexts, which we can “hard” code in the packages as needed:-
    Audit_logs
    Source_files
    Validation_logs

    With this setup we can switch from different dev/test/ live file structures for different contexts.

    Is this possible or do we need to create lots of physical/logical definitions for each directory?

    Number 2 – files
    Within the different packages we’d like to use variable names to reference different files for input and output.
    E.g
    An input file in the “source_files” sub dir is defined in a control table so we’d like to extract it out to a variable and use it in an interface.
    An output file is written to the “Audit_logs” sub dir but has a variable name like this:-
    Audit_log(__#SessTime).csv
    Where #sesstime is a variable we are setting from a specific database query to get a date/time we need to use.

    The naming of these inputs/outputs seems to be prescribed in the model definitions.
    Do we have to describe these names in the models (is this possible) or can we set them in the interfaces/procedures/commands?
    (I had been using three steps to create my audit logs (odiSqlUnload commands for header and data with a merge command to put them together) where the target file name is as above but we’ve had some help to change this to use an interface instead but can’t quite get the hang of how to set the file name how we’d like.)

    Any pointers you can give us most appreciated.

  2. Hi,

    I don’t see any links to download this LKM.
    Where to find it?

  3. I am unable to write a CLOB to a file. Do you have any solutions?

  4. Hi
    This is very useful..! I have some other requirement..
    While loading to a flat file i want to convert the columns into rows and rows to columns. Does ODI support the Pivot and UnPivot

    what is the best way to do this. I have done this using a procedure. any other ways…!

    Thanks in Advance,
    Mansur

Leave a Reply

Required fields are marked *.