A simple and secured way to encrypt and decrypt data in Oracle 11g with DBMS_OBFUSCATION_TOOLKIT package. DBMS_OBFUSCATION_TOOLKIT enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms.

The following Toolkit package uses DBMS_OBFUSCATION_TOOLKIT package to encrypt and decrypt data.

Note: You must modify the key defined in this package to have your custom secret encryption method. The data is encrypted by the key can be decrypt only with that key. Learn More...

To encrypt data:
select toolkit.encrypt('The test data 123.') from dual;
To decrypt data:
select Toolkit.decrypt('5C8ADD7B98919FC937A53AB986782EA9EE63D4ADDF7CA322') from dual;
The test data 123.

Create Or Replace PACKAGE Toolkit As
  FUNCTION encrypt (p_text  In  VARCHAR2) RETURN RAW;
  FUNCTION decrypt (p_raw  In  RAW) RETURN VARCHAR2;
END Toolkit;

Create Or Replace PACKAGE BODY Toolkit As
  ---- the custom key for
  g_key     RAW(32767)  := UTL_RAW.cast_to_raw('20130417');
  ---- the custom padding character
  g_pad_chr VARCHAR2(1) := '_';

  PROCEDURE padstring (p_text  In OUT  VARCHAR2);

  FUNCTION encrypt (p_text  In  VARCHAR2) RETURN RAW Is
    l_text       VARCHAR2(32767) := p_text;
    l_encrypted  RAW(32767);
    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input          => UTL_RAW.cast_to_raw(l_text),
                                        Key            => g_key,
                                        encrypted_data => l_encrypted);
    RETURN l_encrypted;

  FUNCTION decrypt (p_raw  In  RAW) RETURN VARCHAR2 Is
    l_decrypted  VARCHAR2(32767);
    DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        Key   => g_key,
                                        decrypted_data => l_decrypted);
    RETURN Rtrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);

  PROCEDURE padstring (p_text  In OUT  VARCHAR2) Is
    l_units  NUMBER;
    IF Length(p_text) Mod 8 > 0 Then
      l_units := Trunc(Length(p_text)/8) + 1;
      p_text  := Rpad(p_text, l_units * 8, g_pad_chr);
    END IF;

END Toolkit;

Oracle Forms Recipes eBookDownload

Secure Encryption and decryption of data in Oracle using DBMS_OBFUSCATION_TOOLKIT package example.

Post a Comment

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.