Reusing Oracle Sequences in place of Sequence Generator transformation.
While working with relational DBMS systems . we come across many objects which can be reused in our informatica mappings which saves the development and testing time and performance will also be good at those objects executes and vendor DBMS server level .
Here we will discuss about sequence Generator transformation alternatives . if we are using oracle we are having Db object called sequence which worked same like sequence generator but resides in database and can be used anywhere in DB for any purpose .
How to define Sequence in Oracle ?
Syntax to create sequence
While working with relational DBMS systems . we come across many objects which can be reused in our informatica mappings which saves the development and testing time and performance will also be good at those objects executes and vendor DBMS server level .
Here we will discuss about sequence Generator transformation alternatives . if we are using oracle we are having Db object called sequence which worked same like sequence generator but resides in database and can be used anywhere in DB for any purpose .
How to define Sequence in Oracle ?
Syntax to create sequence
The oracle docs note the syntax for the Oracle CREATE SEQUENCE function as follows:
CREATE SEQUENCE [ schema. ]sequence
Let's look at this example for using the Oracle CREATE SEQUENCE:
SQL> create sequence pubs1;
Sequence created.
Sequence created.
In the example above, we used CREATE SEQUENCE to create a sequence calledpubs1. We use the NEXTVAL function to retrieve the next value. You can get the current value by calling the CURRVAL function. You must call the NEXTVAL function before calling the CURRVAL function, or you will get an error. There is no current value to the sequence until the next value has been called at least once.
When you use Oracle CREATE SEQUENCE to create the sequence, you have a lot of flexibility as to how the sequence generates the next number.
SQL> create sequence test
2 start with 0
3 increment by 1
4 maxvalue 10000
5 cycle
6 cache 5;
2 start with 0
3 increment by 1
4 maxvalue 10000
5 cycle
6 cache 5;
Sequence created.
SQL> select pubs2.nextval from dual;
NEXTVAL
----------
1
----------
1
In this example, using Oracle CREATE SEQUENCE, the sequence pubs2 will start at number eight and increment by twos to 10000 and then start over. Let’s cover each of their parameters:
Advantages is here like we can use same object all over database and also in ETL tools ( whatever tools supports)
when we create sequence generator it cannot be used in oracle it self , so here we are increasing reusability.
Now how to use sequence defined in oracle in Informatica mappings
There are mainly 3 ways
1. write procedure in oracle itself with out paramtere for sequence.
1.1 Use connected procedure transformation
1.2 Use unconnected procedure transformation .
2.Use SQL transformation to read sequence values.
Following is the mapping which demonstrates how to do it .
following example uses a sequence created in Oracle
CREATE SEQUENCE "SCOTT"."SQ_GLOBAL"
MINVALUE 0
MAXVALUE 99999999
INCREMENT BY 1
START WITH 61
CACHE 20
ORDER CYCLE ;
And the stored procedure which is created to return the value of sequence
create or replace
procedure p_get_sequence
(
dept_name in Number
, seq_no out Number
) as
begin
select sq_global.nextval into seq_no from dual;
end p_get_sequence;
following is mapping where both are used to know how we can use them .
we have used procedure thransformation to used procedure which gives sequence number
and also we can use SQL transformation to directly use sequence in sql statement
following screen shows how to configure SQL transformation proprties
CREATE SEQUENCE "SCOTT"."SQ_GLOBAL"
MINVALUE 0
MAXVALUE 99999999
INCREMENT BY 1
START WITH 61
CACHE 20
ORDER CYCLE ;
And the stored procedure which is created to return the value of sequence
create or replace
procedure p_get_sequence
(
dept_name in Number
, seq_no out Number
) as
begin
select sq_global.nextval into seq_no from dual;
end p_get_sequence;
following is mapping where both are used to know how we can use them .
we have used procedure thransformation to used procedure which gives sequence number
and also we can use SQL transformation to directly use sequence in sql statement
following screen shows how to configure SQL transformation proprties