ODI 12C - Write file names from OS location to database

Usecase: Here we will use a procedure and jython script to take file names from OS location or mount location and write them to a database table. 

Step1: Create a database table to store the file names and other params

Create table poc_param_tbl (

SNO NUMBER,

FILE_NAME VARCHAR2(500),

PROCESSED_FLAG VARCHAR2(1),

INSERT_DT TIMESTAMP(6)

UPDATE_DT TIMESTAMP(6)

);

Step2: create database physical and logical architecture connection under Topology:

Step3: create a project, a variable and create a new procedure with 2 tasks - to trucate the table and to run the jython code.

Jython script:

import os

import datetime

sc=odiRef.getJDBCConnection("SRC")

cnt=0

filepath="#POC.LV_EPM_OBJ_STORAGE_SRC_FOL_PATH"

ct=datetime.datetime.now()

setFileNameQuery="insert into POC_param_tbl(sno, file_name,insert_dt,update_dt,processed_flag) values(?,?,?,?,?)"

for files in os.listdir(filepath):

   if files.endswith(".csv"):

      cnt =cnt+1

      sqls=sc.prepareStatement(setFileNameQuery)

sqls.setInt(1,cnt)

sqls.setString(2,files)

sqls.setTimestamp(3,ct)

sqls.setTimestamp(4,ct)

sqls.setString(5,"N")

executeStatement=sqls.executeUpdate()

sqls.close()

Notes:

1. Suppose any patching is done to the server where ODI studio installed, then we need to clear the cache and restart odi studio otherwise it will fail with "Script Engine not present error"

Path:

Cd /u01/oracle/mwh/odi/studio

./odi.sh -clean -initialize

2. Here, we have hardcoded the sourcr path in the project variable, that we can use as refresh variable and fetch from the database table.