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 18.104.22.168.0 and later
Oracle Data Integrator on Marketplace - Version 22.214.171.124.200123 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:
- 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
- Execute the above SELECT in SQLPlus or SQLDeveloper
--> It succeeds.
- 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"
- Refresh the Variable in ODI Studio
--> This also succeeds.
- 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."
- 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
- 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!