Skip to main content

Data & Intelligence

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.

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

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.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Ashwin Pittampally

10 years of experience doing analysis, design, development and implementation of Oracle Business Intelligence applications and ETL solutions using Informatica, DAC, ODI. Worked extensively on Oracle BIAPPS Implementations, SQL querying and Stored Procedures using PL/SQL. Worked on Functional Areas - HR, Finance, Service and Marketing Analytics, Procurement and Spend Analytics, Supply Chain and Order Management Analytics, Enterprise Healthcare Analytics (EHA) and OHADI (Oracle Healthcare Analytics Data Integration)

More from this Author

Follow Us