ODI Tip: Get Physical Schema from Logical Schema in an ODI variable

When it comes to file paths you do not want to hard-code them because they will need to be manually updated when you migrate from one environment to another.
The best way to retrieve a file path at runtime is to get it from the physical schema that you have configured in the Topology.
You can create an ODI variable and use the following code for its refresh:


select '<%=odiRef.getSchemaName("EXTRACT_FILES_LS","W")%>' 
from dual

where EXTRACT_FILES_LS is the Logical Schema name.

When you migrate to a new environment, the only thing that will need to be updated is the Topology Physical Schema and by refreshing the variable you will have the updated file path.