troubleshooting Question

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

Avatar of TOBA MARUS
TOBA MARUS asked on
ExchangeOracle Database
1 Comment1 Solution132 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros