4

I have a scalar function in my source database schema which I am using it to get balance of the specified account.

NVL(MYGL.F_GET_LAST_ACC_BALANCE(ACCOUNTS.ACC_NO, TRUNC(SYSDATE), 1),0)

In ODI mapping I would like to use this function to get balance of the accounts and then load them into my DWH DB.

I am using IKM Oracle Insert with DbLink.

I tried to use EXPRESSION inside mapping however that didn't give me the result because ODI tries to execute MYGL.F_GET_LAST_ACC_BALANCE on DWH side not on the source side. MYGL.F_GET_LAST_ACC_BALANCE is defined in Source DB.

This is the generated query

INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO DWH.FACT_ACCOUNTS
  (
    REPORT_DATE ,
    ACC_NO ,
    ACC_PROD_CODE ,
    REAL_BALANCE ,
    LAST_BALANCE 
  ) 
SELECT 
  TRUNC(SYSDATE) ,
  ACCOUNTS.ACC_NO ,
  ACCOUNTS.ACC_PROD_CODE ,
  ACCOUNTS.ACC_BALANCE ,
  (NVL(MYGL.F_GET_LAST_ACC_BALANCE(ACCOUNTS.ACC_NO, TRUNC(SYSDATE), 1),0))  
FROM 
  <schema>.<table>@<dblink_to_source> ACCOUNTS

What it needs to be

INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO DWH.FACT_ACCOUNTS
  (
    REPORT_DATE ,
    ACC_NO ,
    ACC_PROD_CODE ,
    REAL_BALANCE ,
    LAST_BALANCE 
  ) 
SELECT 
  TRUNC(SYSDATE) ,
  ACCOUNTS.ACC_NO ,
  ACCOUNTS.ACC_PROD_CODE ,
  ACCOUNTS.ACC_BALANCE ,
  (NVL(MYGL.F_GET_LAST_ACC_BALANCE@<dblink_to_source>(ACCOUNTS.ACC_NO, TRUNC(SYSDATE), 1),0))  
FROM 
  <schema>.<table>@<dblink_to_source> ACCOUNTS

enter image description here

Derviş Kayımbaşıoğlu
  • 24,022
  • 2
  • 42
  • 59

1 Answers1

0

Probably what you need it's too custom for using a predefined Oracle KM.

I think that a solution it's to use another IKM, that it's not using DBLINK, instead it's using a JDBC connection (connect directly to source, through the jdbc defined in the Physical schema).

In this way, your code will be executed directly on the source and the function will be recognized.

F.Lazarescu
  • 1,342
  • 2
  • 13
  • 26