Oriol Tomàs said:
Somebody knows how to implement optimistic concurrency with Oracle Stored
Procedures?
Optimistic concurrency requires passing extra data in your DML statement to
make sure that the target row has not been modified by another session since
you queried it. The OracleCommandBuilder generates DML with optimistic
concurrency control automatically, but if you want to implement it in a
stored procedure, you will either need to pass two sets of values for each
row, the old value and the new value, or add a timestamp column to your
table which is updated on any change. Then you can just pass one extra
column, the original timestamp.
So your procedures would look something like
procedure my_table_dml.updater(
p_key_col my_table.key_col%type,
p_col1 my_table.col1%type,
p_col2 my_table.col2%type,
p_col3 my_table.col3%type,
p_col4 my_table.col4%type,
p_col1_old_value my_table.col1%type,
p_col2_old_value my_table.col2%type,
p_col3_old_value my_table.col3%type,
p_col4_old_value my_table.col4%type)
is
begin
update my_table set
col1 = p_col1,
col2 = p_col2,
col3 = p_col3,
col4 = p_col4
where
key_col = p_key_col
and (col1 = p_col1_old_value or (p_col1_old_value is null and p_col1 is
null)),
and (col2 = p_col2_old_value or (p_col2_old_value is null and p_col2 is
null)),
and (col3 = p_col3_old_value or (p_col3_old_value is null and p_col3 is
null)),
and (col4 = p_col4_old_value or (p_col4_old_value is null and p_col4 is
null));
if sql%rowcount = 0 then
RAISE_APPLICATION_ERROR(-20999, 'Concurrency violation. Row could not be
located for updating');
end if;
end;
or
procedure my_table_dml.updater(
p_key_col my_table.key_col%type,
p_col1 my_table.col1%type,
p_col2 my_table.col2%type,
p_col3 my_table.col3%type,
p_col4 my_table.col4%type,
p_timestamp in out my_table.timestamp_col%type)
is
begin
update my_table set
col1 = p_col1,
col2 = p_col2,
col3 = p_col3,
col4 = p_col4,
timestamp_col = sysdate
where
key_col = p_key_col
and timestamp_col = p_timestamp
returning timestamp_col into p_timestamp;
if sql%rowcount = 0 then
RAISE_APPLICATION_ERROR(-20999, 'Concurrency violation. Row could not be
located for updating');
end if;
end;
David
