Unanswered: synchronous calling of stored procedure
I writing a stored procedure (say P1) in which I have some business logic and from that procedure I am calling another procedure (say P2). The P1 procedure will be called from multiple instances. Now I want that at any given time the P2 procedure should be called by 1 instance only. i.e., even though if two instaces call the P2 procedure then that procedure should be execute one by one for those instances.
Please help me in how to achieve this functionality.
You can do this by obtaining a lock. The simplest is to obtain a lock on a row of some table:
procedure p2 is
select col from some_table where pk=...
-- Now continue
The second call to P2 will have to wait for the other caller's transaction* to finish before it can continue.
Alternatively, you can use the DBMS_LOCK package to define your own locking mechanism.
* Note: my p2 code above locks a record in some_table for the duration of the whole transaction, not just the procedure call. This may be too much. You could use an autonomous transaction to prevent this.