Calling Oracle sequence.nexval from Informatica

[AINSOF]

Hi,
I created an Oracle sequence, and want to use this sequence on a field in my target table. I will like to know if Informatica can call an oracle sequence and how to make the call if it can. WE do not want to use Informatica sequence generator.

Your help is appreciated.

Thanks
AIN

 

[Variation180]

For our systems we would call the sequence from a trigger on the table. We however, have many processes hitting our tables at the same time (i.e. informatica, plsql and vitria). This way, regardless of what process we are using, the sequence generator is called by the table on insert or update. I don't know if that will help, but for what its worth....

Thanks,
Chris

 

[AINSOF]

I created the following trigger to automatically generate sequence number on the RUN_DATE_ID field of EIC_RUN_Date table.

CREATE OR REPLACE TRIGGER RUN_DATE_ID_TRIG
before INSERT on EIC_RUN_DATE
for each row
BEGIN
Select RUN_DATE_SEQ.NEXTVAL
into:NEW.RUN_DATE_ID
From dual;
END RUN_DATE_ID_TRIG;

EIC_RUN_DATE table definition:
RUN_DATE_ID NUMBER (25),
START_RUN_DT DATE,
LAST_RUN_DT DATE ) ;

When I use Informatica to populate the START_RUN_DT and LAST_RUN_DT I get the following error:

WRITER_1_1_1> WRT_8229 Database errors occurred:
ORA-26086: direct path does not support triggers

Please any ideas on how to resolve this problem will be appreciated.

Thanks
AIN

 

[Variation180]

You will not be able to use the trigger if you are using an append or direct load option. You can call the oracle sequence by creating a function in oracle to call it. Then you can call the function with a stored procedure transformation. Sorry for the confusion.

 

[AINSOF]

Thanks Variation180.

AIN