Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2016-09-21
1
Medium Priority
?
83 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
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video discusses moving either the default database or any database to a new volume.
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

581 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