Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
TO_DATE in ODI with define variable is error

Hi All, please help me
i installed ODI ver 12.2.1.4 but got 1 error, when i declare variable using TO_DATE function, But with ver 12.2.1.2.0 there is no error.
My command is: You 7/27/2022 10:16 AM • select TO_DATE('20220101', 'YYYYMMDD') FROM DUAL.
Below is the error message.:
ODI-1228: Task Variable-ABC- fails on the target connection SB_ODS_AML.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:510)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:462)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1105)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:551)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:656)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:230)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:42)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:766)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1044)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1243)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1456)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:370)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:196)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:204)
at oracle.odi.runtime.agent.execution.sql.RefreshVariableExecutor.execute(RefreshVariableExecutor.java:55)
at oracle.odi.runtime.agent.execution.sql.RefreshVariableExecutor.execute(RefreshVariableExecutor.java:27)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
at oracle.odi.runtime.agent.execution.VariableSessionTask.processTask(VariableSessionTask.java:192)
at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:128)
at oracle.odi.runtime.agent.execution.VariableSessionTask.doExecuteTask(VariableSessionTask.java:164)
at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:886)
at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2225)
at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:610)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
at java.lang.Thread.run(Thread.java:750)
Caused by: Error : 936, Position : 15, Sql = select TO_DATE(@(string),'DD-MON-YYYY HH24:MI:SS') FROM DUAL, OriginalSql = select TO_DATE(@(string),'DD-MON-YYYY HH24:MI:SS') FROM DUAL, Error Msg = ORA-00936: missing expression
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:514)
... 32 more
Answers
-
That "@(string)" isn't a valid sql string.
Also, I would not recommend using ODI variables of date datatype. Those are Java datatypes, and converting into something you can use in sql is not trivial.
Better to use string variables for dates and use to_char and to_date functions where needed.