Wednesday, September 22, 2021

ODI - Create a date variable and use sysdate in a package

Usecase: we will create refresh variable which will store sysdate data and use in a map field. We will map source Location table to target location table with ETL_date field and use in a a package.

Source table has:

  • Loc_id
  • Street_address
  • Postal_code
  • City
  • State_province
  • Country

Target table has:

  • Loc_id
  • Street_address
  • Postal_code
  • City
  • State_province
  • Country
  • ETL_date

Implementation Steps:

Step1: Create oracle technology physical and logical schema and context in topology

Step2: create model of the logical schemas and have data stores.

Step3: create a project and create new mapping

Drag and drop source and target tables

Map them auto by name

Step4: create a variable and go to refreshing section and choose schema and write the select query.

Select TO_CHAR(sysdate, 'YYYYMMDD HH24:MI:SS') from dual

Step5:Go back to the mapping and select Target ETL_date field and edit expression and use creates variable.


Step6:  create a new package and drag and drop map and variable to package

Link ok between them

Step7: Go to the mapping and physical tab and choose context and Knowledge modules as below:

LKM for default: LKM SQL to SQL

IKM for target: IKM SQL Control Append

Flow control : False

Step8: create a step scenario and save and run

Step9: check the status from Operator table and also from your target db table.

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...