Here is my recent PL/SQL package to send email directly from Oracle database with attachment or without attachment. I used other UTL packages before but this one UTL_TCP looks better. I am using it and found very efficient.
Note: please review it, test it before using any production environment. thanks.
If you face ORA-24247: network access denied by access control list (ACL) . please find the solution in below link-
https://halimdba.blogspot.com/2015/02/ora-24247-network-access-denied-by.html
SQL> select * from dba_network_acls;
1) Package specification
CREATE OR REPLACE PACKAGE SAY_HELLO_FROM_DB
IS
PROCEDURE pro_send_emails_with_attach (
sender_email_address VARCHAR2,
recipient_email_addresses VARCHAR2,
email_subject VARCHAR2,
email_body_text VARCHAR2,
email_attachment_text CLOB,
email_attachment_file_name VARCHAR2);
PROCEDURE pro_send_emails_without_attach (
sender_email_address VARCHAR2,
recipient_email_addresses VARCHAR2,
email_subject VARCHAR2,
email_body_text VARCHAR2);
END;
/
2) Package body
CREATE OR REPLACE PACKAGE BODY SAY_HELLO_FROM_DB
IS
PROCEDURE pro_send_emails_with_attach (
sender_email_address VARCHAR2,
recipient_email_addresses VARCHAR2,
email_subject VARCHAR2,
email_body_text VARCHAR2,
email_attachment_text CLOB,
email_attachment_file_name VARCHAR2)
IS
c UTL_TCP.connection;
rc BINARY_INTEGER;
v_next_column BINARY_INTEGER;
v_recipient_email_length BINARY_INTEGER;
v_recipient_email_addresses VARCHAR2 (500);
v_single_recipient_email_addr VARCHAR2 (100);
j BINARY_INTEGER;
current_position BINARY_INTEGER := 1;
email_attachment_text_segment CLOB;
--- v_date varchar2(100);
BEGIN
v_recipient_email_addresses := recipient_email_addresses;
v_recipient_email_length := LENGTH (v_recipient_email_addresses);
v_recipient_email_addresses := v_recipient_email_addresses || ',';
v_next_column := 1;
IF INSTR (v_recipient_email_addresses, ',') = 0
THEN
v_single_recipient_email_addr := v_recipient_email_addresses;
v_recipient_email_length := 1;
END IF;
-- -- CHANGE SMTP SERVER'S ip AND port
c := UTL_TCP.open_connection ('192.168.X.XXX', 25);
rc := UTL_TCP.write_line (c, 'HELO localhost');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
WHILE v_next_column <= v_recipient_email_length
LOOP
v_single_recipient_email_addr :=
SUBSTR (
v_recipient_email_addresses,
v_next_column,
INSTR (v_recipient_email_addresses, ',', v_next_column)
- v_next_column);
v_next_column :=
INSTR (v_recipient_email_addresses, ',', v_next_column) + 1;
rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || sender_email_address);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc :=
UTL_TCP.write_line (c,
'RCPT TO: ' || v_single_recipient_email_addr);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'DATA');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc :=
UTL_TCP.write_line (
c,
'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));
---- v_date := 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') ;
rc := UTL_TCP.write_line (c, 'From: ' || sender_email_address);
rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');
rc := UTL_TCP.write_line (c, 'To: ' || v_single_recipient_email_addr);
rc := UTL_TCP.write_line (c, 'Subject: ' || email_subject);
rc := UTL_TCP.write_line (c, 'Content-Type: multipart/mixed;');
rc := UTL_TCP.write_line (c, ' boundary="-----SECBOUND"');
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, '-------SECBOUND');
rc := UTL_TCP.write_line (c, 'Content-Type: text/plain');
rc := UTL_TCP.write_line (c, 'Content-Transfer-Encoding: 7bit');
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, email_body_text);
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, '-------SECBOUND');
rc := UTL_TCP.write_line (c, 'Content-Type: text/plain;');
rc :=
UTL_TCP.write_line (
c,
' name="' || email_attachment_file_name || '"');
rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');
rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');
rc := UTL_TCP.write_line (c, '');
current_position := 1;
j := (ROUND (LENGTH (email_attachment_text)) / 32000) + 1;
DBMS_OUTPUT.put_line (
'j= ' || j || ' length= ' || LENGTH (email_attachment_text));
FOR i IN 1 .. j
LOOP
email_attachment_text_segment :=
SUBSTR (email_attachment_text, current_position, 32000);
rc := UTL_TCP.write_line (c, email_attachment_text_segment);
current_position := current_position + 32000;
END LOOP;
rc := UTL_TCP.write_line (c, '-------SECBOUND--');
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, '.');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
DBMS_OUTPUT.put_line (
'***** Single E-mail= '
|| v_single_recipient_email_addr
|| ' '
|| v_next_column);
END LOOP;
rc := UTL_TCP.write_line (c, 'QUIT');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
UTL_TCP.close_connection (c);
END;
PROCEDURE pro_send_emails_without_attach (
sender_email_address VARCHAR2,
recipient_email_addresses VARCHAR2,
email_subject VARCHAR2,
email_body_text VARCHAR2)
IS
c UTL_TCP.connection;
rc BINARY_INTEGER;
v_next_column BINARY_INTEGER;
v_recipient_email_length BINARY_INTEGER;
v_recipient_email_addresses VARCHAR2 (500);
v_single_recipient_email_addr VARCHAR2 (100);
BEGIN
v_recipient_email_addresses := recipient_email_addresses;
v_recipient_email_length := LENGTH (v_recipient_email_addresses);
v_recipient_email_addresses := v_recipient_email_addresses || ',';
v_next_column := 1;
IF INSTR (v_recipient_email_addresses, ',') = 0
THEN
v_single_recipient_email_addr := v_recipient_email_addresses;
v_recipient_email_length := 1;
END IF;
-- CHANGE SMTP SERVER'S ip AND port
c := UTL_TCP.open_connection ('192.168.XX.XX', 25);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'HELO localhost');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
WHILE v_next_column <= v_recipient_email_length
LOOP
v_single_recipient_email_addr :=
SUBSTR (
v_recipient_email_addresses,
v_next_column,
INSTR (v_recipient_email_addresses, ',', v_next_column)
- v_next_column);
v_next_column :=
INSTR (v_recipient_email_addresses, ',', v_next_column) + 1;
rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || sender_email_address);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc :=
UTL_TCP.write_line (c,
'RCPT TO: ' || v_single_recipient_email_addr);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'DATA');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc :=
UTL_TCP.write_line (
c,
'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));
rc := UTL_TCP.write_line (c, 'From: ' || sender_email_address);
rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');
rc := UTL_TCP.write_line (c, 'To: ' || v_single_recipient_email_addr);
rc := UTL_TCP.write_line (c, 'Subject: ' || email_subject);
rc := UTL_TCP.write_line (c, email_body_text);
rc := UTL_TCP.write_line (c, '.');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
DBMS_OUTPUT.put_line (
'***** Single E-mail= '
|| v_single_recipient_email_addr
|| ' '
|| v_next_column);
END LOOP;
rc := UTL_TCP.write_line (c, 'QUIT');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
UTL_TCP.close_connection (c);
END;
END;
/
3) Calling the procedure for this package for sending email
If you want send to multiple person, please use comma separated email address as below
In SQL plus -
exec SAY_HELLO_FROM_DB.pro_send_emails_with_attach('Halim@test.com','Halim1@test.com,Halim2@test.com','Test From Database','Check the alter log','have you checked alertlog today?','attention');
