How to Fix Oracle’s Database Function Issue in ODI Interfaces

We have noticed that in few interfaces, the NVL function is not working as expected when “_simple_view_merging” is set to be true in the Oracle source database.

This problem is encountered if the column corresponding to the first function displays the correct results, but the columns corresponding to other functions return nothing. You can see this when a nested query that has several functions in an outer query block, points to the same function in the inner query block, and “_simple_view_merging” is set to be true.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

You can fix this in the ODI interface by passing the parameter through the ETL_HINT_NESTED variable and setting “_simple_view_merging” to False in the source database.

Here is the fix:
Add the variable ETL_HINT_NESTED before the interface and pass the string “/*+ opt_param(‘_simple_view_merging’,’false’) */ “ into the package of the interface experiencing the issue.