Data Integration Tips: Oracle Data Integrator - Quotes in Variables

Michael Rainey -

It's Sunday night, and we have just enough time for another quick Data Integration Tip from Rittman Mead. In another recent, yet somewhat trivial, challenge I ran into this week was the ability to pass quotations through to a Scenario as a part of an ODI 11g Variable (this is also applicable to ODI 12c). Let's get right into the example.

To set the stage, I'm working on project that includes integration with Hyperion Planning. This specific task is a command line call to the CalcMgrCmdLineLauncher.cmd to execute a specific business rule against the data in the Planning application. In this case, our business rule name contains spaces and, for various reasons, the name cannot be changed to remove the spaces. The ODI 11g project is setup to run many of these planning business rules by calling a generic Scenario, based on a Package, passing variables to make it dynamic.

The command for the OdiStartScen ODI Tool in the calling Package looks like this:

OdiStartScen "-SCEN_NAME=UTIL_EXECUTE_CALCMGR" "-SCEN_VERSION=001" "-SYNC_MODE=1" "-EPM.PV_PLANNING_CALCMGR_RULE=Revenue and Burden All Contracts"

As you can see, there are double quotes around each of the parameters and their values being passed into the ODI Tool, including the Scenario name and version, the synchronous flag, and additional variables such as the PV_PLANNING_CALCMGR_RULE. What I found was that the Scenario had issues accepting the ODI variable value with spaces.

To work around this issue, I simply added an escape to the double quotes around the variable value with spaces. The double double quotes allow the " to be passed through as a part of the variable string value.

OdiStartScen "-SCEN_NAME=UTIL_EXECUTE_CALCMGR" "-SCEN_VERSION=001" "-SYNC_MODE=1" "-EPM.PV_PLANNING_CALCMGR_RULE=""Revenue and Burden All Contracts"""

Now, the Scenario UTIL_EXECUTE_CALCMGR can accept the variable value with spaces, and the necessary double quotes, and pass it on through to the CalcMgrCmdLineLauncher.cmd command.

So there you have it, another Data Integration Tip from the folks at Rittman Mead. Simple as it was, I hope you find it useful! Check back regularly for more DI Tips and have a great week!