UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).
We can send two types of mails using UTL_SMTP:-
- Mails without attachment.
- Mails with attachment.
Sending Mails with attachment :-
1. First check UTL_FILE_DIR parameter in Oracle Database :-
SQL> show parameter utl_file NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ utl_file_dir string /america/report
/america/report has to be physically present in Files and attachment need to be mailed has to be present in this directory.
2. Create a package to send mail with attachment :-
CREATE OR REPLACE PACKAGE AMERICA_ATTACH IS smtp_port NUMBER := 25; boundary CONSTANT VARCHAR2(256) := 'CES.Boundary.DACA587499938898'; crlf VARCHAR2(2):= CHR(13) || CHR(10); my_code NUMBER; my_errm VARCHAR2(32767); PROCEDURE AMERICA_ATTACH_EMAIL(smtp VARCHAR2, from_name VARCHAR2,to_names VARCHAR2,subject VARCHAR2 DEFAULT '', message VARCHAR2 DEFAULT NULL,cc_names VARCHAR2 DEFAULT NULL, bcc_names VARCHAR2 DEFAULT NULL,filename VARCHAR2 DEFAULT NULL, filetype VARCHAR2 DEFAULT 'text/plain'); FUNCTION AMERICA_ATTACH_ADDRESS(smtp_host IN VARCHAR2,addr_list IN OUT VARCHAR2) RETURN VARCHAR2 ; PROCEDURE AMERICA_ATTACH_SPLITPATH(file_path IN VARCHAR2, directory_path OUT VARCHAR2,file_name OUT VARCHAR2); PROCEDURE AMERICA_ATTACH_APPEND(directory_path IN VARCHAR2, file_name IN VARCHAR2,file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION); END AMERICA_ATTACH; /
CREATE OR REPLACE PACKAGE BODY AMERICA_ATTACH AS FUNCTION AMERICA_ATTACH_ADDRESS (smtp_host IN VARCHAR2, addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS addr VARCHAR2 (256); i PLS_INTEGER; FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2) RETURN PLS_INTEGER IS c VARCHAR2 (5); i PLS_INTEGER; len PLS_INTEGER; inside_quote BOOLEAN; BEGIN inside_quote := FALSE; i := 1; len := LENGTH (str); WHILE (i <= len) LOOP c := SUBSTR (str, i, 1); IF (inside_quote) THEN IF (c = '"') THEN inside_quote := FALSE; ELSIF (c = '\') THEN i := i + 1; -- END IF; GOTO next_char; END IF; IF (c = '"') THEN inside_quote := TRUE; GOTO next_char; END IF; IF (INSTR (chrs, c) >= 1) THEN RETURN i; END IF; <<next_char>> i := i + 1; END LOOP; RETURN 0; END; BEGIN addr_list := LTRIM (addr_list); i := lookup_unquoted_char (addr_list, ',;'); IF (i >= 1) THEN addr := SUBSTR (addr_list, 1, i - 1); addr_list := SUBSTR (addr_list, i + 1); ELSE addr := addr_list; addr_list := ''; END IF; i := lookup_unquoted_char (addr, '<'); IF (i >= 1) THEN addr := SUBSTR (addr, i + 1); i := INSTR (addr, '>'); IF (i >= 1) THEN addr := SUBSTR (addr, 1, i - 1); END IF; END IF; i := lookup_unquoted_char (addr, '@'); IF (i = 0 AND smtp_host != 'localhost') THEN i := INSTR (smtp_host, '.', -1, 2); addr := addr || '@' || SUBSTR (smtp_host, i + 1); END IF; addr := '<' || addr || '>'; RETURN addr; END;
PROCEDURE AMERICA_ATTACH_SPLITPATH ( file_path IN VARCHAR2, directory_path OUT VARCHAR2, file_name OUT VARCHAR2 ) IS pos NUMBER; BEGIN pos := INSTR (file_path, '/', -1); IF pos = 0 THEN pos := INSTR (file_path, '\', -1); END IF; IF pos = 0 THEN directory_path := NULL; ELSE directory_path := SUBSTR (file_path, 1, pos - 1); END IF; file_name := SUBSTR (file_path, pos + 1); END;
PROCEDURE AMERICA_ATTACH_APPEND ( directory_path IN VARCHAR2, file_name IN VARCHAR2, file_type IN VARCHAR2, conn IN OUT UTL_SMTP.connection ) IS file_handle UTL_FILE.file_type; bfile_handle BFILE; bfile_len NUMBER; pos NUMBER; read_bytes NUMBER; line VARCHAR2 (1000); DATA RAW (200); my_code NUMBER; my_errm VARCHAR2 (32767); directory_name VARCHAR2 (30); BEGIN BEGIN BEGIN line := directory_path; SELECT dd.directory_name INTO directory_name FROM SYS.all_directories dd WHERE dd.directory_path = line AND ROWNUM =1; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line (line || 'does not have valid directorty'); IF SUBSTR (file_type, 1, 4) != 'text' THEN bfile_handle := BFILENAME (directory_name, file_name); bfile_len := DBMS_LOB.getlength (bfile_handle); pos := 1; DBMS_LOB.OPEN (bfile_handle, DBMS_LOB.lob_readonly); ELSE file_handle := UTL_FILE.fopen (directory_name, file_name, 'r'); END IF; LOOP IF SUBSTR (file_type, 1, 4) != 'text' THEN IF pos + 57 - 1 > bfile_len THEN read_bytes := bfile_len - pos + 1; ELSE read_bytes := 57; END IF; DBMS_LOB.READ (bfile_handle, read_bytes, pos, DATA); UTL_SMTP.write_raw_data (conn, UTL_ENCODE.base64_encode (DATA)); pos := pos + 57; IF pos > bfile_len THEN EXIT; END IF; ELSE UTL_FILE.get_line (file_handle, line); UTL_SMTP.write_data (conn, line || crlf); END IF; END LOOP; -- Output any errors, except at end when no more data is found EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN my_code := SQLCODE; my_errm := SQLERRM; DBMS_OUTPUT.put_line ('Error code ' || my_code || ': ' || my_errm); END; -- Close the file (binary or text) IF SUBSTR (file_type, 1, 4) != 'text' THEN DBMS_LOB.CLOSE (bfile_handle); ELSE UTL_FILE.fclose (file_handle); END IF; END;
PROCEDURE AMERICA_ATTACH_EMAIL ( smtp VARCHAR2, from_name VARCHAR2, to_names VARCHAR2, subject VARCHAR2 DEFAULT '', MESSAGE VARCHAR2 DEFAULT NULL, cc_names VARCHAR2 DEFAULT NULL, bcc_names VARCHAR2 DEFAULT NULL, filename VARCHAR2 DEFAULT NULL, filetype VARCHAR2 DEFAULT 'text/plain' ) IS smtp_host VARCHAR2 (256) := smtp; recipients VARCHAR2 (32767); directory_path VARCHAR2 (256); file_name VARCHAR2 (256); mesg VARCHAR2 (32767); conn UTL_SMTP.connection; i BINARY_INTEGER; BEGIN conn := UTL_SMTP.open_connection (smtp_host, smtp_port); UTL_SMTP.helo (conn, smtp_host); recipients := from_name; UTL_SMTP.mail (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients)); recipients := to_names; WHILE recipients IS NOT NULL LOOP UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients)); END LOOP; recipients := cc_names; WHILE recipients IS NOT NULL LOOP UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients)); END LOOP; recipients := bcc_names; WHILE recipients IS NOT NULL LOOP UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients)); END LOOP; UTL_SMTP.open_data (conn); mesg := 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf || 'From: ' || from_name || crlf || 'Subject: ' || subject || crlf || 'To: ' || to_names || crlf; IF cc_names IS NOT NULL THEN mesg := mesg || 'Cc: ' || cc_names || crlf; END IF; IF bcc_names IS NOT NULL THEN mesg := mesg || 'Bcc: ' || bcc_names || crlf; END IF; mesg := mesg || 'Mime-Version: 1.0' || crlf || 'Content-Type: multipart/mixed; boundary="' || boundary || '"' || crlf || crlf || 'This is a Mime message.' || crlf || crlf; UTL_SMTP.write_data (conn, mesg); IF MESSAGE IS NOT NULL THEN mesg := '--' || boundary || crlf || 'Content-Type: text/plain' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf; UTL_SMTP.write_data (conn, mesg); UTL_SMTP.write_data (conn, MESSAGE || crlf); END IF; IF filename IS NOT NULL THEN AMERICA_ATTACH_SPLITPATH (filename, directory_path, file_name); mesg := crlf || '--' || boundary || crlf; IF SUBSTR (filetype, 1, 4) != 'text' THEN mesg := mesg || 'Content-Type: ' || filetype || '; name="' || file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || file_name || '"' || crlf || 'Content-Transfer-Encoding: base64' || crlf || crlf; ELSE mesg := mesg || 'Content-Type: application/octet-stream; name="' || file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || file_name || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf; END IF; UTL_SMTP.write_data (conn, mesg); AMERICA_ATTACH_APPEND (directory_path, file_name, filetype, conn); UTL_SMTP.write_data (conn, crlf); END IF; mesg := crlf || '--' || boundary || '--' || crlf; UTL_SMTP.write_data (conn, mesg); UTL_SMTP.close_data (conn); UTL_SMTP.quit (conn); EXCEPTION WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN my_code := SQLCODE; my_errm := SQLERRM; BEGIN UTL_SMTP.quit (conn); EXCEPTION WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN NULL; END; raise_application_error (-20000, 'Failed to send mail - SMTP server down or unavailable: Error code ' || my_code || ': ' || my_errm ); WHEN OTHERS THEN my_code := SQLCODE; my_errm := SQLERRM; raise_application_error (-20000, 'Failed to send mail: Error code ' || my_code || ': ' || my_errm ); END; END AMERICA_ATTACH; /
3. Call Email procedure to send america.xls file to desired user :-
DECLARE SMTP VARCHAR2(200); FROM_NAME VARCHAR2(200); TO_NAME VARCHAR2(2000); SUBJECT VARCHAR2(200); MESSAGE VARCHAR2(2000); CC_NAMES VARCHAR2(200); BCC_NAMES VARCHAR2(200); FILENAME VARCHAR2(200); FILETYPE VARCHAR2(200); BEGIN SMTP := '10.10.4.10'; FROM_NAME := '[email protected]'; TO_NAME := '[email protected]'; SUBJECT := 'TEST FILE'; MESSAGE := 'Hi this is a test message.'; CC_NAMES := NULL; BCC_NAMES := NULL; FILETYPE := 'text'; SELECT ' /america/report/america.xls' INTO FILENAME FROM dual; AMERICA_ATTACH.AMERICA_ATTACH_EMAIL ( SMTP, FROM_NAME, TO_NAME, SUBJECT, MESSAGE, CC_NAMES, BCC_NAMES, FILENAME, FILETYPE ); COMMIT; END; / PL/SQL procedure successfully completed.
Mail has been sent with attachment america.xls.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8