How do you use the sequence id generated by a sequence object in oracle in a mapping?
I've done this previously by creating a stored procedure that returns the sequence nextval, but in this case i can't create a stored procedure
on the legacy database.
Requirements is to load data to a legacy database and the primary key of the tables is using sequences generated by the database.
Thanks for the help!!!
How about bringing it from source qualifier with the sources. writing a lookup override?
can you please explain further, can a sequnce become a source?
As far as i am aware of that there is no best option to use the sequence id generated by oracle sequences in a mapping.
One option is to write a stored procedure and get the sequence but you have mentioned that you can't go with that option.Also this will hit database for each row which reduces the performance.
There are other options is to use a sequence generator in the mapping and this will cause issue if it exceeds the max limit
Try the below logic and see how it helps
Create a unconnected lookup with the below SQL overide
Select sequence_name.nextval as seq_num , 1 as dummy from dual.
Using expression transforamtion add a dummy port and give the value as 1 and call the unconnected lookup by passing this dummy port value
In lookup match two dummy ports and get the seq_num as output.
Then use this sequence in the furthur flow
Please let me know if you face any issues
Thanks! can i choose any table as a lookup and then do SQL override?
Solution form Sasiramesh is good start but
With current explanation you will allways get same number from unconnected lookup because by defaul, unconnected lookups are built with static cache, so it means that above query would be executed only once and every record will get that value.
To make this Sasiramesh solution work, you need on session level to uncheck Lookup caching enabled option on specific lookup on mapping tab of that session.
If Lookup caching enabled is not checked, it tells PC that send's query to database for each row which is calling specific router. And one more thing. This Lookup doesn't eed to be unconnected. Same effect could be done with connected lookup
Did you consider that you will have a lot performance problems with this, because for 1 million source rows it need's to query database 1 million times?
Better option is to use Informatica sequences, but it's up to you
For any table qustion. You don't need to use any table, you can create blank lookup and populate output and lookup ports by hand and then put adequate SQL override