Q: PL/SQL preocedure and row locks

Can anybody help???

Although I am not new to Oracle, I am new to PL/SQL, and I have encountered a problem. I want to write a PL/SQL procedure that selects rows form a parent table that match a certain criteria, selects their corresponding rows from a child table, then updates the rows in both the child table and parent table based on some logic. For performance purposes, I am trying to stay with one cursor that selects both parent and child rows. So my cursor looks something like this:

CURSOR C1 is

select 	A.primary_key_column


,	A.column2

,	B.primary_key_col

,	B.foreign_key_to_A_column


,	B.column3


from 	A


,	B

where 	B.foreign_key_to_A_column = A.primary_key_column
and 	A.column2 = 'NO';

and base on certain logic, A.column2 and B.column3 will be updated.

This procedure needs to run several times a day. The problem is that at any given time, another user might be viewing or updating a record from the child table (using Oracle Forms) that my procedure is attempting to update. When this happens, my procedure either needs to be able to update that record over the other users (which I dont think is possible, and I MAY NOT lock the entire table for this procedure) or be able to ignore (or rollback) all retrieved records that include the same foreign key to the parent row as the child row that is locked. i.e from the example above, if a row in B with B.foreign_key_to_A_column = 5 is already locked, then no other row in B with B.foreign_key_to_A_column = 5 should be updated, and neither should row A with A.primary_key = 5.

So far, I have been able to get it so that the procedure stops to wait for the specific row to unlock, adn I can get it so the whole program errors out if a row is locked, but I cant get it to skip the necessary records.

If anybody has any ideas, they are gratefully appreciated.

Danny Received on Thu Jun 22 1995 - 00:00:00 CEST