5 Replies Latest reply: Sep 26, 2013 12:17 PM by ParthD RSS

    Using native database sequences in ODI

    ParthD

      Hi,

       

      I am using ODI 11.1.1.7.0 on windows 64 bit machine.

       

      I have written the following queries:

       

      create table sequence_src (EmpName varchar(20), salary int);

      create table sequence_tgt( CustId int, CustName varchar(20));

       

       

      create sequence emp_sequence

      minvalue 101

      maxvalue 999

      start with 101

      increment by 1;

       

       

      insert into sequence_src values('abc',1000);

      insert into sequence_src values('sdsd',2000);

      insert into sequence_src values('ress',3000);

      insert into sequence_src values('zzx',4000);

      insert into sequence_src values('dsw',5000);

       

      commit;

       

      So as you see i have created sequence named emp_sequence in my database

       

      When i map the target column i.e EmpID with the following implementation

       

      EMP_SEQUENCE.NEXTVAL

       

      I get an error when the interface runs saying  " EMPID" is an invalid identifier on target connection oracle

       

      how to solve this error?

       

      can anyone tell how to use database native sequences in ODI       

       

       

      EDIT: This is complete error log

       

      ODI-1228: Task native_sequence (Integration) fails on the target ORACLE connection all_oracle11g_server.

      Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "EMPID": invalid identifier

       

       

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)

        at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)

        at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)

        at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)

        at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)

        at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)

        at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)

        at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)

        at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)

        at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)

        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)

        at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)

        at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)

        at java.lang.Thread.run(Thread.java:722)