Forum Stats

  • 3,814,528 Users
  • 2,258,880 Discussions
  • 7,892,767 Comments

Discussions

ODI 12c - Retrieve target table name from mapping and save it to variable

kovar
kovar Member Posts: 44 Red Ribbon
edited Feb 13, 2018 4:58AM in Data Integrator

Hi,

I have a ODI package with sequentially ordered mappings inside it. What is the best way to retrieve target table name of each mapping (one after another) and save it into ODI variable?

I would use that target table name in variable each time new mapping starts and forward it to my stored procedure on database.

Thanks in advance!

- Kovar

Tagged:
kovar

Best Answer

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 29, 2018 1:08AM Answer ✓

    You don't need to rename it, you can simply use substr or replace function in refresh statement

    select replace(substr('M_MYTABLE_INSERT',3),'_INSERT','') from dual

    Or with substitution api

    select replace(substr('<%=odiRef.getPrevStepLog("STEP_NAME")%>',3),'_INSERT','') from dual

    Your variable finaly return table name

    kovarkovar

Answers

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 28, 2018 2:55PM

    Hi !

    You can simply name steps like a target table name, for example T_ORDER_STATUSES, and on the next step get it from refresh variable:

    <%=odiRef.getPrevStepLog("STEP_NAME")%>

    Other way is select from ODI work repository (using refresh variable)

  • kovar
    kovar Member Posts: 44 Red Ribbon
    edited Jan 28, 2018 3:58PM

    Hi rpc1,

    thank you for your response. Your idea with STEP NAMES is a nice tip. My mapping names look like this: M_<table_name>_INSERT , it would probably take some time to rename them in package but its feasible. Also, working with SNP_ tables is another option that I had in mind in order to solve this situation.

    - Kovar

  • Pavan8u
    Pavan8u Member Posts: 278 Silver Badge
    edited Jan 28, 2018 11:38PM

    Hi,

    You can assign a value to the variable before/after the interface in the package and use that variable to send it to your db.

    1) Employee interface

    2) VARIABLE_ASSIGN = EMPLOYEE

    3) call your db

    Regards,

    Pavan

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 29, 2018 1:08AM Answer ✓

    You don't need to rename it, you can simply use substr or replace function in refresh statement

    select replace(substr('M_MYTABLE_INSERT',3),'_INSERT','') from dual

    Or with substitution api

    select replace(substr('<%=odiRef.getPrevStepLog("STEP_NAME")%>',3),'_INSERT','') from dual

    Your variable finaly return table name

    kovarkovar
  • kovar
    kovar Member Posts: 44 Red Ribbon
    edited Jan 29, 2018 5:58AM

    Nice, thanks! This would work pretty well with packages. Do you think this approach is applicable in Load Plans with Parallel steps?

    load_plan_oracle_community.png

    - Kovar

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 29, 2018 6:41AM

    Yes, I think it will work fine, because it executes in session scope.

  • 2957944
    2957944 Member Posts: 60
    edited Jan 30, 2018 12:53AM

    Hi Kovar,

         What rpc said was perfect, if you want to use with in Load plans, you have to configure the variable inside load plans once again.

This discussion has been closed.