My Oracle Support Banner

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:

Oracle Data Integrator - Version and later
Oracle Data Integrator on Marketplace - Version and later
Information in this document applies to any platform.


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.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.