TO_DATE in ODI with define variable is error

Hao Nguyen

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

Marco Fris

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.