Saturday, March 23, 2013

Oracle Sequence Generator In Informatica

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.





1 comment:

  1. I also observed in most project Oracle sequence is used instead of sequence generator. The reason might be, with the help of ORA seq, it is easy to insert record manually in table using SQL. In case of seq generator transformation, current value need to be modified on informatica if record is inserted manually.

    In my current project trigger on target table is used to generate surrogate key.
    In past project stored procedure transformation was used.

    Now it's nice to have SQL transformation :)

    ReplyDelete

All about CSS

From book HTML & CSS - Design and Build Websites - Jon Duckett CSS works by associating rules with HTML elements. These rules govern how...