Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I tell if a row has been changed since I last looked at it?

Oracle Supplied Packages

How can I tell if a row has been changed since I last looked at it?

by  MikeJones  Posted    (Edited  )
I've seen this question banded about a few times, it appears to be more of a problem with Web applications which don't have a persistent connection to the database and therefore can't lock a record. One solution is to create a version-stamp or timestamp column on the table(s) and then on update update this column. I'm not very keen on this for a few reasons.

1. you have to increase the size of every row on the table and possibly many tables. This incease in size might not be trivial

2. any existing code would have to be changed to incorperate maintaining the extra column. This may impact the performance of this code, and end users will see a slowing of their system with no functionality improvement.

I know that a trigger could also be used to maintain this, but I feel this is just as sloppy.

So I decided to write the following, I hope it is of use to someone. I will calculate an integer value in the range of 0..2147483647 for each row. This can be considered a checksum if you will.

You call the function as

Code:
select what, 
       ever, 
       columns, 
       you, 
       want, 
       hashme('table_name',rowid)
from table_name
where....

On update you can use the hash value you got to make sure the row hasn't changed...

Code:
update my_table
set column = 'some value'
where some_condition = 'some condition'
and '1234567890' = hashme('my_table',rowid)
/

where 1234567890 is the hash value you got back from the select.

For those of you which are either:

A) lazy and can't be bothered to look through the code
or
B) New to Oracle and don't understand some of it.

It has the following limitations.

* Any Long Raw columns are ignored in the value.
* The table can not have any LOB columns
* The total length of a row can not exceed 32K (excluding
Longs)

These limitations are caused by either Oracle limitations or my lack of enthusiasm to solve them.

So here is the code.

Code:
create or replace function hashme (i_table_name in varchar2, 
                                   i_row_id in rowid)
return number
is
--
/* Variables and Types */
  type char_tab     is table of char(255)      index by binary_integer;
  type varchar2_tab is table of varchar2(4000) index by binary_integer;
  type date_tab     is table of date           index by binary_integer;
  type number_tab   is table of number         index by binary_integer;
  type raw_tab      is table of raw(32000)     index by binary_integer;
  --
  l_columns          dbms_sql.desc_tab;
  l_tablename        varchar2(30) := i_table_name;
  l_cursor           integer;
  l_ignore           integer;
  no_of_cols         integer;
  --
  l_char             char_tab;
  l_date             date_tab;
  l_varchar2         varchar2_tab;
  l_number           number_tab;
  l_raw              raw_tab;
  --
  l_vc_inc           integer := 1;
  l_c_inc            integer := 1;
  l_d_inc            integer := 1;
  l_n_inc            integer := 1;
  l_r_inc            integer := 1;
  l_offset           integer;
  l_read_length      integer;
  l_long_chunk       varchar(32000);
  --
  l_row              varchar2(32000);
  l_sql              varchar2(1000);
  rowid_not_found    exception;
  someother_datatype exception;
  l_step             varchar2(100);
--
/* Procedures */
--
--
procedure p_set_step (p_step in varchar2) is
begin
--dbms_output.put_line (p_step);
l_step := p_step;
end;
--
begin
  p_set_step(10);
  --
  p_set_step(20);
  l_sql := 'select * from '|| l_tablename || ' where rowid = ''' || i_row_id || '''';
  --
  p_set_step(30);
  l_cursor := dbms_sql.open_cursor;
  --
  p_set_step(40);
  dbms_sql.parse(l_cursor, l_sql, omx_oam_cmn_01.gc_dbmssql_flag);
  --
  p_set_step(50);
  dbms_sql.describe_columns(l_cursor, no_of_cols, l_columns);
  p_set_step(no_of_cols);
  p_set_step(60);
  --
  for r_column in 1 .. no_of_cols loop
  p_set_step('Column '||r_column);
    if l_columns(r_column).col_type = 1 then
      p_set_step(970);
      l_varchar2(l_vc_inc) := null;
      dbms_sql.define_column(l_cursor, r_column, l_varchar2(l_vc_inc),l_columns(r_column).col_max_len);
      l_vc_inc := l_vc_inc +1;
    elsif l_columns(r_column).col_type = 2 then
      p_set_step(980);
      l_number(l_n_inc) := null;
      dbms_sql.define_column(l_cursor, r_column, l_number(l_n_inc));
      l_n_inc := l_n_inc +1;
    elsif l_columns(r_column).col_type = 12 then
      p_set_step(990);
      l_date(l_d_inc) := null;
      dbms_sql.define_column(l_cursor, r_column, l_date(l_d_inc));
      l_d_inc := l_d_inc +1;
    elsif l_columns(r_column).col_type = 96 then
      p_set_step(9100);
      l_char(l_c_inc) := null;
      dbms_sql.define_column_char(l_cursor,r_column,l_char(l_c_inc), l_columns(r_column).col_max_len);
      l_c_inc := l_c_inc +1;
    elsif l_columns(r_column).col_type = 8 then
      p_set_step(9110);
      dbms_sql.define_column_long(l_cursor,r_column);
    elsif l_columns(r_column).col_type = 24 then
      p_set_step(9);
      -- Cant cope with long raw.
      null;
    elsif l_columns(r_column).col_type = 23 then
      l_raw(l_r_inc) := null;
      dbms_sql.define_column_raw(l_cursor, r_column, l_raw(l_r_inc), l_columns(r_column).col_max_len);
    else
      raise someother_datatype;
    end if;
  end loop;
  --
  l_vc_inc := 1;
  l_c_inc  := 1;
  l_d_inc  := 1;
  l_n_inc  := 1;
  --
  p_set_step(60);
  l_ignore := dbms_sql.execute(l_cursor);
  --
  if dbms_sql.fetch_rows(l_cursor) = 0 then
    raise rowid_not_found;
  end if;
  --
  p_set_step('no of cols is '||no_of_cols);
  p_set_step(70);
  for r_columns in 1 .. no_of_cols loop
    p_set_step('on_col '||r_columns);
    if l_columns(r_columns).col_type = 1 then
      p_set_step(80);
      dbms_sql.column_value(l_cursor, r_columns, l_varchar2(l_vc_inc));
      l_row := l_row || nvl(l_varchar2(l_vc_inc),'null');
      l_vc_inc := l_vc_inc +1;
    elsif l_columns(r_columns).col_type = 2 then
      p_set_step(90);
      dbms_sql.column_value(l_cursor, r_columns, l_number(l_n_inc));
      l_row := l_row || to_char(l_number(l_n_inc));
      l_n_inc := l_n_inc +1;
    elsif l_columns(r_columns).col_type = 12 then
      p_set_step(100);
      dbms_sql.column_value(l_cursor, r_columns, l_date(l_d_inc));
      l_row := l_row || to_char(l_date(l_d_inc),'ddmmyyyyhh24miss');
      l_d_inc := l_d_inc +1;
    elsif l_columns(r_columns).col_type = 96 then
      p_set_step(110);
      dbms_sql.column_value_char(l_cursor, r_columns, l_char(l_c_inc));
      l_row := l_row || l_char(l_c_inc);
      l_c_inc := l_c_inc +1;
    elsif l_columns(r_columns).col_type = 8 then
      p_set_step(120);
      l_offset := 0;
      while 1=1 loop
        dbms_sql.column_value_long(l_cursor, r_columns, 32000, l_offset, l_long_chunk, l_read_length);
        l_row := l_row || dbms_utility.get_hash_value(l_long_chunk, 0, 2147483647);
        exit when l_read_length < 32000;
        l_offset := l_offset + 32000;
      end loop;
    elsif l_columns(r_columns).col_type = 23 then
      dbms_sql.column_value_raw(l_cursor, r_columns, l_raw(l_r_inc));
      l_row := l_row||utl_raw.cast_to_varchar2(l_raw(l_r_inc));
      l_r_inc := l_r_inc + 1;
    else
      p_set_step(130);
      raise someother_datatype;
    end if;
  end loop;
  p_set_step(140);
  --
  dbms_sql.close_cursor(l_cursor);
  --
  return dbms_utility.get_hash_value(l_row, 0, 2147483647);
  --
exception
  when rowid_not_found then
    raise_application_error(-20101,'Rowid '|| i_row_id|| ' not found in table '|| i_table_name);
  when someother_datatype then
    raise_application_error(-20102,'Can not cope with one of the datatypes in the table '|| i_table_name);
  when others then 
    raise_application_error(-20100,'Step '||l_step||' '||substr(sqlerrm,1,200));
end;
/
set arraysize 1
sho errors

Anyone Interested in futhering the code should know following.

* Long raws are excluded from the calculation as DBMS_SQL
does not support them in terms of Define column and
column value.
* LOB's are not supported but could be, they would need to
be read in a loop and each 32K chunk hashed up and added
to the main string, much the same way as longs are
currently handled.
* If a huge table is read it's possible that it will blow
the 32K limit of the row to hash, this could be solved by
turning the row in to a hash value as it approaches 32K
and then continuing, I'm not overly keen on this and
think it's unlikely to happen (32K row size without
long / Lob data!)

I've done little testing with this, but have found it to work well with the test data I have given it. On a 22K rows it produced no duplicate keys, I'll try it on bigger data soon. The performance of the function should be good, It needs to do only 1 ROWID fetch so interms of table access should be very effecint, however if many rows are selected and therefore passed through the function it may be an idea to pin both DBMS_SQL and DBMS_UTILITY to the SGA so as to minimise the reloading overhead.

Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top