How can I delay execution of a pkg until previous execs of the pkg

[celispb]

I have a package (process_pkg) called by a trigger. The trigger is fired when table_hdr is updated. The process that updates table_hdr is from other databases via a db links.

So, db1, db2, db3 and db4 can update the table at the same time but I want only one occurence of process_pkg running at a time.

How do I pause execution of process_pkg if process_pkg is already running?

I tried this

PROCEDURE main
BEGIN
UPDATE test_table
SET test_col1 = test_col1
WHERE test_col2 = 1;
procedure1;
procedure2;
. . .
END main;

I thought the update to test_table would lock the record until main completed and the commit (or rollback) was issued but it did not work. This worked when I tested in SQlPlus. But when the other databases insert and updated into table_hdr it did not work. (And I am not sure why? Any explaination would be appreciated.)

But the main question is how can I delay execution of package until previous executions of the package have completed?

Thanks for any help. Let me know if I left out any details.

 

[celispb]

Just to clarify the situation.

insert_pkg starts on db1, db2, db3, db4. This package inserts data into table_hdr and table_dtl in my_db.

When table_hdr.transmit_complete is updated, an after update trigger fires and calls my_process_pkg.

I do not want my_process_pkg to run for all the dbs at once. my_process_pkg runs fairly fast (no more than 2 seconds from start to end.)

my_process_pkg can also run from a form.

Thanks for any help.