I cannot reproduce your issue -- what do you get if you run this test script and have you considered putting out some debug information from your procedure (eg: before you update, select out and print the row you are planning on updating to see what it looks like?)



ops$tkyte@ORA10G> drop table t;
 
Table dropped.
 
ops$tkyte@ORA10G> create table t as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA10G> column rowid new_val rid
ops$tkyte@ORA10G> column rowscn new_val rscn
ops$tkyte@ORA10G> select rowid, ora_rowscn rowscn, ename, sal from emp where ename = 'KING';
 
ROWID                  ROWSCN ENAME             SAL
------------------ ---------- ---------- ----------
AAAMuKAAEAAAAPMAAI   27321452 KING              605
 
ops$tkyte@ORA10G> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update emp set sal = sal*1.1 where ename = 'KING';
  5          commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> update emp set sal = 100 where rowid = '&RID' and ora_rowscn = &RSCN;
old   1: update emp set sal = 100 where rowid = '&RID' and ora_rowscn = &RSCN
new   1: update emp set sal = 100 where rowid = 'AAAMuKAAEAAAAPMAAI' and ora_rowscn =   27321452 
0 rows updated.
 
ops$tkyte@ORA10G> select rowid, ora_rowscn rowscn, ename, sal from emp where ename = 'KING';
 
ROWID                  ROWSCN ENAME             SAL
------------------ ---------- ---------- ----------
AAAMuKAAEAAAAPMAAI   27321472 KING            665.5
 
 

I presume you are refering to ora_rowscn?  If so, here is an excerpt from my most recent book on that:

<quote src=Expert Oracle: Database Architecture>

Optimistic Locking Using ORA_ROWSCN

Starting with Oracle 10g Release 1, you have the option to use the built-in ORA_ROWSCN function. It works very much like the version column technique described previously, but it can be performed automatically by Oracle—you need no extra column in the table and no extra update/maintenance code to update this value.

ORA_ROWSCN is based on the internal Oracle system clock, the SCN. Every time you commit in Oracle, the SCN advances (other things can advance it as well, but it only advances; it never goes back). The concept is identical to the previous methods in that you retrieve ORA_ROWSCN upon data retrieval, and you verify it has not changed when you go to update. The only reason I give it more than passing mention is that unless you created the table to support the maintenance of ORA_ROWSCN at the row level, it is maintained at the block level. That is, by default many rows on a single block will share the same ORA_ROWSCN value. If you update a row on a block with 50 other rows, then they will all have their ORA_ROWSCN advanced as well. This would almost certainly lead to many false positives, whereby you believe a row was modified that in fact was not. 

Therefore, you need to be aware of this fact and understand how to change the behavior.

To see the behavior and then change it, we’ll use the small DEPT table again:

ops$tkyte@ORA10G> create table dept
  2  (deptno, dname, loc, data,
  3   constraint dept_pk primary key(deptno)
  4  )
  5  as
  6  select deptno, dname, loc, rpad('*',3500,'*')
  7    from scott.dept;
Table created. 

Now we can inspect what block each row is on (it is safe to assume in this case they are in the same file, so a common block number indicates they are on the same block). I was using an 8KB block size with a row width of about 3,550 bytes, so I am expecting there to be two rows per block for this example:

ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING          20972   34676029
        20 RESEARCH            20972   34676029
        30 SALES               20973   34676029
        40 OPERATIONS          20973   34676029

And sure enough, that is what we observe in this case. So, let’s update the row where DEPTNO = 10 on block 20972:

ops$tkyte@ORA10G> update dept
  2     set dname = lower(dname)
  3   where deptno = 10;
1 row updated.
 
ops$tkyte@ORA10G> commit;
Commit complete.

What we’ll observe next shows the consequences of ORA_ROWSCN being tracked at the block level. We modified and committed the changes to a single row, but the ORA_ROWSCN values of both of the rows on block 20972 have been advanced:

ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting          20972   34676046
        20 RESEARCH            20972   34676046
        30 SALES               20973   34676029
        40 OPERATIONS          20973   34676029

It would appear to anyone else that had read the DEPTNO=20 row that it had been modified, even though it was not. The rows on block 20973 are “safe”—we didn’t modify them, so they did not advance. However, if we were to update either of them, both would advance. So the question becomes how to modify this default behavior. Well, unfortunately, we have to re-create the segment with ROWDEPENDENCIES enabled.

Row dependency tracking was added to the database with Oracle9i in support of advanced replication, to allow for better parallel propagation of changes. Prior to Oracle 10g, its only use was in a replication environment, but starting in Oracle 10g we can use it to implement an effective optimistic locking technique with ORA_ROWSCN. It will add 6 bytes of overhead to each row (so it is not a space saver compared to the do-it-yourself version column) and that is, in fact, why it requires a table re-create and not just a simple ALTER TABLE: the physical block structure must be changed to accommodate this feature.

Let’s rebuild our table to enable ROWDEPENDENCIES. We could use the online rebuild capabilities in DBMS_REDEFINITION (another supplied package) to do this, but for something so small, we’ll just start over:

ops$tkyte@ORA10G> drop table dept;
Table dropped.
 
ops$tkyte@ORA10G> create table dept
  2  (deptno, dname, loc, data,
  3   constraint dept_pk primary key(deptno)
  4  )
  5  ROWDEPENDENCIES
  6  as
  7  select deptno, dname, loc, rpad('*',3500,'*')
  8    from scott.dept;
Table created.
 
ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING          21020   34676364
        20 RESEARCH            21020   34676364
        30 SALES               21021   34676364
        40 OPERATIONS          21021   34676364

We’re back where we were before: four rows on two blocks, all having the same initial ORA_ROWSCN value. Now when we update DEPTNO=10

ops$tkyte@ORA10G> update dept
  2     set dname = lower(dname)
  3   where deptno = 10;
1 row updated.
 
ops$tkyte@ORA10G> commit;
Commit complete.

we should observe the following upon querying the DEPT table:

ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting          21020   34676381
        20 RESEARCH            21020   34676364
        30 SALES               21021   34676364
        40 OPERATIONS          21021   34676364

The only modified ORA_ROWSCN at this point belongs to DEPTNO = 10, exactly what we wanted. We can now rely on ORA_ROWSCN to detect row-level changes for us. 
</quote> 

I see that - I don't like their workaround (doesn't work for any query that returns more than one row).  However, a view can correct this:


ops$tkyte@ORA10GR2> create or replace function my_security_function( p_schema in varchar2,
  2                                        p_object in varchar2 ) return varchar2
  3  as
  4  begin
  5          return '1=1';
  6  end;
  7  /

Function created.

ops$tkyte@ORA10GR2> create table t ( x int );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR2> create or replace view v_of_t
  2  as
  3  select t.*, ora_rowscn rscn
  4    from t;

View created.

ops$tkyte@ORA10GR2> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'V_OF_T',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from v_of_t;

         X       RSCN
---------- ----------
         1    2207152
 

ops$tkyte%ORA10GR2> create table dept rowdependencies as select * from scott.dept;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function my_function( p_schema in varchar2, p_object in varchar2 ) return varchar2
  2  as
  3  begin
  4          return 'deptno = 10';
  5  end;
  6  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view deptv as select dept.*, ora_rowscn scn from dept;

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'DEPTV',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'my_function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 Accounting     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234

ops$tkyte%ORA10GR2> insert into deptv (deptno,dname,loc) values ( 10,'xxx','yyy');

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234
        10 xxx            yyy             64971260

ops$tkyte%ORA10GR2> update deptv set dname = 'abc' where dname = 'xxx';

1 row updated.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234
        10 abc            yyy             64971263

ops$tkyte%ORA10GR2> delete from deptv where dname = 'abc';

1 row deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



ops$tkyte%ORA10GR2> alter table t enable row movement;

Table altered.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE ROW MOVEMENT

ops$tkyte%ORA10GR2> create table t1
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2 (
  2  OWNER                  VARCHAR2(30),
  3  OBJECT_NAME            VARCHAR2(30),
  4  SUBOBJECT_NAME         VARCHAR2(30),
  5  OBJECT_ID              NUMBER,
  6  DATA_OBJECT_ID         NUMBER,
  7  OBJECT_TYPE            VARCHAR2(19),
  8  CREATED                DATE,
  9  LAST_DDL_TIME          DATE,
 10  TIMESTAMP              VARCHAR2(19),
 11  STATUS                 VARCHAR2(7),
 12  TEMPORARY              VARCHAR2(1),
 13  GENERATED              VARCHAR2(1),
 14  SECONDARY              VARCHAR2(1),
 15  NAMESPACE              NUMBER,
 16  EDITION_NAME           VARCHAR2(30)
 17  ) rowdependencies;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.can_redef_table( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2', orderby_cols => 'OBJECT_ID' )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
  2          dbms_redefinition.copy_table_dependents
  3          ( user, 'T1', 'T2',
  4            copy_indexes => dbms_redefinition.cons_orig_params,
  5            num_errors => :nerrors );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print nerrors

   NERRORS
----------
         0

ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t2;

Table dropped.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "OWNER" VARCHAR2(30) CONSTRAINT "SYS_C0012216" NOT NULL ENABLE NOVALIDATE,

        "OBJECT_NAME" VARCHAR2(30) CONSTRAINT "SYS_C0012217" NOT NULL ENABLE NOVALIDAT
E,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER CONSTRAINT "SYS_C0012218" NOT NULL ENABLE NOVALIDATE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE CONSTRAINT "SYS_C0012219" NOT NULL ENABLE NOVALIDATE,
        "LAST_DDL_TIME" DATE CONSTRAINT "SYS_C0012220" NOT NULL ENABLE NOVALIDATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30),
         CONSTRAINT "T1_PK" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" <b>ROWDEPENDENCIES
</b>


ops$tkyte%ORA10GR2> drop table t1;

Table dropped.