Saturday, March 17, 2012
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
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
Subscribe to:
Posts (Atom)
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.