Oracle pl/sql


I have a table ORDER(order_id number, Migrate_flag varchar2(1));

I have another table SUB_ORDRDS(sub_order_id number,order_id number);

ie for all there orders sub orders will be stored here.


There requirement is I need to migrate the orders data where the migate flag is 'Y' and all the sub orders under those orders to another two tables(insert or update) .

In the above example if I migrate all the orders first to table1.
after that I am joining the SUB_ORDERS table with the ORDERS table with condition migate_flag=Y and migrating SUB_ORDERS table data to table2.

My question is,
In the above example 1 and 2 orders will get migrate first to table1
Its taking around 15 min. of time in my real scenario due to huge number or records.
But if someone changes migrate_flag to 'N' for those orders which are Y before,
then the sub orders for those orders do not get migrate and data discrepancy may happen like an order might got migrated and sub orders under it do not get migrate.
How to achieve the both transactions as atomic? ie if order migrate then suborders also migrate otherwise both do not migrate.
One way I think is
1) In the order cursor,
I need to run the suborder cursor for every order and migrate suborders also immediately along with that particular order. But this is causing performance issue
I I need to run the suborder query many times ie for every order.
Another way is
I need to lock all order by writing cursor for update and migrate all orders and do not commit and then migrate all the sub orders after that execute commit and release the lock.
But locking those many records for such a long time is causing issues for the end user.

Can you please suggest is there any other way to do ?
like locking batch wise like only 1000 records and migrate orders and sub orders and then lock the next 1000 and so on. So that I could achieve atomic nature and also do not lock huge records for long time.

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

One of the way is that when inserting the orders into the new_orders table you also need to set a flag on a column in this table. Call this column say 'MIGRATE_STATUS' and make it a CHAR(1).
When you inserted the data from your ORDERS table (for the migrate_flag of 'Y') make the MIGRATE_STATUS = 'I' (stands for incomplete data)
Then, when you are populating the new_sub_orders table, do not use the migrate_flag of the orders table anymore. Instead join the old sub_orders table with the new_orders table for the records with the MIGRATE_STATUS = 'I'. When you insert the new_sub_orders table run the UPDATE for the new_orders and set the MIGRATE_STATUS = 'C' (complete).
Do everything in a single transaction

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wasim Akram ShaikCommented:
This is a generic situation while using oracle database to do batch processing, I have had faced this issue many times especially during the migrations from legacy to some product database.

The approach which is to split and divide the records to harness the power of parallel processing.

Add a column to your original table

ORDER(order_id number, Migrate_flag varchar2(1),run_number number);

order_id  migrate_flag    run_number
1,                Y                        0
2,                Y                        0
3,                N                        1
4                 N                        1
5                 Y                         2

So by using an extra column you can always pick the run number and can run it many times

so the cursor would become something like this

procedure first_process(p_run_number in number) as
cursor do_process
select * from orders where run_number=p_run_number
and migrate_flag='N';
for l1 in do_process
update orders set run_number=p_run_number,migrate_flag='Y';
end loop;

You just have to do a task before you could do this, you have to assign run_numbers to your records, this too can be automated by using a sequence and storing the start order_number and end_order_number..

By this way you can do this task faster by performing single row commits and can run the process n number of times without the problem of duplication.
GouthamAnandAuthor Commented:
Thank  you very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.