ODI Variable Refresh Query That Incorporates a Reserved Word in the Select Query Returns a Different Result in ODI 10g Compared to ODI 11g when Schema is Sunopsis Memory Engine (Doc ID 1550678.1)

Last updated on OCTOBER 13, 2023

Applies to:

Symptoms

A Refresh Variable has been defined that incorporates a reserved word in the SQL SELECT query.

When this SELECT is executed in Oracle Data integrator (ODI) 10g compared to ODI 11g, the results differ.

Test case steps

1. Create the table in HSQL database (which is used by the ODI Memory Engine technology):
        
   
             
2. Insert into the table values retrieved from "Command on Source":
   
   
   
   
                
3. Now refresh a Variable where the query retrieves the results from the table insert:
       
   

The results will differ in ODI 10g compared to ODI 11g:

• In ODI 10g, the query will return the value held in the table created earlier. In this case 'system' . The value from Technology Oracle as expected.
            
• In ODI 11g, the query will return the value 'SA' . The user of the Data Server MEMORY_ENGINE.

Changes

 Environment upgraded from ODI 10g to ODI 11g.

Cause