SQL & PL/SQL

Hi everyone. I'm new to this forum, but I've been getting deeper into Oracle processing as part of my job, and a situation arose that I hope the membership can help me with.

We use an ETL software package to load Data Warehouse Tables that are all Oracle. As part of that load, we process many Tables with Type-2 methodology (insert a new row with a new unique sequence number as the primary key, set the old row to "Inactive" but retain it for historical purposes).

As part of a tuning effort, we are attempting to run multiple threads in our ETL. One of the functions of the ETL is to retrieve the max of the key sequence number, then add 1 to it, then insert. This worked fine with a single thread, but upon splitting into multiple threads, we got the unique-key-constraint error. Thread 1 reaches the retrieval and gets the max number and adds 1 to it. But before that row can be inserted, thread 2 reaches the retrieval and gets the same max number and adds 1 to it. Then thread 1 inserts successfully, but thread 2 errors out due to the unique constraint.

We tried using the Oracle Sequence and a Trigger that retrieves the NEXTVAL from the sequence. This works, but to me it isn't as elegant a solution as I'd like to see, because when we move to the next-higher environment, we'll have to reset the start number (we set the start number by retrieving the max of the sequence one time and setting it to that).

I tried writing a trigger that, prior to insert, selects the max of the sequence number. But that, of course, produced the "table mutating" error.

I read in this forum about a solution that uses the "pragma" process, but that sounds like it could be an issue as well.

Does anyone have a solution that can be written once, then migrated to our next-higher environments without having to manually intervene?

Thank you in advance for your input.