?
Solved

where to add if or exception to exit and not end email  when no datafound

Posted on 2016-09-21
1
Medium Priority
?
77 Views
1 Endorsement
Last Modified: 2016-09-23
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) := '-----AABCDEFBBCCC0123456789DE';
    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_server);
    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_connection);

    UTL_SMTP.write_data(v_connection, 'From: ' || :p_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: ' || :p_to || 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="'
        || 'your_file_name.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);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
1
Comment
Question by:TOBA MARUS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 41808962
I would suggest adding a numeric variable at the top, and setting that to 0 (zero) before opening the loop.  Then, in the loop, add 1 to this variable each time.  And, after the "end loop;" check if this variable is greater than 0.  If yes, continue.  If no, skip to the end.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month12 days, 7 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question