This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

TO_DATE in ODI with define variable is error

Hao Nguyen
Hao Nguyen Member Posts: 4 Red Ribbon

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

Tagged:

Answers

  • Marco Fris
    Marco Fris Member Posts: 152 Bronze Badge

    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.