Encryption in Oracle

As of Oracle 8.1.6 you can now perform thorough encryption using the built-in package DBMS_OBFUSCATION_TOOLKIT (and no, that wouldn't be my choice of name either!)

If you are not yet at 8.1.6, the following routine is very simple, gives very secure encryption and lets you choose your own key length. The nicest thing about this routine is that to reverse the encryption, you just call the SAME routine with the encrypted text.

Some common sense notes:

create or replace
function encipher(p_string varchar2, p_key varchar2) return varchar2 is
  c_key_length number := 60;
  v_key varchar2(2000) := substr(p_key,1,c_key_length*2);
  v_result varchar2(2000);
  v_each_byte number(3);
  v_each_key_byte number(3);
-- Make sure the key is at least 'c_key_length' chars long with some random junk
  while length(v_key) < c_key_length loop
    v_key := v_key || '@1Z8F$'  || v_key;
  end loop;
  v_key := substr(v_key,1,c_key_length);
-- For each char in the string
  for i in 1 .. length(p_string) loop
-- get the ascii vals of the char, and its equivalent in the key
      v_each_byte := ascii(substr(p_string,i,1));
      v_each_key_byte := ascii(substr(v_key,mod(i,c_key_length)+1,1));
-- xor the two ascii values and convert back to a char byte
      v_result :=  v_result ||
       chr(v_each_byte + v_each_key_byte - 2*bitand(v_each_byte,v_each_key_byte));
  end loop;
  return v_result;