PL/SQL - Data Encryption/Decryption

The below example gives us an idea how to encrypt and decrypt data using PL/SQL.

DECLARE

   l_data   VARCHAR2 (255);

   l_str    VARCHAR2 (100) := 'This is for testing';

BEGIN

   l_data := RPAD (l_str, (TRUNC (LENGTH (l_str) / 8) + 1) * 8, CHR (0));

   --Calling the decryption fuction

   DBMS_OBFUSCATION_TOOLKIT.DESEncrypt (input_string => l_data,

                                                                                      key_string => 'MagicKey',

                                                                                      encrypted_string => l_str);

   DBMS_OUTPUT.put_line ('Encrypted Data: ' || l_str); --Encrypted String

   --Calling the decryption fuction

   DBMS_OBFUSCATION_TOOLKIT.DESDecrypt (input_string => l_str,

                                                                                      key_string => 'MagicKey',

                                                                                      decrypted_string => l_data);

   l_str := RTRIM (l_data, CHR (0));

   DBMS_OUTPUT.put_line ('Decrypted Data: ' || l_str); --Decrypted String

END;

We can replace the Key String with different values.

If we are trying to encrypt/decrypt data on a specific column in a table, it is better to write different procedures/functions for both encryption and decryption.