How to Generate Sequence numbers using Expression Transformation in OBIA ? What is the alternate to SEQ GENERATOR TRANSFORMATION?

While generating the surrogate key for target table in OBIA you can either use Sequence generator transformation or Sequence generator function in Oracle.

1)For using Oracle sq first you need to create a stored procedure(trigger) in the database say SP_seq.And then call the same using stored procedure transformation and you can connect to the output port.

CREATE SEQUENCE SEQ_ABC
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1
NoCACHE
NOCYCLE
/

create or replace TRIGGER TAREGT_A
BEFORE INSERT ON TABLE_A
FOR EACH ROW
DECLARE
NO  TABLE_A_PK%TYPE;
BEGIN
SELECT SEQ_ABC.nextval
INTO NO FROM dual;
:new. TABLE_A_PK:= NO;
END;
/

2)Now when you are using the sequence in Oracle it may load the database sometimes,in that case we can go for Sequence Generator Transformation.Here you need to give the start value ,end value and increment by values.

But while using Sequence Generator you may come across a situation where the Max value is reached but still you want to generate more, in that case you either go for Oracle sequence or else

1. Use an EXP transformation and pass the output of the Sequence Gen to the expression(SEQ_IN)
2. Define a variable VAR_SEQ and initialize to zero
VAR_SEQ = DECODE(SEQ_IN), 'End Value', VAR_SEQ = VAR_SEQ + 1 ,VAR_SEQ)
where 'End Value' is the max value defined in the Sequence Gen transformation.