PL-SQL Procedure to send email with attachment
How to send email using plsql
How to send email with attachment using plsql
oracle send email from database
oracle send email with attachment utl_smtp
1. Compile the below procedure XXSEND_EMAIL in the database
CREATE OR REPLACE PROCEDURE XXSEND_EMAIL (p_filename1 IN VARCHAR2,
v_subjectline IN VARCHAR2,
o_errbuf OUT VARCHAR2,
o_retcode OUT NUMBER)
AS
CONN UTL_SMTP.connection;
v_msg_text VARCHAR2 (8000) := NULL;
v_msg_from VARCHAR2 (100);
v_mailhost VARCHAR2 (100);
v_msg_subject VARCHAR2 (100) := v_subjectline;
v_msg_to VARCHAR2 (1000);
v_msg_rcpt VARCHAR2 (1000);
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2 (200)
INDEX BY BINARY_INTEGER;
FILE_ARRAY VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/
I BINARY_INTEGER; /** ARRAY INDEX **/
L_FILE_HANDLE UTL_FILE.FILE_TYPE; /** FILE POINTER **/
L_DIRECTORY_NAME VARCHAR2 (200) := fnd_profile.VALUE ('UTIL_PATH'); /** TO STORE THE PATH / DIRECTORY NAME OF THE FILE **/
l_attachment LONG; -- VARCHAR2(32767); /** TO STORE THE MESSAGE **/
L_LINE VARCHAR2 (1000); /** TO STORE THE CONTENTS OF THE LINE READ FROM THE FILE **/
L_FILE_NAME VARCHAR2 (100); /** TO STORE THE FILENAME **/
reply UTL_SMTP.reply;
i_idx NUMBER := 0;
v_osuser VARCHAR2 (100);
v_serverhost VARCHAR2 (100);
CURSOR cur_mail_id
IS /*select recipients mail ids from table name*/
SELECT 'example@email.com' FROM DUAL;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Sending Email...');
v_msg_from := 'noreply@example.com'; -- from mail id
BEGIN
SELECT fscpv.parameter_value smtp_host
INTO v_mailhost
FROM fnd_svc_comp_params_b fscpb,
fnd_svc_comp_param_vals fscpv,
fnd_svc_components fsc
WHERE fscpb.parameter_id = fscpv.parameter_id
AND fscpv.component_id = fsc.component_id
AND fscpb.parameter_name = 'OUTBOUND_SERVER'
AND fsc.component_name = 'Workflow Notification Mailer';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_errbuf := ' SMTP HOST not found:' || SQLERRM;
o_retcode := 1;
RAISE;
END;
BEGIN
BEGIN
-- Open the connection
BEGIN
i_idx := 0;
WHILE i_idx < 5
LOOP
reply := UTL_SMTP.open_connection (v_mailhost, 25, conn);
IF reply.code BETWEEN 400 AND 599
THEN
fnd_file.put_line (
fnd_file.LOG,
'Retrying to open connection: ' || TO_CHAR (i_idx + 1));
fnd_file.put_line (fnd_file.LOG, reply.text);
ELSE
EXIT;
END IF;
i_idx := i_idx + 1;
END LOOP;
IF i_idx >= 5
THEN
RAISE_APPLICATION_ERROR (
-20010,
'Could not establish SMTP connection: '
|| reply.code
|| ' '
|| reply.text);
END IF;
EXCEPTION
WHEN OTHERS
THEN
o_errbuf := 'error :' || SQLERRM;
o_retcode := 1;
RAISE;
END;
-- Check for handshake
BEGIN
i_idx := 0;
WHILE i_idx < 5
LOOP
reply := UTL_SMTP.helo (conn, v_mailhost);
IF reply.code BETWEEN 400 AND 599
THEN
fnd_file.put_line (
fnd_file.LOG,
'Retrying helo: ' || TO_CHAR (i_idx + 1));
fnd_file.put_line (fnd_file.LOG, reply.text);
ELSE
EXIT;
END IF;
i_idx := i_idx + 1;
END LOOP;
IF i_idx >= 5
THEN
RAISE_APPLICATION_ERROR (
-20010,
'Could not establish shakehand: '
|| reply.code
|| ' '
|| reply.text);
END IF;
EXCEPTION
WHEN OTHERS
THEN
o_errbuf := 'error:' || SQLERRM;
o_retcode := 1;
RAISE;
END;
-- Add the Sender with respect to the site user's user group
BEGIN
i_idx := 0;
WHILE i_idx < 5
LOOP
reply := UTL_SMTP.Mail (conn, v_msg_from);
IF reply.code BETWEEN 400 AND 599
THEN
fnd_file.put_line (
fnd_file.LOG,
'Retrying mail: ' || TO_CHAR (i_idx + 1));
fnd_file.put_line (fnd_file.LOG, reply.text);
ELSE
EXIT;
END IF;
i_idx := i_idx + 1;
END LOOP;
IF i_idx >= 5
THEN
RAISE_APPLICATION_ERROR (
-20010,
'Could not initiate mail transaction with SMTP server: '
|| reply.code
|| ' '
|| reply.text);
END IF;
EXCEPTION
WHEN OTHERS
THEN
o_errbuf := 'error :' || SQLERRM;
o_retcode := 1;
RAISE;
END;
-- Add the reciepents
BEGIN
i_idx := 0;
WHILE i_idx < 5
LOOP
FOR rec_mail_id IN cur_mail_id
LOOP
v_msg_rcpt := rec_mail_id.description;
reply := UTL_SMTP.Rcpt (conn, v_msg_rcpt);
v_msg_to := v_msg_to || rec_mail_id.description || '; ';
END LOOP;
IF reply.code BETWEEN 400 AND 599
THEN
fnd_file.put_line (
fnd_file.LOG,
'Retrying rcpt: ' || TO_CHAR (i_idx + 1));
fnd_file.put_line (fnd_file.LOG, reply.text);
ELSE
EXIT;
END IF;
i_idx := i_idx + 1;
END LOOP;
IF i_idx >= 5
THEN
RAISE_APPLICATION_ERROR (
-20010,
'Could not initiate Rcpt: '
|| reply.code
|| ' '
|| reply.text);
END IF;
EXCEPTION
WHEN OTHERS
THEN
o_errbuf := 'error :' || SQLERRM;
o_retcode := 1;
RAISE;
END;
v_msg_to := RTRIM (v_msg_to, '; ');
-- Open the data mode
BEGIN
i_idx := 0;
WHILE i_idx < 5
LOOP
reply := UTL_SMTP.Open_data (conn);
IF reply.code BETWEEN 400 AND 599
THEN
fnd_file.put_line (
fnd_file.LOG,
'Retrying open data: ' || TO_CHAR (i_idx + 1));
fnd_file.put_line (fnd_file.LOG, reply.text);
ELSE
EXIT;
END IF;
i_idx := i_idx + 1;
END LOOP;
IF i_idx >= 5
THEN
RAISE_APPLICATION_ERROR (
-20010,
'Could not send open_data to SMTP server: '
|| reply.code
|| ' '
|| reply.text);
END IF;
EXCEPTION
WHEN OTHERS
THEN
o_errbuf := 'error :' || SQLERRM;
o_retcode := 1;
RAISE;
END;
BEGIN
-- Subject of the email
-- Message Text in HTML format
v_msg_text := UTL_TCP.crlf || 'Attachment: Wire Payment Files.';
-- Sending the header information
UTL_SMTP.Write_data (conn,
'From: ' || v_msg_from || UTL_TCP.crlf);
UTL_SMTP.Write_data (conn, 'To: ' || v_msg_to || UTL_TCP.crlf);
--utl_smtp.Write_data(conn,'Cc: ' ||'name@domain.com' ||utl_tcp.utl_tcp.crlf);
UTL_SMTP.Write_data (
conn,
'Subject: ' || v_msg_subject || UTL_TCP.crlf);
UTL_SMTP.Write_data (conn,
'MIME-Version: ' || '1.0' || UTL_TCP.crlf);
UTL_SMTP.Write_data (
conn,
'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"'
|| UTL_TCP.crlf
|| ''
|| UTL_TCP.crlf
|| '--DMW.Boundary.605592468'
|| UTL_TCP.crlf
|| 'Content-Type: text/plain;'
|| UTL_TCP.crlf
|| 'Content-Transfer_Encoding: 7bit'
|| UTL_TCP.crlf
|| UTL_TCP.crlf);
-- End of header information
UTL_SMTP.Write_data (conn, v_msg_text);
UTL_SMTP.Write_data (conn, UTL_TCP.crlf);
UTL_SMTP.Write_data (conn, UTL_TCP.crlf);
EXCEPTION
WHEN OTHERS
THEN
o_errbuf := 'error :' || SQLERRM;
o_retcode := 1;
RAISE;
END;
/*** START ATTACHING THE FILES ***/
BEGIN
FILE_ARRAY (1) := p_filename1;
FOR i IN 1 .. 1 --run the loop to attach more than one files
LOOP
IF file_array (i) IS NOT NULL
THEN
BEGIN
l_file_name := FILE_ARRAY (i);
l_file_handle :=
UTL_FILE.fopen (l_directory_name, l_file_name, 'R');
l_attachment :=
UTL_TCP.crlf
|| '--DMW.Boundary.605592468'
|| UTL_TCP.crlf
|| 'Content-Type: application/octet-stream; name="'
|| l_file_name
|| '"'
|| UTL_TCP.crlf
|| 'Content-Disposition: attachment; filename="'
|| l_file_name
|| '"'
|| UTL_TCP.crlf
|| 'Content-Transfer-Encoding: 7bit'
|| UTL_TCP.crlf
|| UTL_TCP.crlf;
UTL_SMTP.write_data (conn, l_attachment);
LOOP
BEGIN
UTL_FILE.get_line (l_file_handle, l_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
l_attachment := l_line || UTL_TCP.crlf;
UTL_SMTP.write_data (conn, l_attachment);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (
fnd_file.LOG,
' Invalid Operation in Mail attempt using UTL_SMTP:'
|| SQLERRM);
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (
fnd_file.LOG,
' Invalid Operation in Mail attempt using UTL_SMTP:'
|| SQLERRM);
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Invalid Operation in Mail attempt using UTL_SMTP:'
|| SQLERRM);
END;
UTL_FILE.fclose (l_file_handle);
END IF;
END LOOP;
l_attachment :=
UTL_TCP.crlf || '--DMW.Boundary.605592468--' || UTL_TCP.crlf;
UTL_SMTP.write_data (conn, l_attachment);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Invalid Operation in Mail attempt using UTL_SMTP:'
|| SQLERRM);
END;
UTL_SMTP.Close_data (conn);
UTL_SMTP.Quit (conn);
COMMIT;
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Invalid Operation in Mail attempt using UTL_SMTP:' || SQLERRM);
END;
END XXSEND_EMAIL;
/
2. Call the procedure with below anonymous block
DECLARE
o_errbuf VARCHAR2 (2000);
o_retcode NUMBER;
BEGIN
XXSEND_EMAIL ('testfile.pdf',
'Test Subject',
o_errbuf,
o_retcode);
DBMS_OUTPUT.put_line (o_errbuf);
END;
- Please note that the Workflow mailer should be up and running in order this procedure to work
- Please note that the file location should be registered as util file location in the database in order to send the attachment
Related posts:
Upload your own post and refer it anywhere anytime:
Like this:
Like Loading...
Getting error:
Error(18,41): PLS-00201: identifier ‘FND_PROFILE.VALUE’ must be declared
Looks like you do not have sufficient access. Please make sure you are compiling the procedure using apps schema.
But, below query does not give any result in SYS:
select * from dba_objects
where OBJECT_NAME like ‘%FND%PROFILE%’;
If you are using oracle database without oracle EBS, you need to modify the procedure a little.