PL/SQL (MOSC)

MOSC Banner

locking tables

edited Mar 30, 2015 1:42PM in PL/SQL (MOSC) 11 commentsAnswered

We have a table, id_table, that have 2 columns:
        cust_id NUMBER(10)
        cust_name VARCHAR2(16)
My requirement, for each request find the min cust_id that has a null cust_name and send it to an external system to get the cust_name.
Then cust_name is updated with value returned by the external system.
The problem arises when we 2 requests at the same time.

The solution we came up with is
LOCK TABLE id_table IN EXCLUSIVE MODE;
SELECT min(cust_id) into min_cust_id from id_table where cust_name is null;
Send min(cust_id) to an external system, then update the table with the Returned_cust_name

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center