This discussion is archived
5 Replies Latest reply: Sep 26, 2013 10:17 AM by ParthD RSS

Using native database sequences in ODI

ParthD Newbie
Currently Being Moderated

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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points