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.
