Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
select what,
ever,
columns,
you,
want,
hashme('table_name',rowid)
from table_name
where....
update my_table
set column = 'some value'
where some_condition = 'some condition'
and '1234567890' = hashme('my_table',rowid)
/
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