Database Sequence
Create a database Sequence in Oracle.
create sequence test_sequence
minvalue 2
maxvalue 999
start with 2
increment by 2
Map your interface
Call the sequence in the respective column using this format
<SCHEMA_NAME>.<SEQUENCE_NAME>.NEXTVAL
TEMP.TEST_SEQUENCE.NEXTVAL
TEMP – SCHEMA_NAME
TEST_SEQUENCE – SEQUENCE_NAME
- Map it to ‘Target’
- Use ‘Insert’ only Option
run your interface and check for data.
ODI Sequence
Create an ODI sequence with the required option
Map the column and the sequence with the ODI_SEQUENCE as
:ODI_SEQ_NEXTVAL
:<COLON>_<ODI_SEQUENCE>_NEXTVAL
- Map it to ‘Target’
- Use ‘Insert’ only Option
- IKM SQL TO SQL APPEND ( for Row by Row processing)
run the interface and check for the data
November 26, 2016 at 3:17 PM
can you help on this i have a requirment i have to maintain unique number for every 5000 records as a batch.
November 28, 2016 at 12:41 PM
Hi,
if you’re at Oracle, just map the sequence in ODI and recreate it whenever you need….
make sense?
September 15, 2016 at 1:11 AM
Hi what is the maximum value an ODI sequence will reach upto?
After reaching at the max value does it loops back and starts from 1 as DB sequence?
September 9, 2016 at 10:13 AM
format is #.nextval in odi 12c. isnt it?
February 2, 2016 at 1:13 PM
Hi,
How to retrieve current value.
Regards,
Rabi
September 29, 2015 at 4:31 PM
Hello guys,
I want to know how to make a logic of sequence number, it is running on CDC basis, every time generate a unique number. Whenever i execute the scenario it should not be start from 1 or 0, it incremented by 1 start when the last execution stop.
Please light up on this issue or your valuable answer.
June 23, 2014 at 7:27 AM
Hi Experts,
I really need an urgent help of whose answer I am not getting anywhere. So i thought the best source will be you.
I am having column Account_Number which is having Duplicate values in Source.
And I am having a primary Key Constraints on ACCOUNT_NUMBER in Target.
So while Executing interface it gives Unique Constraint voilated.
Now my requirement,In Target column Account_Number we have to append Sequence..
For E.g
Source table
Account_Number column having following values
Account_Number
abcd
abcd
abcd
abcd
efgh
efgh
efgh
And i have to mapped as below for Target Column.
Account_Number
abcd_1
abcd_2
abcd_3
abcd_4
efgh_1
efgh_2
Regards
Prashant
March 10, 2014 at 8:58 PM
Hi Experts,
I really need an urgent help of whose answer I am not getting anywhere. So i thought the best source will be you.
I am having a ID column (primary key) in a table which I need to load with seq. generator so that the value for each record increases by 1.
So every time the ETL is run value in the table should increase from 1 and again in the next load should start from 1.
I created a seq generator and used it but the issue is for each run of the ETL, the value is same. For eg: For the first run the value is 1 for all records and 2 for all records of the second run. Where as I want it to be like below.
1st Run
ID Name
1 a
2 b
c c
2nd run.
1 d
2 e
3 f
March 12, 2014 at 4:29 AM
Hi…
What I can suggest is to create a procedure in ODI that drops the sequence and recreate it. Use the procedure as first step of your load package (or load process).
Make sense?
Cezar
May 28, 2014 at 7:04 AM
Hi, I having the same problem, did you find already a solution
June 20, 2011 at 6:07 AM
hi,
I wnt to use ODI sequence in my project. whether the sequence number created will be continuous with every execution or it will start from the first for every execution. If it continues the sequence number for every execution of the interface, is there a way to restart the sequence for every interface execution.
June 20, 2011 at 6:34 AM
There is a tool OdiReinitializeSeq.Use this to initialize sequence for each run. You can find this under the package tools.
June 18, 2010 at 6:31 AM
hi….
i want to use a user defined function for converting null to default value.i m using function as
nulltodefault($(pal.lastname),$(var)).i
i hav to use this interface and a variable (var) in a package where var contains default value.how to do that?
what to write in function code and at target mapping?
June 21, 2010 at 8:52 AM
Hi Pallavi..
I will send you an email about this.
Thank you for visit us!
January 12, 2010 at 6:44 PM
Hi,
I have a question. In source table i have 50 rows and i am processing all 50 rows at once to target. Now if first row has insert and 10th row has update to same id.
When i run ODI it fails some time as it tries to process update first insert latter. Reason was it doesn’t follow any sequence when we select commit after 50 rows or so.
Any solution to this?