I had seen couple of infra project implementation wherein they have used Oracle Sequence Generator in informatica [with the help of SQL tranformation] rather than using Informatica Sequence Generator transformation.
I could not find any promising reason of not using Sequence Generator Tranformation, apart from one which is for Push Down Optimization. I never tried Push Down optimization with Oracle Sequence Generator [Instead of informatica sequence generator tranformation], but I feel it may helpful in that scenario.
Please find below guidelines to implement\use Oracle Sequence Generator tranformation in your informatica mapping.
1. First create a sequence generator in your oracle schema. Here, I have created oracle sequence called mySequence in hr schema.
SQL> create sequence mySequence
2 start with 1
3 increment by 1
4 nocache;
Sequence created.
SQL>
2. Verify your sequence generator if its working or not
select mySequence.nextval from dual;
select mySequence.currval from dual;
3. As we are ready with oracle sequence generator, we will go ahead with mapping creation.
Our mapping will be very simple...
a. Source Qualifier
b. SQL Tranformation
c. Target
4. In SQL Tranformation we will add ports as below
a. same input ports which are coming from source
b. One extra output port which is attached with sequence generator query
5. Connect all ports from Sequence Generator tranformation to Relational Target. Once we do that we are done with the mapping.
6. Create workflow which is connected with this mapping and run the workflow. Verify the records and at the same time current value of the sequence you have created.
I could not find any promising reason of not using Sequence Generator Tranformation, apart from one which is for Push Down Optimization. I never tried Push Down optimization with Oracle Sequence Generator [Instead of informatica sequence generator tranformation], but I feel it may helpful in that scenario.
Please find below guidelines to implement\use Oracle Sequence Generator tranformation in your informatica mapping.
1. First create a sequence generator in your oracle schema. Here, I have created oracle sequence called mySequence in hr schema.
SQL> create sequence mySequence
2 start with 1
3 increment by 1
4 nocache;
Sequence created.
SQL>
2. Verify your sequence generator if its working or not
select mySequence.nextval from dual;
select mySequence.currval from dual;
3. As we are ready with oracle sequence generator, we will go ahead with mapping creation.
Our mapping will be very simple...
a. Source Qualifier
b. SQL Tranformation
c. Target
a. same input ports which are coming from source
b. One extra output port which is attached with sequence generator query
5. Connect all ports from Sequence Generator tranformation to Relational Target. Once we do that we are done with the mapping.
6. Create workflow which is connected with this mapping and run the workflow. Verify the records and at the same time current value of the sequence you have created.
