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. https://soalicious.blogspot.com/2022/10/odi-12c-write-file-names-from-location.html
  2. Import flat file to odi studio model using a variable for file name. Follow my below blog: https://soalicious.blogspot.com/2022/10/odi-12c-importing-flat-file-to-odi.html
  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_EPM_OBJ_STORAGE_SRC_FOL_PATH
  • 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...