Home » SQL & PL/SQL » SQL & PL/SQL » Requirement to extract sql script output and attach the same in a email and send to multiple users (11g)
| Requirement to extract sql script output and attach the same in a email and send to multiple users [message #643371] |
Tue, 06 October 2015 07:20  |
 |
avinashkk
Messages: 6
Registered: October 2015
Location: hyderabad
|
Junior Member |
|
|
I have a requirement to attach a sql output in a email and send to multiple users, can any one help me.
currently i am Using utl_smtp functionality, below is the script which i am using.
can anyone update the code such that it will pick the output from sql query.
DECLARE
v_From VARCHAR2(80) := '****.***@gmail.com';
v_Recipient VARCHAR2(80) := '****.***@gmail.com';
v_Subject VARCHAR2(80) := ' (autogenerated email)';
v_Mail_Host VARCHAR2(30) := 'mail1.88.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'Hi Team,'|| crlf || -- Message body
' '|| crlf || -- Message body
'Enddate TMS User account whose HR record is inactive'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="excel.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="excel.csv"'|| crlf ||
crlf ||
'CSV,file,attachement'|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--' -- End MIME mail
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/
|
|
|
|
|
|
|
|
|
|
| Re: Requirement to extract sql script output and attach the same in a email and send to multiple users [message #643422 is a reply to message #643380] |
Wed, 07 October 2015 06:47  |
|
avinashkk
Messages: 6
Registered: October 2015
Location: hyderabad
|
Junior Member |
|
|
CREATE OR REPLACE PROCEDURE trackekr (cursor1 IN OUT sys_refcursor)
AS
v_connection UTL_SMTP.connection;
v_clob CLOB := EMPTY_CLOB ();
v_len INTEGER;
v_index INTEGER;
mailhost VARCHAR2(30);
c_mime_boundary CONSTANT VARCHAR2(256) := 'the boundary can be almost anything';
user_id NUMBER (10, 0) := 0;
employee_id NUMBER (10, 0) := 0;
email_address VARCHAR2 (80);
user_name VARCHAR2 (80);
description VARCHAR2 (80);
start_date DATE;
end_date DATE;
BEGIN
OPEN cursor1 FOR
SELECT user_id
, employee_id
, email_address
, user_name
, description
, start_date
, end_date
FROM fnd_user
WHERE ROWNUM < 5;
LOOP
FETCH cursor1
INTO user_id
, employee_id
, email_address
, user_name
, description
, start_date
, end_date;
v_clob :=
v_clob
|| user_id
|| ','
|| employee_id
|| ','
|| email_address
|| ','
|| user_name
|| ','
|| description
|| ','
|| start_date
|| ','
|| end_date
|| UTL_TCP.crlf;
END LOOP;
-- UTL
v_connection := UTL_SMTP.open_connection (mailhost, 25); -- you need to fill in your SMTP server name or ip address
UTL_SMTP.helo (v_connection, 'mail1.@@.com');
UTL_SMTP.mail (v_connection, '@@@@terex.com');
UTL_SMTP.rcpt (v_connection, '@@@terex.com');
--- you need to fill in this value
UTL_SMTP.open_data (v_connection);
UTL_SMTP.write_data (v_connection
, 'From:
'
|| '@@@terex.com'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection
, 'To: '
|| '@@@terex.com'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection, 'Subject: test subject'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection, 'MIME-Version: 1.0'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection
, 'Content-Type: multipart/mixed;
boundary="'
|| c_mime_boundary
|| '"'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection
, 'This is a multi-part message in
MIME format.'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection, '--'
|| c_mime_boundary
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection
, 'Content-Type: text/plain'
|| UTL_TCP.crlf);
-- Set up attachment header
UTL_SMTP.write_data (v_connection
, 'Content-Disposition: attachment;
filename="'
|| 'FIRSTFILE.csv'
|| '"'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
-- Write attachment contents
v_len := DBMS_LOB.getlength (v_clob);
v_index := 1;
WHILE v_index <= v_len
LOOP
UTL_SMTP.write_data (v_connection
, DBMS_LOB.SUBSTR (v_clob
, 32000
, v_index
));
v_index := v_index
+ 32000;
END LOOP;
-- End attachment
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection
, '--'
|| c_mime_boundary
|| '--'
|| UTL_TCP.crlf);
UTL_SMTP.close_data (v_connection);
UTL_SMTP.quit (v_connection);
END;
[Updated on: Wed, 07 October 2015 06:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jul 11 10:10:06 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
