Wednesday, November 9, 2022

ODI 12C - Read multiple files and store them in database table

Usecase: Here, we have multiple csv files from Source team. This usecase is to process multiple files from file location and store into database table.

Lets see how we can achieve this:

Highlevel steps

  1. First write all file names from the source file location to database table using Jython script procedure. Follow my below blog.
  2. Import flat file to odi studio model using a variable for file name. Follow my below blog:
  3. Creat a package to read the file contents one by one and write to dadabase table. Which has been described in the details steps below.

Detailed steps:

Package flow: File Load to Emp Db Table

Step1: first create a parameter table to fetch all the parametes needed to poll the files like file location, mail from, mail to , archive location etc.

Create table poc_parameter_tbl(

Par_Name varchar2(200),

Par_val varchar2(500),

Insert_dt timestamp(6),

update_dt timestamp(6)


Step2: Create a table to store all the file contents:

Create table poc_emp(

EmpId number,

Name varchar2(200),

Dept varchar2(200),

Salary Number


Step3: create model

Step4: create refersh variables

Step5: create procedures

Raise error

Truncate table

Update processed flag

Step6: create mapping

Step6: create package with following sequence to process multiple files one by one:

  • Drag and drop LV_FILE_COUNT
  • Drag and Drop LV_FILE_ITERATOR as set variable and assign to zero
  • Drag and Drop LV_FILE_COUNT as evaluate variable and  >= 0
    • If True, 
      • Drag and drop PROC_Truncate_TargetTable
      • Drag and drop LV_FILE_ITERATOR as set variable and increment with 1
      • Drag and drop LV_FILE_ITERATOR as  evaluate variable and <= #POC.LV_FILE_COUNT
        • If True,
          • Drag and drop POC_FILE_NAME
          • Take mapping FileToDBLoad
          • Proc_Update_Processed_Flag
          • Loop to LV_FILE_ITERATOR where it is crementaing with 1.
    • If False,
      • Take LV_ERROR_MSG as set variable and assign with "No File Found"
      • Call Proc_Raise_Error

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...