How to encrypt/decrypt a field in Oracle

Oracle provides package DBMS_OBFUSCATION_TOOLKIT which can be used for encrypting/decrypting database fields.

There are a couple of things to remember about this package though:

1. The package provides PROCEDURES to perform encryption/decryption. This alone may not be very useful if you need to use plain SQL. So I provide two functions to wrap these procedures (see below).
2. The length of both the value to be encrypted and the encryption key must be exact multiples of 8. Therefore you need to pad the value with somethig (i.e blanks) to reach the desired length.
   

These are the functions:

CREATE OR REPLACE function encrypt_val(
   input_string       VARCHAR2,
   key_string         VARCHAR2 ) return varchar2 as
   encrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string => input_string, key_string => key_string,
                                        encrypted_string => encrypted_string);
   --DBMS_OUTPUT.put_line ('encrypted hex value : ' || RAWTOHEX (UTL_RAW.cast_to_raw (encrypted_string)));
   return encrypted_string;
END;
/

CREATE OR REPLACE FUNCTION decrypt_val (encrypted_string VARCHAR2, key_string VARCHAR2)
   RETURN VARCHAR2
AS
   decrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string => encrypted_string, key_string => key_string,
                                        decrypted_string => decrypted_string);
--   DBMS_OUTPUT.put_line ('decrypted string output : ' || decrypted_string);
   RETURN decrypted_string;
END;
/

Example:

The following statement reads a table containing users and encrypts the username (i.e. in order to generate a default password). The actual value to be encrypted is the user name right padded with blanks so that the total length is a multiple of 8.

SELECT user_nm val,
       encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
FROM   ref_user;
        
To retrieve the decrypted value, you need to both decrypt and TRIM:

SELECT val, enc_val, TRIM (decrypt_val (enc_val, 'mykey678')) dec_val
FROM   (
        SELECT user_nm val,
               encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
        FROM   ref_user       
        );