Examples on how to use "dbms_obfuscation_toolkit" package

drop table tbl_test purge;

create table tbl_test (
  source_passwd varchar2(64), 
  encrypted_string varchar2(2000),
  encrypted_raw raw(2000),
  decrypted_string1 varchar2(64), 
  decrypted_string2 varchar2(64))
/



insert into tbl_test (source_passwd) values('abc');
insert into tbl_test (source_passwd) values('this is long password');
insert into tbl_test (source_passwd) values('Something_Special*');
commit;

-- below program will encrypt the password and store them into both varchar2 and raw columns.
-- the decryption will decrypt twice, one using varchar2, one using raw column

set serveroutput on
execute dbms_output.enable(10000);
declare
  v_encrypted_raw               RAW(2048);
  v_encrypted_string            VARCHAR2(2048);
  v_decrypted_raw               RAW(2048);
  v_decrypted_string1            VARCHAR2(2048);
  v_decrypted_string2            VARCHAR2(2048);
begin
  -- encryption loop
  for c1 in (select * from tbl_test)
  loop
    dbms_output.put_line(c1.source_passwd);
    dbms_obfuscation_toolkit.DESEncrypt(input => UTL_RAW.CAST_TO_RAW(rpad(c1.source_passwd,64,' ')),
                   key =>  UTL_RAW.CAST_TO_RAW('abcdefgh'), 
                   encrypted_data => v_encrypted_raw);
    v_encrypted_string := UTL_RAW.CAST_TO_VARCHAR2(v_encrypted_raw);
    update tbl_test 
    set encrypted_string=v_encrypted_string,
        encrypted_raw=v_encrypted_raw
    where source_passwd = c1.source_passwd;
  end loop;
  commit;
  -- decrytion loop - using string column
  for c1 in (select * from tbl_test)
  loop
    dbms_output.put_line(c1.source_passwd);
    dbms_obfuscation_toolkit.DESDecrypt(input => UTL_RAW.CAST_TO_RAW(c1.encrypted_string),
                   key =>  UTL_RAW.CAST_TO_RAW('abcdefgh'), 
                   decrypted_data => v_decrypted_raw);
    v_decrypted_string1 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
    update tbl_test 
    set decrypted_string1=v_decrypted_string1
    where source_passwd = c1.source_passwd;
  end loop;
  commit;
  
  -- decrytion loop - using raw column
  for c1 in (select * from tbl_test)
  loop
    dbms_output.put_line(c1.source_passwd);
    dbms_obfuscation_toolkit.DESDecrypt(input => c1.encrypted_raw,
                   key =>  UTL_RAW.CAST_TO_RAW('abcdefgh'), 
                   decrypted_data => v_decrypted_raw);
    v_decrypted_string2 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
    update tbl_test 
    set decrypted_string2=v_decrypted_string2
    where source_passwd = c1.source_passwd;
  end loop;
  commit;
end;
/

select * from tbl_test where source_passwd=decrypted_string1;
select * from tbl_test where source_passwd=decrypted_string2;