TOBA MARUS
asked on
where to add if or exception to exit and not end email when no datafound
DECLARE
v_connection UTL_SMTP.connection;
-- mime blocks (the sections of the email body that can become attachments)
-- must be delimited by a string, this particular string is just an example
c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC01234567 89DE';
v_clob CLOB := EMPTY_CLOB();
v_len INTEGER;
v_index INTEGER;
BEGIN
-- Build the contents before connecting to the mail server
-- that way you can begin pumping the data immediately
-- and not risk an SMTP timeout
FOR x IN (SELECT *
FROM all_objects
WHERE ROWNUM < 20)
LOOP
v_clob :=
v_clob
|| x.owner
|| ','
|| x.object_name
|| ','
|| x.object_type
|| ','
|| TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
|| UTL_TCP.crlf;
END LOOP;
v_connection := UTL_SMTP.open_connection(: p_smtp_ser ver);
UTL_SMTP.helo(v_connection , :p_domain);
UTL_SMTP.mail(v_connection , :p_from);
UTL_SMTP.rcpt(v_connection , :p_to);
UTL_SMTP.open_data(v_conne ction);
UTL_SMTP.write_data(v_conn ection, 'From: ' || :p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(v_conn ection, 'To: ' || :p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(v_conn ection, 'Subject: test subject' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_conn ection, '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_conn ection, 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_conn ection, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_conn ection, 'Content-Type: text/plain' || UTL_TCP.crlf);
-- Set up attachment header
UTL_SMTP.write_data(
v_connection,
'Content-Disposition: attachment; filename="'
|| 'your_file_name.csv'
|| '"'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data(v_conn ection, 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_conn ection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
v_index := v_index + 32000;
END LOOP;
--
-- End attachment
UTL_SMTP.write_data(v_conn ection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_conn ection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(v_conn ection);
UTL_SMTP.quit(v_connection );
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(DBMS_ UTILITY.fo rmat_error _stack);
END;
v_connection UTL_SMTP.connection;
-- mime blocks (the sections of the email body that can become attachments)
-- must be delimited by a string, this particular string is just an example
c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC01234567
v_clob CLOB := EMPTY_CLOB();
v_len INTEGER;
v_index INTEGER;
BEGIN
-- Build the contents before connecting to the mail server
-- that way you can begin pumping the data immediately
-- and not risk an SMTP timeout
FOR x IN (SELECT *
FROM all_objects
WHERE ROWNUM < 20)
LOOP
v_clob :=
v_clob
|| x.owner
|| ','
|| x.object_name
|| ','
|| x.object_type
|| ','
|| TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
|| UTL_TCP.crlf;
END LOOP;
v_connection := UTL_SMTP.open_connection(:
UTL_SMTP.helo(v_connection
UTL_SMTP.mail(v_connection
UTL_SMTP.rcpt(v_connection
UTL_SMTP.open_data(v_conne
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(
v_connection,
'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
);
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(
v_connection,
'This is a multi-part message in MIME format.' || UTL_TCP.crlf
);
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(v_conn
-- Set up attachment header
UTL_SMTP.write_data(
v_connection,
'Content-Disposition: attachment; filename="'
|| 'your_file_name.csv'
|| '"'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data(v_conn
-- Write attachment contents
v_len := DBMS_LOB.getlength(v_clob)
v_index := 1;
WHILE v_index <= v_len
LOOP
UTL_SMTP.write_data(v_conn
v_index := v_index + 32000;
END LOOP;
--
-- End attachment
UTL_SMTP.write_data(v_conn
UTL_SMTP.write_data(v_conn
UTL_SMTP.close_data(v_conn
UTL_SMTP.quit(v_connection
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(DBMS_
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.