One of the truly awesome and powerful features of Oracle Data Integrator is to develop interfaces against a logical schema, then let ODI choose the physical schema at run time. In other words, we have the same logical job that should work in development, quality assurance, and production environments – and this works without having to create and maintain multiple copies of the same job.

Some environments, however, are less than ideal and don’t have a servers for testing (!). ODI really, really benefits from extensive testing in development, though. So what to do? Create a separate schema on the production server. We can still map out the test context for ODI to this separate schema and more or less approximate having a true testing environment.

What if we need to write an ODI Procedure, though? Obviously our first choice is to create an Interface,  but we need to drop down to procedures here and there for various reasons. Normally a procedure would work just fine in different physical servers because the code being executed, including any schema references, would be the same. This is not true if we have a faux test schema on our production server. We might have CUSTOMERS and our newly created CUSTOMERS_DEV or something similar. If an ODI procedure wants to do an update on a table, for instance, it might look like this:

UPDATE CUSTOMERS.INFO SET fav_color = 'Red'

Our procedure is tied to the particular schema. Again, if we had the same schema name but on different physical servers, this wouldn’t be an issue. But we don’t in this scenario. Let’s update the procedure to pull out the proper schema name from the ODI context so that the procedure works in our quasi-dev and production enviroments:

UPDATE <%=odiRef.getSchemaName()%>.INFO SET fav_color = 'Red'

Tada! In the development context, the schema CUSTOMERS_DEV will be used, and in production, CUSTOMERS will be used. Of course, the ideal solution is to have a full test and production environment, but in a pinch this will allow us to do testing on the same server and ensure that our ODI implementation is well-tested, correct, and robust.