create or replace package subs_cipher
is
function scramble ( obfs_in varchar2 ) return varchar2;
— pragma not required as of 8.1.5
pragma restrict_references( scramble, wnds, wnps, wnps);
function unscramble ( obfs_in varchar2 ) return varchar2;
— pragma not required as of 8.1.5
pragma restrict_references( unscramble, wnds, rnds, wnps );
end;
create or replace package body subs_cipher
is
— character translation sets
— second set must be identical to first, but in a different order
—
xlate_from varchar2(100) := ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()-_=+’;
xlate_to varchar2(100) := ‘PQRtsuLTU%^VWvJYcd0eifghNl(9O&*-_~A1+xC!D=E6jSIKr$@k#n7Fm23ZB45wy8GHM)Xopqzab’;
— how many pieces should text be broken into
v_chunks integer := 4;
— reverse the order of text sent
function reverse ( forward_in varchar2 ) return varchar2
is
v_reverse varchar2(4000) := null ;
begin
v_reverse := ”;
for pos in 1 .. length ( forward_in )
loop
v_reverse := v_reverse || substr( forward_in, 0 – pos, 1);
end loop;
return v_reverse;
end;
— chop the text sent into v_chunks pieces of equal length,
— plus any remainder, then reorder the chunks
function chopit ( text_in varchar2 ) return varchar2
is
v_mod integer;
v_chunk_len integer;
v_chop varchar2(4000);
v_text_length integer;
begin
v_text_length := length(text_in);
if v_text_length < ( v_chunks + 1 ) then
if mod(v_text_length,2) = 0 then
return substr(text_in, (v_text_length / 2) + 1 ) || substr(text_in, 1,v_text_length / 2);
else
return reverse(text_in);
end if;
end if;
— get the remainder
v_mod := mod(length(text_in), v_chunks);
v_chunk_len := (length(text_in) – v_mod) / v_chunks;
v_chop := ”;
if v_mod > 0 then
v_chop := reverse(substr(text_in,0-v_mod));
end if;
v_chop := v_chop
|| substr(text_in, ( v_chunk_len * 2 ) + 1, v_chunk_len ) –3
|| substr(text_in, 1, v_chunk_len ) –1
|| substr(text_in, ( v_chunk_len * 3 ) + 1, v_chunk_len ) –4
|| reverse(substr(text_in, v_chunk_len + 1, v_chunk_len )); –2
return v_chop;
end;
— reverse text chopped by chopit
function unchop ( text_in varchar2 ) return varchar2
is
v_mod integer;
v_chunk_len integer;
v_chop varchar2(4000);
v_text_length integer;
begin
v_text_length := length(text_in);
if v_text_length < ( v_chunks + 1 ) then
if mod(v_text_length,2) = 0 then
return substr(text_in, (v_text_length / 2) + 1 ) || substr(text_in, 1,v_text_length / 2);
else
return reverse(text_in);
end if;
end if;
v_mod := mod(length(text_in),v_chunks);
v_chunk_len := (length(text_in) – v_mod) / v_chunks;
v_chop := ”;
if v_mod > 0 then
–v_chop := reverse(substr(text_in,1,v_mod));
v_chop := reverse(substr(text_in,1,v_mod));
end if;
v_chop :=
substr(text_in, v_chunk_len + v_mod + 1, v_chunk_len ) –1
|| reverse(substr(text_in, ( v_chunk_len * 3 ) + v_mod + 1, v_chunk_len )) –2
|| substr(text_in, v_mod + 1, v_chunk_len ) –3
|| substr(text_in, ( v_chunk_len * 2 ) + v_mod + 1, v_chunk_len ) –4
|| v_chop; — remainder
return v_chop;
end;
function scramble ( obfs_in varchar2 ) return varchar2
is
begin
return translate( reverse(chopit(obfs_in)) , xlate_from, xlate_to );
end;
function unscramble ( obfs_in varchar2 ) return varchar2
is
begin
return translate( unchop(reverse(obfs_in)) , xlate_to, xlate_from );
end;
END subs_cipher;
/
—————- USAGE —————————
update cif_address set street_Address_1=vivek.subs_cipher.scramble(subs_cipher.scramble(street_address_1)) where street_address_1 is not null;
commmit;
update cif_address set street_Address2=subs_cipher.scramble(vivek.subs_cipher.scramble(street_address2)) where street_address2 is not null;
commit;
update cif_address set street_Address3=subs_cipher.scramble(vivek.subs_cipher.scramble(street_address3)) where street_address3 is not null;
commit;