Alas, the ora_rowscn approach is not going to work very well. It still enables "lost updates".

I have my information from AskTom: ORA_ROWSCN for optimistic locking

Copied example here to anticipate dead links...

set echo on

drop table t;
create table t ( x int primary key, name varchar2(30) ) rowdependencies;

insert into t values ( 1, 'john' );
insert into t values ( 2, 'mary' );
commit;

variable ora_rowscn number
set autoprint on
exec select ora_rowscn into :ora_rowscn from t where x = 2;

set echo off
prompt in another session issue:
prompt variable ora_rowscn number
prompt set autoprint on
prompt exec select ora_rowscn into :ora_rowscn from t where x = 2;;
prompt and come back here and hit enter...
pause

set echo on
update t set name = 'beth' where x = 2 and ora_rowscn = :ora_rowscn;

set echo off
prompt in another session issue:
prompt update t set name = 'sally' where x = 2 and ora_rowscn = :ora_rowscn;;
prompt and come back here and hit enter...
pause

commit;

set echo off
prompt now commit in the other session and notice your lost update :(

What you should do IMHO, is to add an extra column (eg: Version_Number) to your table where you set the nextval of a sequence on update of a record (using a trigger, or do this in the package code).

WorkFlow: Client queries some rows and obtains the data with the version_Number. When updating this data he needs to check the version_number with the current version_number in the record. If they differ, no row will be updated and you should return an error to the client (No_Records_Found).

create table t ( x int primary key, name varchar2(30), version_Number number);
create sequence s_t start with 1 increment by 1;

insert into t values ( 1, 'john', s_t.nextval);
insert into t values ( 2, 'mary', s_t.nextval);
commit;

select * from t;

You see something like this:

X NAME Version_Number

1 john 1

2 mary 2

-- first session
update t set name = 'beth', version_number = s_t.nextval where x = 2 and version_number = 2;

-- second session
update t set name = 'sally', version_number = s_t.nextval where x = 2 and version_number = 2; 

-- First session
commit;

-- Second session returns with no updated records...

As I said, you should notify the client that there was no record updated, but this could have 2 reasons:

• outdated record
• deleted record

In both cases the client has to refresh this record again and determine what to do next.