Saturday, March 17, 2012

DBMS_OBFUSCATION_TOOLKIT - Script to ENCRYPT/DECRYPT using ORACLE PLSQL




This post will give you a sample example script to ENCRYPT and DECRYPT a data using the ORACLE PL/SQL utility named DBMS_OBFUSCATION_TOOLKIT

Below are the important points to be noted down,
If the input data or key given to the PL/SQL DESDECRYPT function is empty, then Oracle raises ORA error 28231 "Invalid input to Obfuscation toolkit."
If the input data given to the DESDECRYPT function is not a multiple of 8 bytes, Oracle raises ORA error 28232 "Invalid input size for Obfuscation toolkit."
If the key length is missing or is less than 8 bytes, then the procedure raises the error ORA-28234 "Key length too short." Note that if larger keys are used, extra bytes are ignored. So a 9-byte key will not generate an exception.



SCRIPT:
SET serveroutput ON;

DECLARE
   lv_encrypted_data              VARCHAR2 (2000);
   lv_decrypted_data              VARCHAR2 (2000);
   piv_str                        VARCHAR2 (2000) := 'www.shareoracleapps.com';
   piv_pass_key                   VARCHAR2 (2000) := 'teamsearch';
   a_var                          VARCHAR2 (100);
   error_in_input_buffer_length   EXCEPTION;
   PRAGMA EXCEPTION_INIT (error_in_input_buffer_length, -28232);
   input_buffer_length_err_msg    VARCHAR2 (100)
                      := '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';
BEGIN
   DBMS_OUTPUT.put_line (   'Input_string->:'
                         || piv_str
                         || CHR (10)
                         || 'LENGTH OF STRING=>'
                         || LENGTH (piv_str)
                        );

--  Since DES needs data to be in multples of 8 bytes we had padded the data, if the
--  data did not meet the 8 bytes boundry requirment. So now after decrypting we
--  would need to remove the padding if it exists
   IF MOD (LENGTH (piv_str), 8) != 0
   THEN
      piv_str :=
         RPAD (piv_str,
               LENGTH (piv_str) + 8 - MOD (LENGTH (piv_str), 8),
               CHR (0)
              );
   END IF;

   lv_encrypted_data :=
      DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string      => piv_str,
                                           key_string        => piv_pass_key
                                          );
   DBMS_OUTPUT.put_line ('Encrypted Data: ' || lv_encrypted_data);
   lv_decrypted_data :=
      DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string      => lv_encrypted_data,
                                           key_string        => piv_pass_key
                                          );
   -- This is to remove the padded chracters
   lv_decrypted_data :=
       SUBSTR (lv_decrypted_data, 1, INSTR (lv_decrypted_data, CHR (0), 1) - 1);
   DBMS_OUTPUT.put_line (   'decrypt_string->:'
                         || lv_decrypted_data
                         || CHR (10)
                         || 'LENGTH OF STRING=>'
                         || LENGTH (lv_decrypted_data)
                        );
EXCEPTION
   WHEN error_in_input_buffer_length
   THEN
      DBMS_OUTPUT.put_line ('> ' || input_buffer_length_err_msg);
END;



Saturday, March 17, 2012 by Team search · 2

Script to Send Email Using Oracle PLSQL (UTL_SMTP)


This post gives you an example script to send an E-Mail from Oracle PL/SQL using the UTL_SMTP.

SCRIPT:



SET SERVEROUTPUT ON; 
DECLARE
   l_err_msg            VARCHAR2 (2000)     := NULL;
   l_email_text         VARCHAR2 (32000)    DEFAULT NULL;
   p_host_id_address    VARCHAR2 (100);
   p_remittance_email   VARCHAR2 (200)      := 'xyz@shareoracleapps.com';
   l_new_line           VARCHAR2 (2)        := UTL_TCP.crlf;
   l_connection         UTL_SMTP.connection;
BEGIN
   BEGIN
      SELECT UTL_INADDR.get_host_address ('mailhost.shareoracleapps.com')
        INTO p_host_id_address
        FROM DUAL;
   END;

   l_connection := UTL_SMTP.open_connection (p_host_id_address, 25);
   UTL_SMTP.helo (l_connection, p_host_id_address);
   UTL_SMTP.mail (l_connection, 'Teamsearch@shareoracleapps.com');
   UTL_SMTP.rcpt (l_connection, p_remittance_email);
   l_email_text :=
         l_email_text
      || 'From: '
      || 'teamsearch@shareoracleapps.com'
      || l_new_line
      || 'Subject: Test Email'
      || l_new_line
      || 'To: '
      || p_remittance_email
      || l_new_line
      || 'Content-Type: text/html'
      || l_new_line
      || l_new_line
      || '<HTML><BODY><P><PRE>'
      || l_new_line
      || 'This is Blog contains the details about Oracle apps Technical and Functional'
      || '</PRE></P></BODY></HTML>';
   fnd_file.put_line (fnd_file.LOG, 'Calling UTL_SMTP.DATA');
   UTL_SMTP.DATA (l_connection, l_email_text);
   DBMS_OUTPUT.put_line ('Calling UTL_SMTP.quit');
   UTL_SMTP.quit (l_connection);
   DBMS_OUTPUT.put_line ('Email Sent Successfull.');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Unexpected Error Occured  = '
                            || l_err_msg
                            || SUBSTR (SQLERRM, 1, 200)
                           );
END send_email_detail;


by Team search · 1

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.