Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.7K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
ODI 12c - Retrieve target table name from mapping and save it to variable

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
Best 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
Answers
-
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)
-
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
-
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
-
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
-
Nice, thanks! This would work pretty well with packages. Do you think this approach is applicable in Load Plans with Parallel steps?
- Kovar
-
Yes, I think it will work fine, because it executes in session scope.
-
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.