ODI 12c Variables using Colon Punctuation Mark ( : ) In the Refresh Statement Fails when Executed From the"Query Execution Plan">"Run SQL Code", While the Variable Refresh Itself Succeeds (Doc ID 2987393.1)

Last updated on NOVEMBER 17, 2023

Applies to:

Symptoms

Having an Oracle Data integrator (ODI) 12c Variable which contains one or more colons ( : ) in its value, such as, for example: NOV-10-2023 10:25:11 

Refreshing the Variable value succeeds.

However, the same SELECT command fails when executed from ODI Studio > Operator > the Session> Sessions' Task > "Code" tab, >"Query/Execution Plan"<

The error message received is:

Steps to replicate the behavior:

1.  Have a simple ODI Variable (of Alphanumeric or Text datatype),and refresh using the following SELECT command (on Oracle technology):
   

     SELECT to_char ( sysdate,'DD-MM-YYYY HH24:MI:SS ' ) FROM dual 

2. Execute the above SELECT in SQLPlus or SQLDeveloper
   --> It succeeds.  
      
3. In ODI Studio > Designer, edit the Variable, and go to the "Refreshing" tab.
   
   Click the green swoosh (Testing Query in the DBMS)
   --> Result is: "Valid query"
     
4. Refresh the Variable in ODI Studio
   --> This also succeeds.
    
5. Edit the Variable task from the ODI Session created during the Variable Refresh.
   On the Sessions' Task > "Code" tab, click the "Query/Execution Plan" button.
   In the "Requester" pop-up window, click the "Run SQL Code" button.
   --> Result is: "Execution of query failed."
      
6. Replace the colon punctuation mark (:) in the SELECT command with anything else. For example:

      SELECT to_char ( sysdate,'DD-MM-YYYY HH24-MI-SS ' ) FROM dual 

7. Repeat step #5 above, and observe that this executes successfully.

Changes

Cause