Oracle Encryption/Decryption
The data such as credit card numbers, social security numbers (USA), etc. have to be encrypted and decrypted during data management activities. The PL/SQL code below shows the use of DBMS_CRYPTO package to encrypt and decrypt data.
-- In case it is not setup, the steps are as follows (for windows)
-- The exact path may change based on Oracle_Home

1. sqlplus "SYS AS SYSDBA"
2. @C:\11g\app\oracle\product\11.2.0\server\rdbms\admin\dbmsobtk
3. @C:\11g\app\oracle\product\11.2.0\server\rdbms\admin\prvtobtk.plb

-- If all users (public) should not have privileges, it should be
-- given to named users

GRANT EXECUTE ON dbms_crypto to public;
GRANT EXECUTE ON dbms_sqlhash to public;
GRANT EXECUTE ON dbms_obfuscation_toolkit to public;
GRANT EXECUTE ON dbms_obfuscation_toolkit_ffi to public;
GRANT EXECUTE ON dbms_crypto_ffi to public;
Encryption/Decryption Using DBMS_CRYPTO
 v_credit_card_no      VARCHAR2(19) := '1111-2222-3333-4444';
 v_credit_card_no_raw  RAW(128) := utl_raw.cast_to_raw(v_credit_card_no);
 v_key_byte_size       NUMBER := 32;
 v_encryption_key      RAW(128) := DBMS_CRYPTO.RANDOMBYTES(v_key_byte_size);
 v_encryption_type     PLS_INTEGER;
 v_encrypted_raw       RAW(2048);
 v_decrypted_raw       RAW(2048);
 v_decrypted_cc        VARCHAR2(19);

  dbms_output.put_line('Credit Card Number (CCN): ' || v_credit_card_no);

  v_encryption_type := DBMS_CRYPTO.ENCRYPT_AES256 +
                       DBMS_CRYPTO.CHAIN_CBC +

  v_encrypted_raw := dbms_crypto.encrypt(
                       src => UTL_I18N.STRING_TO_RAW(v_credit_card_no_raw,'AL32UTF8'),
                       typ => v_encryption_type,
                       key => v_encryption_key);

  dbms_output.put_line('Encrypted CCN: ' ||v_encrypted_raw);

  v_decrypted_raw := dbms_crypto.decrypt(src => v_encrypted_raw,
                       typ => v_encryption_type,
                       key => v_encryption_key);

  -- dbms_output.put_line('Decrypted CCN: ' ||
  --             UTL_I18N.RAW_TO_CHAR(v_decrypted_raw, 'AL32UTF8'));

  v_decrypted_cc := UTL_RAW.cast_to_varchar2(UTL_I18N.RAW_TO_CHAR(v_decrypted_raw,
  dbms_output.put_line('Decrypted CCN: ' ||v_decrypted_cc);
Encryption/Decryption I/O (above PL/SQL anonymous block as a script)

Encryption, Decryption Output

Oracle registered trademark of Oracle Corporation.

Last Revised On: December 08, 2013