Oracle Database: How to Send mails from Oracle Database using UTL

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:-
1. Mails without attachment.
2. Mails with attachment.

Mails without attachment :-

SQL> DECLARE
c UTL_SMTP.CONNECTION;
2 3
4 PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
5 BEGIN
6 UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
7 END;
8
9 BEGIN
10 c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
11 UTL_SMTP.HELO(c, '10.10.1.10');
12 UTL_SMTP.MAIL(c, 'amit.rath@example.com');
13 UTL_SMTP.RCPT(c, 'amit.rath@example.com');
14 UTL_SMTP.OPEN_DATA(c);
15 send_header('From', '"Sender" <amit.rath@example.com>');
16 send_header('To', '"Recipient" <amit.rath@example.com>');
17 send_header('Subject', 'Hello');
18 UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
19 UTL_SMTP.CLOSE_DATA(c);
20 UTL_SMTP.QUIT(c);
21 EXCEPTION
22 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
23 BEGIN
24 UTL_SMTP.QUIT(c);
25 EXCEPTION
26 WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
27 NULL; -- When the SMTP server is down or unavailable, we don't have
28 -- a connection to the server. The QUIT call will raise an
29 -- exception that we can ignore.
30 END;
31 raise_application_error(-20000,
32 'Failed to send mail due to the following error: ' || sqlerrm);
33 END;
34 /

DECLARE
*
ERROR at line 1:
ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access
denied by access control list (ACL)
ORA-06512: at line 25

If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database 11g we have to configure ACL in order to use PL/SQL packages. To know more about creating and configuring ACL, go to create and configure ACLs in Oracle database.

Once we configure ACL then we are able to successfully send mails using bove mentioned code. PFB :-

SQL> DECLARE
c UTL_SMTP.CONNECTION;
2 3
4 PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
5 BEGIN
6 UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
7 END;
8
9 BEGIN
10 c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
11 UTL_SMTP.HELO(c, '10.10.1.10');
12 UTL_SMTP.MAIL(c, 'amit.rath@example.com');
13 UTL_SMTP.RCPT(c, 'amit.rath@example.com');
14 UTL_SMTP.OPEN_DATA(c);
15 send_header('From', '"Sender" <amit.rath@example.com>');
16 send_header('To', '"Recipient" <amit.rath@example.com>');
17 send_header('Subject', 'Hello');
18 UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
19 UTL_SMTP.CLOSE_DATA(c);
20 UTL_SMTP.QUIT(c);
21 EXCEPTION
22 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
23 BEGIN
24 UTL_SMTP.QUIT(c);
25 EXCEPTION
26 WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
27 NULL; -- When the SMTP server is down or unavailable, we don't have
28 -- a connection to the server. The QUIT call will raise an
29 -- exception that we can ignore.
30 END;
31 raise_application_error(-20000,
32 'Failed to send mail due to the following error: ' || sqlerrm);
33 END;
34 /

PL/SQL procedure successfully completed.

Mail has been send.

2. 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 /amit/report

/amit/report has to be physically present in FS 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 AMIT_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 AMIT_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 AMIT_ATTACH_ADDRESS(smtp_host IN VARCHAR2,addr_list IN OUT VARCHAR2) RETURN VARCHAR2 ;

PROCEDURE AMIT_ATTACH_SPLITPATH(file_path IN VARCHAR2, directory_path OUT VARCHAR2,file_name OUT VARCHAR2);

PROCEDURE AMIT_ATTACH_APPEND(directory_path IN VARCHAR2, file_name IN VARCHAR2,file_type IN VARCHAR2,

conn IN OUT UTL_SMTP.CONNECTION);

END AMIT_ATTACH;

CREATE OR REPLACE PACKAGE BODY AMIT_ATTACH

----------------------------------------------------------------------------------------------------------------------

FUNCTION AMIT_ATTACH_ADDRESS (smtp_host IN VARCHAR2, addr_list IN OUT VARCHAR2)

RETURN VARCHAR2

addr VARCHAR2 (256);

i PLS_INTEGER;

FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)

RETURN PLS_INTEGER

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;

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 AMIT_ATTACH_SPLITPATH (

file_path IN VARCHAR2,

directory_path OUT VARCHAR2,

file_name OUT VARCHAR2

)

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 AMIT_ATTACH_APPEND (

directory_path IN VARCHAR2,

file_name IN VARCHAR2,

file_type IN VARCHAR2,

conn IN OUT UTL_SMTP.connection

)

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

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 AMIT_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'

)

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, AMIT_ATTACH_ADDRESS (smtp_host, recipients));

recipients := to_names;

WHILE recipients IS NOT NULL

LOOP

UTL_SMTP.rcpt (conn, AMIT_ATTACH_ADDRESS (smtp_host, recipients));

END LOOP;

recipients := cc_names;

WHILE recipients IS NOT NULL

LOOP

UTL_SMTP.rcpt (conn, AMIT_ATTACH_ADDRESS (smtp_host, recipients));

END LOOP;

recipients := bcc_names;

WHILE recipients IS NOT NULL

LOOP

UTL_SMTP.rcpt (conn, AMIT_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

|| '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

AMIT_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);

AMIT_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 AMIT_ATTACH;

3. Call Email procedure to send amit.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 := 'eample_attach@example.com';

TO_NAME := 'amit@example.com';

SUBJECT := 'TEST FILE';

MESSAGE := 'Hi this is a test message

.';

CC_NAMES := NULL;
BCC_NAMES := NULL;

FILETYPE := 'text';

SELECT ' /amit/report/amit.xls' INTO FILENAME FROM dual;

AMIT_ATTACH.AMIT_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 amit.xls.

I hope this article helped you.

Regards,
Amit Rath

Oracle Database

About Me

Wednesday, February 5, 2014

How to Send mails from Oracle Database using UTL_SMTP

1 comment:

Popular Posts

Followers

Labels